Daily Production Report Categorywise - on the basis of No. of days actually worked

1

Dear All
I need Monthwise Average daily production report categorywise. I need to calculate what is the total production of cylinders per day in a month. Similarly I need to know what is the total production of 'S&P VA' in a day on the basis of total working days of a month calculated from data itself. For eg. If production has worked for 7 days the average should be based on 7 working days.
Following is the scripted form of table and data:-
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 0:00'
,'FG-019325'
,'PAO/17-18/03971'
,'NOD'
,4
,'CYLINDERS'
);

INSERT INTO ILE (
Posting_Date
,Item_No_
,Document_No_
,Location_Code
,Quantity
,Item_Category_Code
)
VALUES (
'03-07-2017 0:00'
,'FG-030222'
,'PAO/17-18/03977'
,'NOD'
,44
,'S&P VA'
);

INSERT INTO ILE (
Posting_Date
,Item_No_
,Document_No_
,Location_Code
,Quantity
,Item_Category_Code
)
VALUES (
'26-06-2017 0:00'
,'FG-033154'
,'PAO/17-18/03790'
,'NOD'
,20
,'CYLINDERS'
);

INSERT INTO ILE (
Posting_Date
,Item_No_
,Document_No_
,Location_Code
,Quantity
,Item_Category_Code
)
VALUES (
'26-06-2017 0:00'
,'FG-027182'
,'PAO/17-18/03797'
,'NOD'
,50
,'S&P VA'
);

INSERT INTO ILE (
Posting_Date
,Item_No_
,Document_No_
,Location_Code
,Quantity
,Item_Category_Code
)
VALUES (
'30-06-2017 0:00'
,'FG-000242'
,'PAO/17-18/03941'
,'NOD'
,10
,'CYLINDERS'
);

INSERT INTO ILE (
Posting_Date
,Item_No_
,Document_No_
,Location_Code
,Quantity
,Item_Category_Code
)
VALUES (
'03-07-2017 0:00'
,'FG-026591'
,'PAO/17-18/03979'
,'NOD'
,1
,'S&P VA'
);

INSERT INTO ILE (
Posting_Date
,Item_No_
,Document_No_
,Location_Code
,Quantity
,Item_Category_Code
)
VALUES (
'03-07-2017 0:00'
,'FG-027302'
,'PAO/17-18/03955'
,'NOD'
,1
,'CYLINDERS'
);

INSERT INTO ILE (
Posting_Date
,Item_No_
,Document_No_
,Location_Code
,Quantity
,Item_Category_Code
)
VALUES (
'03-07-2017 0:00'
,'FG-036994'
,'PAO/17-18/03970'
,'NOD'
,1
,'CYLINDERS'
);

INSERT INTO ILE (
Posting_Date
,Item_No_
,Document_No_
,Location_Code
,Quantity
,Item_Category_Code
)
VALUES (
'03-07-2017 0:00'
,'FG-037249'
,'PAO/17-18/03974'
,'NOD'
,2
,'CYLINDERS'
);

INSERT INTO ILE (
Posting_Date
,Item_No_
,Document_No_
,Location_Code
,Quantity
,Item_Category_Code
)
VALUES (
'26-06-2017 0:00'
,'FG-037133'
,'PAO/17-18/03813'
,'NOD'
,10
,'CYLINDERS'
);

INSERT INTO ILE (
Posting_Date
,Item_No_
,Document_No_
,Location_Code
,Quantity
,Item_Category_Code
)
VALUES (
'26-06-2017 0:00'
,'FG-025815'
,'PAO/17-18/03823'
,'NOD'
,10
,'S&P VA'
);

INSERT INTO ILE (
Posting_Date
,Item_No_
,Document_No_
,Location_Code
,Quantity
,Item_Category_Code
)
VALUES (
'29-06-2017 0:00'
,'FG-047645'
,'PAO/17-18/03923'
,'NOD'
,10
,'CYLINDERS'
);

INSERT INTO ILE (
Posting_Date
,Item_No_
,Document_No_
,Location_Code
,Quantity
,Item_Category_Code
)
VALUES (
'03-07-2017 0:00'
,'FG-018959'
,'PAO/17-18/03972'
,'NOD'
,6
,'CYLINDERS'
);

INSERT INTO ILE (
Posting_Date
,Item_No_
,Document_No_
,Location_Code
,Quantity
,Item_Category_Code
)
VALUES (
'03-07-2017 0:00'
,'FG-030435'
,'PAO/17-18/03969'
,'NOD'
,1
,'CYLINDERS'
);

