SQL Query Arithmetic - 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 Query Arithmetic

Author

Topic

boku
Starting Member

5 Posts

Posted - 2009-08-23 : 04:00:15

Hi Guys, been looking at this way too long now and figured out most of it but cannot finish the last little bit ... please help

This is my code

[CODE]
SELECT "ACCT",
(
SELECT ISNULL(SUM("GOODS"),0)
+
(SELECT ISNULL(SUM("GOODS"),0) FROM INVHDR WHERE "ACCT"='WEBB' AND "INVTYPE"=3 AND ("DATE" >= '2009-07-01 00:00:00.000' AND "DATE" <= '2010-06-30 00:00:00.000') AND "STATUS"<>9)
-
(SELECT ISNULL(SUM("GOODS"),0) FROM INVHDR WHERE "ACCT"='WEBB' AND "INVTYPE"=4 AND ("DATE" >= '2009-07-01 00:00:00.000' AND "DATE" <= '2010-06-30 00:00:00.000') AND "STATUS"<>9)
+
(SELECT ISNULL(SUM("GOODS"),0) FROM INVHDR WHERE "ACCT"='WEBB' AND "INVTYPE"=5 AND ("DATE" >= '2009-07-01 00:00:00.000' AND "DATE" <= '2010-06-30 00:00:00.000') AND "STATUS"<>9)
+
(SELECT ISNULL(SUM("GOODS"),0) FROM INVHDR WHERE "ACCT"='WEBB' AND "INVTYPE"=6 AND ("DATE" >= '2009-07-01 00:00:00.000' AND "DATE" <= '2010-06-30 00:00:00.000') AND "STATUS"<>9)
+
(SELECT ISNULL(SUM("GOODS"),0) FROM INVHDR WHERE "ACCT"='WEBB' AND "INVTYPE"=7 AND ("DATE" >= '2009-07-01 00:00:00.000' AND "DATE" <= '2010-06-30 00:00:00.000') AND "STATUS"<>9)
+
(SELECT ISNULL(SUM("GOODS"),0) FROM INVHDR WHERE "ACCT"='WEBB' AND "INVTYPE"=8 AND ("DATE" >= '2009-07-01 00:00:00.000' AND "DATE" <= '2010-06-30 00:00:00.000') AND "STATUS"<>9)
FROM INVHDR WHERE "ACCT"='WEBB' AND "INVTYPE"=1 AND ("DATE" >= '2009-07-01 00:00:00.000' AND "DATE" <= '2010-06-30 00:00:00.000') AND "STATUS"<>9
)
"GRAND TOTAL" FROM INVHDR WHERE "ACCT"='WEBB' AND "STATUS"<>9 AND ("DATE" >= '2009-07-01 00:00:00.000' AND "DATE" <= '2010-06-30 00:00:00.000') GROUP BY "ACCT" ORDER BY "GRAND TOTAL" DESC
[/CODE]

What I need to do though is NOT be account "ACCT" specific.

In other words I need to select the top 10 customers AFTER the "GRAND TOTAL" has been worked out for all customers.

I'm lost, please help me

thank you

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-23 : 04:20:39

[code]SELECT ACCT,
SUM( CASE
WHEN INVTYPE IN (1, 3, 5, 6, 7, 8) THEN GOODS
WHEN INVTYPE = 4 THEN -GOODS
ELSE 0
END
) AS [GRAND TOTAL]
FROM INVHDR
WHERE ACCT = 'WEBB'
AND [STATUS] <>9
AND [DATE] >= '20090701'
AND [DATE] < '20100701'
GROUP BY ACCT
ORDER BY [GRAND TOTAL] DESC[/code]

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

boku
Starting Member

5 Posts

Posted - 2009-08-23 : 06:12:24

[resolved]

SELECT TOP 10 ACCT, SUM(CASE WHEN INVTYPE IN (1,3,5,6,7,8) THEN GOODS WHEN INVTYPE IN (4) THEN -1 * GOODS END) AS "GOODS" FROM INVHDR WHERE "DATE" >= '2009-07-01 00:00:00.000' AND "DATE" <= '2010-06-30 00:00:00.000' AND "STATUS" <> 9 AND "INVTYPE" <= 8 GROUP BY "ACCT" ORDER BY "GOODS" DESC

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-23 : 09:29:21

No. Where is the Acct = 'WEBB' part in your suggestion?

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

IncisiveOne
Starting Member

36 Posts

Posted - 2009-08-24 : 11:23:39

Excellent coordinates, Peso.

Anything worth doing, is worth doing right.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources