SQLTeam.com | Weblogs | Forums

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

sql2014

#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


#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


#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.