INSERT INTO ILE (
Posting_Date
,Item_No_
,Document_No_
,Location_Code
,Quantity
,Item_Category_Code
)
VALUES (
'03-07-2017 0:00'
,'FG-047599'
,'PAO/17-18/03973'
,'NOD'
,2
,'CYLINDERS'
);

INSERT INTO ILE (
Posting_Date
,Item_No_
,Document_No_
,Location_Code
,Quantity
,Item_Category_Code
)
VALUES (
'26-06-2017 0:00'
,'FG-044910'
,'PAO/17-18/03818'
,'NOD'
,10
,'S&P VA'
);

INSERT INTO ILE (
Posting_Date
,Item_No_
,Document_No_
,Location_Code
,Quantity
,Item_Category_Code
)
VALUES (
'27-06-2017 0:00'
,'FG-000366'
,'PAO/17-18/03873'
,'NOD'
,10
,'CYLINDERS'
);

Desired Output

+----------------+--+-------+------+-----------+--------+---+-------+
| FY (Mar-April) | | Month | | Cylinders | S&P VA |
+----------------+--+-------+------+-----------+--------+---+-------+
| 2017 | | | Jun | | 7.5 | | 8.75 |
| 2017 | | | July | | 1.89 | | 5 |
+----------------+--+-------+------+-----------+--------+---+-------+

Total Cylinders produced in June 60. Total working days in June is 8. Therefore per day production of cylinders is 7.5 (60/8) in June.
Total Cylinders produced in July 17. Total working days in July is 9. Therefore per day production of cylinders is 1.89 (17/9) in June.

Total S&P VA produced in June 70. Total working days in June is 8. Therefore per day production of S&P VA is 8.75 (70/8) in June.
Total S&P VA produced in July 45. Total working days in July is 9. Therefore per day production of S&P VA is 5 (45/9) in June.

version: sql server 2014

1 Like
2

Try this:

select 
YEAR(Posting_Date) as Year_Posting
,MONTH(Posting_Date) as Month_Posting
,CAST( SUM(CASE WHEN Item_Category_Code ='Cylinders' THEN Quantity ELSE 0 END)*1.0 /COUNT(Posting_Date) AS DECIMAL(8,2))  as Cylinders
,CAST( SUM(CASE WHEN Item_Category_Code ='S&P VA' THEN Quantity ELSE 0 END)*1.0/COUNT(Posting_Date) AS DECIMAL(8,2)) as [S&P VA]
from ILE AS I
group by
 YEAR(Posting_Date)
 , MONTH(Posting_Date)

Output:

Year_Posting	Month_Posting	Cylinders	S&P VA
2017	6	7.50	8.75
2017	7	1.89	5.00

dbfiddle

1 Like
3

Thanks for your response. It seems to be fine but there are approx. 50 types of item category codes and i cannot specify condition of item category code as 'cylinders' or 'S&P VA' manually. I think group by clause must be used for item category also..

mpapreja

4

yes , you can group by it, or you can use pivot(dynamic pivot for it)

5

Thanks but being a beginner it would be difficult for me to implement conditional groupby clause. Could u please help me in formulating sql query using conditional group by clause?

mpapreja

6 
SET DATEFORMAT dmy

