Substract the Sum of two Doc Types in a Table

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

Substract the Sum of two Doc Types in a Table

Author

Topic

Trini
Starting Member

9 Posts

Posted - 2006-06-04 : 14:34:21

I need to subtract the values of the totals of two document types in a table. So far I have the following with the results as shown.

SELECT TOP 100 PERCENT { fn MONTHNAME(DOCDATE) } AS [MONTH], DOCID, SUM(DOCAMNT) AS TOTAL
FROM dbo.tblSales
WHERE (DOCID = 'RTD') OR
(DOCID = 'INV')
GROUP BY DOCID, { fn MONTHNAME(DOCDATE) }
ORDER BY DOCID DESC

This results in the following:
Month DOCID TOTAL
MAY RTD 165752.87
MAY INV 18149416.37

I need one value showing the difference of the totals.
Thanks.

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-06-04 : 18:49:02

This should work:

SELECT { fn MONTHNAME(DOCDATE) } AS [MONTH], DOCID, 
	SUM(CASE WHEN DOCID='RTD' THEN DOCAMNT ELSE 0 END - CASE WHEN DOCID='INV' THEN DOCAMNT ELSE 0 END)
FROM dbo.tblSales
WHERE DOCID IN ('RTD', 'INV')
GROUP BY DOCID, { fn MONTHNAME(DOCDATE) }
ORDER BY DOCID DESC

I haven't tested it, but the basic structure should do what you want.

Cheers,

Tim

Trini
Starting Member

9 Posts

Posted - 2006-06-04 : 19:35:01

quote:
Originally posted by timmy

This should work:
SELECT { fn MONTHNAME(DOCDATE) } AS [MONTH], DOCID,
SUM(CASE WHEN DOCID='RTD' THEN DOCAMNT ELSE 0 END - CASE WHEN DOCID='INV' THEN DOCAMNT ELSE 0 END)
FROM dbo.tblSales
WHERE DOCID IN ('RTD', 'INV')
GROUP BY DOCID, { fn MONTHNAME(DOCDATE) }
ORDER BY DOCID DESC

I haven't tested it, but the basic structure should do what you want.

Cheers,

Tim

Thanks Tim. This helps to return the sum of one of the Doc Types in a negative value. Which is part of the problem.
What I really need is to calculate sales for the Month lets say MAY.
So I am hoping to get a result in this format:

Presently the query returns:

|MONTH | DOCID | (NO COLUMN NAME)
|MAY | RTD | -165752.87000
|MAY | INV | 18149416.37000

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-04 : 20:32:47

remove the DOCID from SELECT AND GROUP BY

SELECT { fn MONTHNAME(DOCDATE) }  AS [MONTH], SUM(DOCAMNT) AS TOTAL
FROM   dbo.tblSales
WHERE DOCID IN ('RTD', 'INV')
GROUP BY { fn MONTHNAME(DOCDATE) }

Trini
Starting Member

9 Posts

Posted - 2006-06-04 : 21:18:24

quote:
Originally posted by khtan

remove the DOCID from SELECT AND GROUP BY
SELECT { fn MONTHNAME(DOCDATE) } AS [MONTH], SUM(DOCAMNT) AS TOTAL
FROM dbo.tblSales
WHERE DOCID IN ('RTD', 'INV')
GROUP BY { fn MONTHNAME(DOCDATE) }

KH

Thanks Khtan but the values of all Doc Types are positive.
To get the true value we need to subtract the total value of RTD docs from the total value of INV docs. Your code can work perfectly if we can convert the values of RTD DOCS to negative before the calculation.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-04 : 21:56:07

[code]SELECT { fn MONTHNAME(DOCDATE) } AS [MONTH],
SUM(case when DOCID = 'RTD' then -1 * DOCAMNT else DOCAMNT end) AS TOTAL
FROM dbo.tblSales
WHERE DOCID IN ('RTD', 'INV')
GROUP BY { fn MONTHNAME(DOCDATE) }[/code]

Trini
Starting Member

9 Posts

Posted - 2006-06-05 : 05:56:08

quote:

Originally posted by khtan

SELECT { fn MONTHNAME(DOCDATE) }  AS [MONTH], 
       SUM(case when DOCID = 'RTD' then -1 * DOCAMNT else DOCAMNT end) AS TOTAL
FROM   dbo.tblSales
WHERE  DOCID IN ('RTD', 'INV')
GROUP BY { fn MONTHNAME(DOCDATE) }

Thanks much. Works like a charm.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources