clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 921327 fiddles created (11993 in the last week).

SET DATEFORMAT dmy IF OBJECT_ID('tempdb..#ILE') IS NOT NULL DROP TABLE #ILE; CREATE TABLE #ILE ( Posting_Date DATE NOT NULL ,Item_No_ VARCHAR(20) NOT NULL ,Document_No_ VARCHAR(20) NOT NULL PRIMARY KEY ,Location_Code VARCHAR(10) NOT NULL ,Quantity INT NOT NULL ,Item_Category_Code VARCHAR(10) NOT NULL ); INSERT INTO #ILE (Posting_Date,Item_No_,Document_No_,Location_Code,Quantity,Item_Category_Code) VALUES ('03-07-2017','FG-019325','PAO/17-18/03971','NOD',4,'CYLINDERS') , ('03-07-2017','FG-030222','PAO/17-18/03977','NOD',44,'S&P VA') , ('26-06-2017','FG-033154','PAO/17-18/03790','NOD',20,'CYLINDERS') , ('26-06-2017','FG-027182','PAO/17-18/03797','NOD',50,'S&P VA') , ('30-06-2017','FG-000242','PAO/17-18/03941','NOD',10,'CYLINDERS') , ('03-07-2017','FG-026591','PAO/17-18/03979','NOD',1,'S&P VA') , ('03-07-2017','FG-027302','PAO/17-18/03955','NOD',1,'CYLINDERS') , ('03-07-2017','FG-036994','PAO/17-18/03970','NOD',1,'CYLINDERS') , ('03-07-2017','FG-037249','PAO/17-18/03974','NOD',2,'CYLINDERS') , ('26-06-2017','FG-037133','PAO/17-18/03813','NOD',10,'CYLINDERS') , ('26-06-2017','FG-025815','PAO/17-18/03823','NOD',10,'S&P VA') , ('29-06-2017','FG-047645','PAO/17-18/03923','NOD',10,'CYLINDERS') , ('03-07-2017','FG-018959','PAO/17-18/03972','NOD',6,'CYLINDERS') , ('03-07-2017','FG-030435','PAO/17-18/03969','NOD',1,'CYLINDERS') , ('03-07-2017','FG-047599','PAO/17-18/03973','NOD',2,'CYLINDERS') , ('26-06-2017','FG-044910','PAO/17-18/03818','NOD',10,'S&P VA') , ('27-06-2017','FG-000366','PAO/17-18/03873','NOD',10,'CYLINDERS'); DECLARE @nvc_Columns_In_Pivot AS NVARCHAR(MAX) =N'' ,@nvc_Columns_In_Header AS NVARCHAR(MAX) =N'' ,@nvc_Columns_In_Sub_Header AS NVARCHAR(MAX) =N'' , @nvc_SQL AS NVARCHAR(MAX) =''; SELECT @nvc_Columns_In_Header = @nvc_Columns_In_Header + N',' + N'CAST(P_Q.' + QUOTENAME(Item_Category_Code) + N'* 1.0/C.Count_Working_Days AS DECIMAL(4,2)) AS '+ QUOTENAME(Item_Category_Code) + CHAR(13)+CHAR(10) + CHAR(9) -- new line ,@nvc_Columns_In_Sub_Header = @nvc_Columns_In_Sub_Header + + N',P_Q.' + QUOTENAME(Item_Category_Code) ,@nvc_Columns_In_Pivot = @nvc_Columns_In_Pivot + ',' + QUOTENAME(Item_Category_Code) FROM #ILE AS I GROUP BY Item_Category_Code SET @nvc_SQL = N'SELECT P_Q.Posting_Date ,' + STUFF(@nvc_Columns_In_Header ,1,1,N'') + N' FROM ( SELECT P_Q.Posting_Date ,' +STUFF(@nvc_Columns_In_Sub_Header ,1,1,N'') + N'FROM (SELECT LEFT(CONVERT(NVARCHAR(10),Posting_Date,112),6) as Posting_Date /* YYYYMM*/ ,Quantity ,Item_Category_Code FROM #ILE )AS S PIVOT ( SUM(Quantity) FOR Item_Category_Code IN (' + STUFF(@nvc_Columns_In_Pivot,1,1,N'') + N') )AS P_Q )P_Q INNER JOIN (SELECT COUNT(Posting_Date) AS Count_Working_Days ,LEFT(CONVERT(NVARCHAR(10),Posting_Date,112),6) AS Working_Days FROM #ILE GROUP BY LEFT(CONVERT(NVARCHAR(10),Posting_Date,112),6) )C ON C.Working_Days = P_Q.Posting_Date ' --PRINT @nvc_SQL; EXEC sp_executesql @nvc_SQL;
Posting_Date CYLINDERS S&P VA
201706 7.50 8.75
201707 1.89 5.00
 hidden batch(es)


 hidden batch(es)