i have data below
CREATE TABLE #ItemMasterFile(CodeItem INT,Descriptionitem VARCHAR(50))
CREATE TABLE #Probale(BID INT,CodeItem INT,prdQTY INT,Entrydate DATETIME)
CREATE TABLE #PackM(PID INT, Entrydate DATETIME)
CREATE TABLE #PackD(DID INT,PID int,BID int,Codeitem int,prdQTY int)
CREATE TABLE #DispatchM(DID INT, Entrydate DATETIME)
Create Table #DispatachD(ID int ,DID int,PID int )
INSERT INTO #ItemMasterFile VALUES(1,'A')
INSERT INTO #ItemMasterFile VALUES(2,'B')
INSERT INTO #ItemMasterFile VALUES(3,'C')
INSERT INTO #ItemMasterFile VALUES(4,'D')
INSERT INTO #ItemMasterFile VALUES(5,'e')
INSERT INTO #ItemMasterFile VALUES(6,'f')
INSERT INTO #ItemMasterFile VALUES(7,'g')
INSERT INTO #ItemMasterFile VALUES(8,'h')
INSERT INTO #ItemMasterFile VALUES(9,'K')
INSERT INTO #ItemMasterFile VALUES(10,'L')
INSERT INTO #ItemMasterFile VALUES(11,'M')
INSERT INTO #Probale VALUES(10011,1,1,'01-06-2019')
INSERT INTO #Probale VALUES(10012,3,1,'02-06-2019')
INSERT INTO #Probale VALUES(10013,11,1,'03-06-2019')
INSERT INTO #Probale VALUES(10014,10,1,'08-06-2019')
INSERT INTO #Probale VALUES(10015,8,1,'03-06-2019')
INSERT INTO #Probale VALUES(10016,9,1,'08-06-2019')
INSERT INTO #Probale VALUES(10017,9,1,'08-06-2019')
INSERT INTO #PackM VALUES(1001,'01-06-2019')
INSERT INTO #PackM VALUES(1002,'01-06-2019')
INSERT INTO #PackD VALUES(1,1001,10011,1,1)
INSERT INTO #PackD VALUES(2,1001,10012,3,1)
INSERT INTO #PackD VALUES(3,1001,10013,11,1)
INSERT INTO #PackD VALUES(4,1001,10014,10,1)
INSERT INTO #PackD VALUES(5,1002,10015,8,1)
INSERT INTO #PackD VALUES(6,1002,10016,9,1)
INSERT INTO #PackD VALUES(7,1002,10017,9,1)
INSERT INTO #DispatchM VALUES(101,'01-06-2019')
INSERT INTO #DispatchM VALUES(102,'01-06-2019')
INSERT INTO #DispatachD VALUES(1,101,1001)
INSERT INTO #DispatachD VALUES(1,102,1002)
i want below format
| Item |
Total probale |
Dispatch |
Pending |
| A |
1 |
1 |
0 |
| B |
0 |
0 |
0 |
| C |
1 |
1 |
0 |
| D |
0 |
0 |
0 |
| E |
0 |
0 |
0 |
| F |
0 |
0 |
0 |
| G |
0 |
0 |
0 |
| H |
1 |
1 |
0 |
| K |
2 |
2 |
0 |
| L |
1 |
1 |
0 |
| M |
1 |
1 |
0 |
