Coplicated Query of Sum from 4 Different Tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Coplicated Query of Sum from 4 Different Tables

Author

Topic

boyzs
Starting Member

8 Posts

Posted - 2008-09-10 : 13:59:45

[code]
Dear Friends,
I am just new with this joins.. I had never used this much complicated joins in queries..
Yesterday I got the solution from this site that was of Sum from 3 tables now I need Sum from 4 tables
I just tide up by solving this query...
Hear is my detail problem to be resolved. I used the following tables
1.Pack Master
PackId PK
FlavourName

2.TransationMaster
Code Pk
TerritoryName

3.SchemeTransationMaster
TransationCode (fk TransationMaster.Code)
PackId (fk Pack Master.PackId)
FlavourName
QtySecSales
QtySchemeSales
Amount

4.CashDiscountTransation
TransationCode (fk TransationMaster.Code)
PackId (fk Pack Master.PackId)
FlavourName
QtySecSales
QtySchemeSales
Amount

Data's are as follows

1.PackMaster
PackId FlavourName
1 CSD
2 JBD
3 AF

2.TransationMaster
Code TerritoryName
1 Ahd
2 Ahd
3 Central Guj
4 Ahd
5 South Guj

3.SchemeTransationMaster
TransationCode PackId FlavourName QtySecSales QtySchemeSales Amount
1 1 CSD 10 1 100
1 2 JBD 20 1 100
2 1 CSD 10 1 100
4 3 AF 10 1 100
5 2 JBD 10 2 100

4.CashDiscountTransation
TransationCode PackId FlavourName QtySecSales QtySchemeSales Amount
1 1 CSD 5 1 100
5 2 JBD 10 1 100

I want output if I gives Parameter as 'South Guj' in TerritoryName

FlavourName QtySecSales QtySchemeSales Amount
JBD 20 3 200

But My Query gives me OutPut Like this
FlavourName QtySecSales QtySchemeSales Amount
CSD 0 0 0
JBD 20 3 200
AF 0 0 0

Hear is the Query I Used

SELECT
PM.FlavourName,
ISNULL(D1.QtySecSales,0.00) as QtySecSales,
ISNULL(D1.QtySchemeSales,0.00) as QtySchemeSales,
ISNULL(D1.AmountOfSchemeVolume,0.00) as AmountOfSchemeVolume,
ISNULL(D2.SecQtyOfCashDisc,0.00) as SecQtyOfCashDisc,
ISNULL(D2.QtyOfCashDisc,0.00) as QtyOfCashDisc,
ISNULL(D2.TotalAmtOfCashDisc,0.00) as TotalAmtOfCashDisc,
(ISNULL(D1.QtySecSales,0.00))+(ISNULL(D2.SecQtyOfCashDisc,0.00)) as SecoundaryTotal,
(ISNULL(D1.QtySchemeSales,0.00))+(ISNULL(D2.QtyOfCashDisc,0.00)) as SchemeTotal,
(ISNULL(D1.AmountOfSchemeVolume,0.00))+(ISNULL(D2.TotalAmtOfCashDisc,0.00)) as AmountTotal
FROM TransationMaster T,PackMaster PM
LEFT JOIN
(
SELECT

P.FlavourName
,SUM(S.QtySecSales) AS QtySecSales
,SUM(S.QtySchemeSales) AS QtySchemeSales
,SUM(S.AmountOfSchemeVolume) AS AmountOfSchemeVolume
FROM SchemeTransationMaster S
inner join PackMaster P on P.PackId = S.PackId
inner join TransationMaster TM on TM.Code = S.TransationCode
WHERE S.IsDeleted = 0
AND TM.IsDeleted = 0
AND TM.TerritoryName= 'South Guj'
GROUP BY P.FlavourName
) D1
ON PM.FlavourName = D1.FlavourName
LEFT JOIN
(
SELECT

P.FlavourName
,SUM(C.SecQtyOfCashDisc) AS SecQtyOfCashDisc
,SUM(C.QtyOfCashDisc) AS QtyOfCashDisc
,SUM(C.TotalAmtOfCashDisc) AS TotalAmtOfCashDisc
FROM CashDiscountTransation C
inner join PackMaster P on P.PackId = C.PackId
inner join TransationMaster TM on TM.Code = C.TransationCode
WHERE C.IsDeleted = 0
AND TM.IsDeleted = 0
AND TM.TerritoryName = 'South Guj'
GROUP BY P.FlavourName
) D2
ON PM.FlavourName = D2.FlavourName
Group By PM.FlavourName,D1.QtySecSales,D1.QtySchemeSales,D1.AmountOfSchemeVolume,D2.SecQtyOfCashDisc,D2.QtyOfCashDisc,D2.TotalAmtOfCashDisc
Order By PM.FlavourName

can any one help me where I am wrong in this query....??

Thank You
Regards
Boyzs
[/code]

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-09-10 : 15:54:24

I am not sure what your question/problem is ....

- Jeff
http://weblogs.sqlteam.com/JeffS

boyzs
Starting Member

8 Posts

Posted - 2008-09-11 : 03:14:54

quote:
Originally posted by jsmith8858

I am not sure what your question/problem is ....

- Jeff
http://weblogs.sqlteam.com/JeffS


  The above query gives me output in this format When I pass Parameter as 'South Guj' in TerritoryName
 
FlavourName   QtySecSales   QtySchemeSales   Amount
 CSD            0              0               0
 JBD            20             3              200
 AF             0              0               0


But I need Output like this

FlavourName   QtySecSales   QtySchemeSales   Amount
 JBD            20             3              200


Hopes you got my problem...


Thank You

Boyzs

boyzs
Starting Member

8 Posts

Posted - 2008-09-11 : 03:24:48

[code]
Thank You To All

I had got the solution now my Query is working...
Just check one condition that <> 0

But there might be some another way to write the query...
How can I join this four tables for doing sum...

[/code]

Thank You

Boyzs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources