I want sum of item qty month wise ...
here is my data ..
CREATE TABLE #ItemMasterFile(CodeItem INT,Descriptionitem VARCHAR(50),SecID INT,weight int);
CREATE TABLE #Probale(BID INT,CodeItem INT,prdQTY INT,Orderno int,weight int,Entrydate DATETIME,DelID int);
INSERT INTO #ItemMasterFile VALUES
(1,'A',1,100)
, (2,'B',2,100)
, (3,'C',3,100)
, (4,'D',4,100)
, (5,'e',5,100)
, (6,'f',6,100)
, (7,'g',4,100)
, (8,'h',4,100)
, (9,'K',2,100)
, (10,'L',4,100)
, (11,'M',2,100);
INSERT INTO #Probale VALUES
(1,1,1,001,100,'01-05-2019',null),
(1,1,1,001,100,'01-06-2019',null)
, (2,3,1,001,200,'02-07-2019',null)
, (3,11,1,002,200,'03-08-2019',null)
, (4,10,1,002,200,'08-08-2019',null)
, (4,1,1,003,200,'08-08-2019',null)
, (4,1,1,003,200,'08-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 |