IF OBJECT_ID('tempdb..#ILE') IS NOT NULL DROP TABLE #ILE;
GO

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(8,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;

Output of it:

Posting_Date CYLINDERS                               S&P VA
201706       7.50                                    8.75
201707       1.89                                    5.00

dbfiddle

7

Thanks for your above response. I have modified your query as per following details to suit the data in my actual table i.e. Item Ledger Entry

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(8,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 [Pneumax India Pvt Ltd$Item Ledger Entry] 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
[Pneumax India Pvt Ltd$Item Ledger Entry]
)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 [Pneumax India Pvt Ltd$Item Ledger Entry]
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;

I am getting following output with above query and errors:slight_smile:

SELECT
P_Q.Posting_Date
,CAST(P_Q.[MANIP]* 1.0/C.Count_Working_Days AS DECIMAL(8,2)) AS [MANIP]
,CAST(P_Q.[RM MANIP]* 1.0/C.Count_Working_Days AS DECIMAL(8,2)) AS [RM MANIP]
,CAST(P_Q.[OTHERS]* 1.0/C.Count_Working_Days AS DECIMAL(8,2)) AS [OTHERS]
,CAST(P_Q.[S&P VA]* 1.0/C.Count_Working_Days AS DECIMAL(8,2)) AS [S&P VA]
,CAST(P_Q.[RM RODLCYL]* 1.0/C.Count_Working_Days AS DECIMAL(8,2)) AS [RM RODLCYL]
,CAST(P_Q.[EQUIP.MAIN]* 1.0/C.Count_Working_Days AS DECIMAL(8,2)) AS [EQUIP.MAIN]
,CAST(P_Q.[RM FRLSNEW]* 1.0/C.Count_Working_Days AS DECIMAL(8,2)) AS [RM FRLSNEW]
,CAST(P_Q.[SA]* 1.0/C.Count_Working_Days AS DECIMAL(8,2)) AS [SA]
,CAST(P_Q.[ACCESS]* 1.0/C.Count_Working_Days AS DECIMAL(8,2)) AS [ACCESS]
,CAST(P_Q.[COMPAIRPIP]* 1.0/C.Count_Working_Days AS DECIMAL(8,2)) AS [COMPAIRPIP]
,CAST(P_Q.[RM ODE]* 1.0/C.Count_Working_Days AS DECIMAL(8,2)) AS [RM ODE]
,CAST(P_Q.[DIG P SWCH]* 1.0/C.Count_Working_Days AS DECIMAL(8,2)) AS [DIG P SWCH]
,CAST(P_Q.[FRLS]* 1.0/C.Count_Working_Days AS DECIMAL(8,2)) AS [FRLS]
,CAST(P_Q.[FITT&TUB]* 1.0/C.Count_Working_Days AS DECIMAL(8,2)) AS [FITT&TUB]
,CAST(P_Q.* 1.0/C.Count_Working_Days AS DECIMAL(8,2)) AS
,CAST(P_Q.[NEW FRLS]* 1.0/C.Count_Working_Days AS DECIMAL(8,2)) AS [NEW FRLS]
,CAST(P_Q.[TOOLS]* 1.0/C.Count_Working_Days AS DECIMAL(8,2)) AS [TOOLS]
,CAST(P_Q.[RMCLAMPIN]* 1.0/C.Count_Working_Days AS DECIMAL(8,2)) AS [RMCLAMPIN]
,CAST(P_Q.[ODE]* 1.0/C.Count_Working_Days AS DECIMAL(8,2)) AS [ODE]
,CAST(P_Q.[CONSUMABLE]* 1.0/C.Count_Working_Days AS DECIMAL(8,2)) AS [CONSUMABLE]
,CAST(P_Q.[VACUUM]* 1.0/C.Count_Working_Days AS DECIMAL(8,2)) AS [VACUUM]
,CAST(P_Q.[PPACKG]* 1.0/C.Count_Working_Days AS DECIMAL(8,2)) AS [PPACKG]
,CAST(P_Q.[RM S&P VA]* 1.0/C.Count_Working_Days AS DECIMAL(8,2)) AS [RM S&P VA]
,CAST(P_Q.[RM M&M VA]* 1.0/C.Count_Working_Days AS DECIMAL(8,2)) AS [RM M&M VA]
,CAST(P_Q.[M&M VA]* 1.0/C.Count_Working_Days AS DECIMAL(8,2)) AS [M&M VA]
,CAST(P_Q.[BATTUTA]* 1.0/C.Count_Working_Days AS DECIMAL(8,2)) AS [BATTUTA]
,CAST(P_Q.[CYLINDERS]* 1.0/C.Count_Working_Days AS DECIMAL(8,2)) AS [CYLINDERS]
,CAST(P_Q.[RM VACUUM]* 1.0/C.Count_Working_Days AS DECIMAL(8,2)) AS [RM VACUUM]
,CAST(P_Q.[RM CYL]* 1.0/C.Count_Working_Days AS DECIMAL(8,2)) AS [RM CYL]
,CAST(P_Q.[RM FRLS]* 1.0/C.Count_Working_Days AS DECIMAL(8,2)) AS [RM FRLS]

FROM
(
SELECT
P_Q.Posting_Date
,P_Q.[MANIP],P_Q.[RM MANIP],P_Q.[OTHERS],P_Q.[S&P VA],P_Q.[RM RODLCYL],P_Q.[EQUIP.MAIN],P_Q.[RM FRLSNEW],P_Q.[SA],P_Q.[ACCESS],P_Q.[COMPAIRPIP],P_Q.[RM ODE],P_Q.[DIG P SWCH],P_Q.[FRLS],P_Q.[FITT&TUB],P_Q.,P_Q.[NEW FRLS],P_Q.[TOOLS],P_Q.[RMCLAMPIN],P_Q.[ODE],P_Q.[CONSUMABLE],P_Q.[VACUUM],P_Q.[PPACKG],P_Q.[RM S&P VA],P_Q.[RM M&M VA],P_Q.[M&M VA],P_Q.[BATTUTA],P_Q.[CYLINDERS],P_Q.[RM VACUUM],P_Q.[RM CYL],P_Q.[RM FRLS]FROM
(SELECT
LEFT(CONVERT(NVARCHAR(10),Posting_Date,112),6) as Posting_Date /* YYYYMM*/
,Quantity
,Item_Category_Code
FROM
[Pneumax India Pvt Ltd$Item Ledger Entry]
)AS S
PIVOT
(
SUM(Quantity) FOR Item_Category_Code IN ([MANIP],[RM MANIP],[OTHERS],[S&P VA],[RM RODLCYL],[EQUIP.MAIN],[RM FRLSNEW],[SA],[ACCESS],[COMPAIRPIP],[RM ODE],[DIG P SWCH],[FRLS],[FITT&TUB],,[NEW FRLS],[TOOLS],[RMCLAMPIN],[ODE],[CONSUMABLE],[VACUUM],[PPACKG],[RM S&P VA],[RM M&M VA],[M&M VA],[BATTUTA],[CYLINDERS],[RM VACUUM],[RM CYL],[RM FRLS])
)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 [Pneumax India Pvt Ltd$Item Ledger Entry]
GROUP BY LEFT(CONVERT(NVARCHAR(10),Posting_Date,112),6)
)C
ON C.Working_Days = P_Q.Posting_Date
Msg 1038, Level 15, State 4, Line 17
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or are not allowed. Change the alias to a valid name.
Msg 1038, Level 15, State 4, Line 17
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or are not allowed. Change the alias to a valid name.
Msg 1038, Level 15, State 4, Line 38
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or are not allowed. Change the alias to a valid name.
Msg 1038, Level 15, State 4, Line 48
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or are not allowed. Change the alias to a valid name.
Msg 102, Level 15, State 1, Line 50
Incorrect syntax near 'P_Q'.
Msg 102, Level 15, State 1, Line 56
Incorrect syntax near 'C'.

Pl. note that I cannot make any changes in my table i.e. Item Ledger Entry. Can u please advice to resolve above error and to fit the query into my actual table.

8 
CAST(P_Q.[FITT&TUB]* 1.0/C.Count_Working_Days AS DECIMAL(8,2)) AS [FITT&TUB]
,CAST(P_Q.[]* 1.0/C.Count_Working_Days AS DECIMAL(8,2)) AS []
,CAST(P_Q.[NEW FRLS]* 1.0/C.Count_Working_Days AS DECIMAL(8,2)) AS [NEW FRLS]

Look at the middle row ... is not right ; I will take a look later in the day to have a deeper understanding of it.

Probably you have a NULL value in field [Item Category Code]

9

add WHERE [Item Category Code] IS NOT NULL in your code:

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(8,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 [Pneumax India Pvt Ltd$Item Ledger Entry] AS I
WHERE [Item Category Code] IS NOT NULL
GROUP BY [Item Category Code]
10

Thanks for your assistance but I have applied the above change but with same output error. I have checked the table and yes there are entries where item category code is blank.

Kindly advice.

11

when you say :code is blank , this mean you have a space char,
then try

WHERE [Item Category Code]> ''

12

I have tried this it seems it is successful but i am still getting following errors:
Msg 102, Level 15, State 1, Line 41
Incorrect syntax near 'Code'.
Msg 102, Level 15, State 1, Line 55
Incorrect syntax near 'C'.

13

The main idea is to get a list of your ItemCategoryCode (uniquely and have all values)
So just do a simple SELECT and go from there

SELECT 
       ItemCategoryCode
FROM  
       yourTable
WHERE
      here a condition
GROUP BY
        ItemCategoryCode

and then update the first part of the script

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(8,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 [Pneumax India Pvt Ltd$Item Ledger Entry] AS I
GROUP BY [Item Category Code]
14

Now I am getting the output using the following query:-1:

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(8,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 [Pneumax India Pvt Ltd$Item Ledger Entry] AS I
where [Item Category Code] <>''
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
[Pneumax India Pvt Ltd$Item Ledger Entry]
)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 [Pneumax India Pvt Ltd$Item Ledger Entry]
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;

But i need slight addition in above query as follows:

I need the output Financial Yearwise and Monthwise using following date functions:-

YEAR(DATEADD(Month, - 3, [Pneumax India Pvt Ltd$Item Ledger Entry].[Posting Date])) AS FY
CONVERT(varchar(3), [Pneumax India Pvt Ltd$Item Ledger Entry].[Posting Date], 100) AS Month

Can u please help me by guiding where exactly above functions are to be inserted into above code to get the yearwise and monthwise production?

15

Firstly the production is grouped yearwise and then by monthwise.