I have data with query ,and ouput result ,query is not giving me correct ouput .
- CREATE TABLE #tbl_Group(B_ID INT,B_Name VARCHAR(50))
- CREATE TABLE #tbl_BloodDonor(D_ID INT,D_Name VARCHAR(50),B_ID int,D_QTY int)
- CREATE TABLE #tbl_Cross(C_ID INT,D_ID int,C_QTY int,B_ID int )
-
- INSERT INTO #tbl_Group VALUES(1,'A')
- INSERT INTO #tbl_Group VALUES(2,'B')
- INSERT INTO #tbl_Group VALUES(3,'C')
- INSERT INTO #tbl_Group VALUES(4,'D')
-
- INSERT INTO #tbl_BloodDonor VALUES(101,'Hussain',1,1)
- INSERT INTO #tbl_BloodDonor VALUES(102,'Akhter',2,1)
- INSERT INTO #tbl_BloodDonor VALUES(103,'Ali',3,1)
- INSERT INTO #tbl_BloodDonor VALUES(104,'Rehan',2,1)
-
- INsert into #tbl_Cross Values(1,101,1,1)
- INsert into #tbl_Cross Values(2,102,1,2)
-
- Select G.B_Name ,Isnull(Isnull(Sum(D.D_QTY),0)-isnull(Sum(C.C_QTY),0),0) Stock
- from #tbl_Group G
- left outer join #tbl_BloodDonor D on G.B_ID=D.B_ID
- left outer join #tbl_Cross c on C.B_ID=G.B_ID
- group by G.B_Name
Output
| Group | Stock |
| A | 1 |
| B | 0 |
| C | 1 |
| D | 0 |