Month Wise Total of QTY of ITem?

Akhter HUssain

7y794SQL

Here is my data ..

CREATE TABLE #ItemMasterFile(ItemCode INT,Descriptionitem VARCHAR(50),SecID INT,weight int);
CREATE TABLE #Probale(BID INT,ItemCode INT,prdQTY INT,Orderno int,weight int,Entrydate DATETIME,DelID int);
INSERT INTO #ItemMasterFile VALUES(1,'A',1,100)
INSERT INTO #ItemMasterFile VALUES(2,'B',2,100)
INSERT INTO #ItemMasterFile VALUES(3,'C',3,100)
INSERT INTO #ItemMasterFile VALUES(4,'D',4,100)
INSERT INTO #ItemMasterFile VALUES(5,'e',5,100)
INSERT INTO #ItemMasterFile VALUES(6,'f',6,100)
INSERT INTO #ItemMasterFile VALUES(7,'g',4,100)
INSERT INTO #ItemMasterFile VALUES(8,'h',4,100)
INSERT INTO #ItemMasterFile VALUES(9,'K',2,100)
INSERT INTO #ItemMasterFile VALUES(10,'L',4,100)
INSERT INTO #ItemMasterFile VALUES(11,'M',2,100);
INSERT INTO #Probale VALUES(1,1,1,001,100,'01-05-2019',null)
INSERT INTO #Probale VALUES(1,1,1,001,100,'01-06-2019',null)
INSERT INTO #Probale VALUES(2,3,1,001,200,'02-07-2019',null)
INSERT INTO #Probale VALUES(3,11,1,002,200,'03-08-2019',null)
INSERT INTO #Probale VALUES(4,10,1,002,200,'08-08-2019',null)
INSERT INTO #Probale VALUES(4,1,1,003,200,'08-08-2019',null)
INSERT INTO #Probale VALUES(4,1,1,003,200,'08-08-2019',null)
INSERT INTO #Probale VALUES(4,1,1,003,200,'01-08-2019',null)

Expected Result...

S.No	Item	May-19	Jun-19	Jul-19	Aug-19
1	A	1	1	0	2
2	B	0	0	0	0
3	C	0	0	1	0
4	D	0	0	0	0
5	E	0	0	0	0
6	F	0	0	0	0
7	E	0	0	0	0
8	G	0	0	0	0
9	H	0	0	0	0
10	K	0	0	0	1
11	L	0	0	0	1
12	m	0	0	0	0

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sonu Gupta7y ago
Accepted answer
Try this --

SELECT SRN, ITEM, Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec,jan,Feb,Mar
FROM
( SELECT SRN,ITEM,SUM(prdQTY) AS prdQTY,Entrydt FROM (
SELECT A.ItemCode AS SRN, Descriptionitem AS ITEM , prdQTY, CONVERT(varchar(3), DATENAME(MM, Entrydate), 100) AS Entrydt
FROM #ItemMasterFile A INNER JOIN #Probale B ON A.ItemCode=B.ItemCode
) Result group by SRN,ITEM,Entrydt
) d
PIVOT
(MAX(prdQTY) FOR ENTRYDT IN (Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec,jan,Feb,Mar) ) PIV;
+4
Akhter HUssain7y ago
Thanks Sonu Gupta..
0
Akhter HUssain7y ago
Hi Sonu Gupta ,
i just added two new rows
INSERT INTO #Probale VALUES(8,1,1,001,100,'04-04-2019',null)
INSERT INTO #Probale VALUES(9,1,1,001,100,'04-24-2019',null)

but there total is not coming correcet
0
Sonu Gupta7y ago
Use this -

SELECT SRN, ITEM, Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec,jan,Feb,Mar
FROM
( SELECT A.ItemCode AS SRN, Descriptionitem AS ITEM , sum(prdQTY) as prdQTY, CONVERT(varchar(3), DATENAME(MM, Entrydate), 100) AS Entrydt
FROM #ItemMasterFile A INNER JOIN #Probale B ON A.ItemCode=B.ItemCode group by A.ItemCode,Descriptionitem,Entrydate
) d
PIVOT
(MAX(prdQTY) FOR ENTRYDT IN (Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec,jan,Feb,Mar) ) PIV;

+1
Akhter HUssain7y ago
Hi,
Sonu Gupta , Check below data ,item is same but result of your query is not getting sum of qty of a item in a same month
INSERT INTO #Probale VALUES(4,1,1,003,200,'08-08-2019',null)
INSERT INTO #Probale VALUES(4,1,1,003,200,'08-08-2019',null)
INSERT INTO #Probale VALUES(4,1,1,003,200,'01-08-2019',null)
0
Sonu Gupta7y ago
Hi,

Try to this -

SELECT SRN, ITEM, Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec,jan,Feb,Mar
FROM
(
SELECT A.ItemCode AS SRN, Descriptionitem AS ITEM , prdQTY, CONVERT(varchar(3), DATENAME(MM, Entrydate), 100) AS Entrydt
from #ItemMasterFile A INNER JOIN #Probale B ON A.ItemCode=B.ItemCode
) d
PIVOT
(
MAX(prdQTY) FOR Entrydt IN (Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec,jan,Feb,Mar)
) PIV;

+1