SQL JOINING TABLES - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

SQL JOINING TABLES

Author

Topic

tunc260
Starting Member

5 Posts

Posted - 2009-05-30 : 09:53:27

i want to write a query list all customers' sales. there is a complexity. we should combine main customers with branches of them.

TBLCUSTOMER is like that

LNGCUSTCODE TXTNAME
100 WALMART ( center )
101 WALMART ( Texas )
102 WALMART ( LA )
151 PIN RETAIL MARKET

actualy 100,101,102 are same customer. we should regard as one customer when calculating its sales.

there is a tblbranch table , like that,

CUSTCODE BRANCHCODE
100 101
100 102
100 103

100 is the main customer
101,102,103 are branches of it

and also we have a tblinvoice which holds sales data

TBLINVOICE is like that

LNGCUSTCODE DDBLGROSSAMOUNT DBLDISCOUNT
100 500 2
101 100 3
151 150 10
102 200 0
121 200 10

according to these table , i should get this report as a result of the query we build.

customer sales
100 795 which gotten from ((500+100+200)-5)
151 140 ( this customer doesnt have any branch so
( 150-10 )
121 91

actualy customer 100's sales is the total sales of 100,101 and 102

i should compute customers' sales by combining its branches

i builded a query like below , but i couldnt combine customers who have branches.

SELECT LNGCUSTCODE, (SUM(DBLGROSSAMOUNT) - SUM(DBLDISCOUNT)) AS SALES
FROM TBLINVOICE TF , TBLCUSTOMER AS TM
WHERE TF.LNGCUSTCODE = TM.LNGCUSTCODE
AND (TF.TXTDATE >= '20090401') AND (TF.TXTDATE <= '20090430')
GROUP BY LNGCUSTCODE
HAVING (SUM(DBLGROSSAMOUNT) - SUM(DBLDISCOUNT))> 2000

Thank u very much for ur intention.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2009-05-30 : 12:40:58

[code]CREATE TABLE #TBLCUSTOMER
(LNGCUSTCODE int, TXTNAME varchar(100))
INSERT #TBLCUSTOMER
SELECT 100, 'WALMART ( center )' UNION ALL
SELECT 101, 'WALMART ( Texas )' UNION ALL
SELECT 102, 'WALMART ( LA )' UNION ALL
SELECT 121, 'unknown' UNION ALL
SELECT 151, 'PIN RETAIL MARKET'

CREATE TABLE #TBLBRANCH
(CUSTCODE int, BRANCHCODE int)
INSERT #TBLBRANCH
SELECT 100, 101 UNION ALL
SELECT 100, 102 UNION ALL
SELECT 100, 103

CREATE TABLE #TBLINVOICE
(LNGCUSTCODE int, DBLGROSSAMOUNT float, DBLDISCOUNT float)
INSERT #TBLINVOICE
SELECT 100, 500, 2 UNION ALL
SELECT 101, 100, 3 UNION ALL
SELECT 151, 150, 10 UNION ALL
SELECT 102, 200, 0 UNION ALL
SELECT 121, 200, 10

SELECT coalesce(TB.CUSTCODE, TC.LNGCUSTCODE) AS CUSTCODE, (SUM(TI.DBLGROSSAMOUNT) - SUM(TI.DBLDISCOUNT)) AS SALES
FROM #TBLINVOICE TI
INNER JOIN #TBLCUSTOMER TC ON TC.LNGCUSTCODE = TI.LNGCUSTCODE
LEFT JOIN #TBLBRANCH TB ON TB.BRANCHCODE = TC.LNGCUSTCODE
GROUP BY coalesce(TB.CUSTCODE, TC.LNGCUSTCODE)
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources