Loading

Query Need to devlelop in sql

Query Need to devlelop in sql

Akhter HUssain

Akhter HUssain

6y1.3kSQL

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

9 Replies

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

Akhter HUssain6y ago
Hi All ,

I found solution ,
Select G.B_Name as Groups, Isnull(Isnull(Sum(D_QTY),0)-isnull(Sum(C.C_QTY),0),0) Stock
from tbl_BloodDonor D
left join tbl_Group g on g.B_ID=d.B_ID
left join tbl_Cross c on c.D_ID=d.D_ID
group by G.B_Name
Thanks All...
0
Akhter HUssain6y ago
Still not giving correct output.
0
Priyanka Jain6y ago
1. Select G.B_Name ,Isnull(Isnull(Sum(D.D_QTY),0)-isnull(Sum(C.C_QTY),0),0) Stock
2. from #tbl_Group G
3. left join #tbl_BloodDonor D on G.B_ID=D.B_ID
4. left join #tbl_Cross c on C.D_ID = D.D_ID
5. group by G.B_Name
The output is as below,
0

Akhter HUssain

Akhter HUssain6y ago

Hi priyanka Jain,

Please review again,

if i add one more record on blood donor table,then it qty is not getting show in result below is data

i have 2 qty of (C) in blooddonor table,one is i consumed into cross table and one must display in stock

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_BloodDonor VALUES(105,'Daw',4,1)
INSERT INTO #tbl_BloodDonor VALUES(106,'Amir',3,1)
INsert into #tbl_Cross Values(1,101,1,1)
INsert into #tbl_Cross Values(2,102,1,2)
INsert into #tbl_Cross Values(3,103,1,2)
INsert into #tbl_Cross Values(4,103,1,4)
INsert into #tbl_Cross Values(5,103,1,3)
Select G.B_Name ,Isnull(Isnull(Sum(D.D_QTY),0)-isnull(Sum(C.C_QTY),0),0) Stock
from #tbl_Group G
left join #tbl_BloodDonor D on G.B_ID=D.B_ID
left join #tbl_Cross c on C.B_ID=G.B_ID
group by G.B_Name

output

A 0

B 0

C 0

D 0

0

Priyanka Jain

Priyanka Jain6y ago

yes as in query should be updated as below,

Select G.B_Name ,Isnull(Isnull(Sum(D.D_QTY),0)-isnull(Sum(C.C_QTY),0),0) Stock
from #tbl_Group G
left join #tbl_BloodDonor D on G.B_ID=D.B_ID
left join #tbl_Cross c on C.B_ID=G.B_ID
group by G.B_Name

+1

Akhter HUssain

Akhter HUssain6y ago

Hi Priyanka Jain

I inserted one more row into @tbl_Cross INsert into #tbl_Cross Values(3,102,1,2)

but Group (B) is not getting less,B qty will be 0 ,but is showing one

below is data

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_BloodDonor VALUES(105,'Daw',4,1)
INsert into #tbl_Cross Values(1,101,1,1)
INsert into #tbl_Cross Values(2,102,1,2)
INsert into #tbl_Cross Values(3,103,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 join #tbl_BloodDonor D on G.B_ID=D.B_ID
left join #tbl_Cross c on C.D_ID=D.D_ID AND G.B_ID=C.B_ID
group by G.B_Name

0

Akhter HUssain

Akhter HUssain6y ago

Hi Piyush Pansuriya,

I consumed A in cross table mean (D_QTY - C_QTY)

0

Piyush Pansuriya

Piyush Pansuriya6y ago

Hi,

As per your output.

Group: A has Stock :1

But is seems something wrong because QTY 1 - 1 will be 0.

So appropriate output is:

Group Stock
A 0
B 0
C 1
D 0

0

Priyanka Jain

Priyanka Jain6y ago

As per your data in queries and your output is mismatching please check once with your output,

It shoule be like below ,

Select G.B_Name ,Isnull(Isnull(Sum(D.D_QTY),0)-isnull(Sum(C.C_QTY),0),0) Stock
from #tbl_Group G
left join #tbl_BloodDonor D on G.B_ID=D.B_ID
left join #tbl_Cross c on C.D_ID=D.D_ID AND G.B_ID=C.B_ID
group by G.B_Name

+1