query question - 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)

query question

Author

Topic

tunc260
Starting Member

5 Posts

Posted - 2009-05-29 : 08:03:56

hello, i m newbie for mssql

i try to write a sql query

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.TXTTARIH <= '20090430')
GROUP BY LNGCUSTCODE
HAVING (SUM(DBLGROSSAMOUNT) - SUM(DBLDISCOUNT))> 2000

some customers have some branches. that branch information save in another table ( TBLBRANCH )

TBLBRANCH is like that

CUSTCODE BRANCHCODE
100 101
100 102
100 103

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

so

i want to write a query list all customers' sales as combining branches with main.

our government want a report like above.

thanks for helping.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-29 : 08:32:09

Follow the guildelines here to get help faster
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

E 12°55'05.63"
N 56°04'39.26"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-29 : 08:48:45

what's LNGCUSTCODE in table TBLINVOICE ? is it the main code or branch code ?

KH
[spoiler]Time is always against us[/spoiler]

tunc260
Starting Member

5 Posts

Posted - 2009-05-29 : 09:00:37

i meant main code with main customer code, this customer have some branches in the city. actualy these , main customer code and its branches are same legally.

for example, TBLINVOICE is like that

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

legally 100,101,102 are the same customer. i should combine them when calculating sales.

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

i should compute customers' sales by combining its branches

thank u very much for helping. i can clarify more if u need

tunc260
Starting Member

5 Posts

Posted - 2009-05-29 : 09:03:08

main customer code and branch customer are different. ( 100 is the main customer code,
101,102,103 are its branches )

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-29 : 09:30:24

[code]
SELECT TB.CUSTCODE, (SUM(DBLGROSSAMOUNT) - SUM(DBLDISCOUNT)) AS SALES
FROM TBLINVOICE AS TF
INNER JOIN TBLCUSTOMER AS TM ON TF.LNGCUSTCODE = TM.LNGCUSTCODE
INNER JOIN TBLBRANCH AS TB ON TF.LNGCUSTCODE = TB. BRANCHCODE
WHERE (TF.TXTDATE >= '20090401')
AND (TF.TXTTARIH <= '20090430')
GROUP BY TB.CUSTCODE
HAVING (SUM(DBLGROSSAMOUNT) - SUM(DBLDISCOUNT))> 2000
[/code]

KH
[spoiler]Time is always against us[/spoiler]

tunc260
Starting Member

5 Posts

Posted - 2009-05-29 : 10:25:35

thank u very much for ur interest, i can make use of it, but it s not the result i want,

cos result of ur query only show customers exists in TBLBRANCH table , other customers , for example, 151 (cust_code) couldnt be seen in the result of ur query. 151 doesnt have any branch. it doesnt exist in TBLBRANCH table.

have a nice day

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources