RE: Another SQL question

In a similar situation I created a view

CREATE VIEW INVSUMMARY AS (
SELECT INVDIDTE,
INVDINV#,
DEC((INVDIDTE) / 100, 6,0) AS INVMONTH,
SUM(INVDAMT) AS LHTOTAL,
SUM(INVDSCEXT) AS SCTOTAL,
SUM(INVDAMT + INVDSCEXT) AS TOTAL
FROM INVDETAIL
GROUP BY INVDIDTE, INVDINV#)

Then I can select from this view for the months that I want. INVDIDTE is a numeric (8,0) date in YYYYMMDD format. So INVMONTH ends up being YYYYMM. You can modify to just extract the year and month from you date field accordingly.

This provides flexibility because it can be used for any range of dates. I also have some views like this that do averages as one of the fields.

Scott

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Lance Gillespie
Sent: Monday, August 25, 2014 3:34 PM
To: 'Midrange Systems Technical Discussion'
Subject: Another SQL question

I need to sum two different values into two different totals by month, probably using a case statement.

I can make the classic SUM CASE WHEN work just fine:

SELECT [Year]=YEAR(OrderDate),
SUM(CASE WHEN MONTH(OrderDate) = 1 THEN SubTotal END) AS 'JANTOT'
,SUM(CASE WHEN MONTH(OrderDate) = 2 THEN SubTotal END) AS 'FEBTOT'
,SUM(CASE WHEN MONTH(OrderDate) = 3 THEN SubTotal END) AS 'MARTOT'
,SUM(CASE WHEN MONTH(OrderDate) = 4 THEN SubTotal END) AS 'APRTOT'
,SUM(CASE WHEN MONTH(OrderDate) = 5 THEN SubTotal END) AS 'MAYTOT'
,SUM(CASE WHEN MONTH(OrderDate) = 6 THEN SubTotal END) AS 'JUNTOT'
,SUM(CASE WHEN MONTH(OrderDate) = 7 THEN SubTotal END) AS 'JULTOT'
,SUM(CASE WHEN MONTH(OrderDate) = 8 THEN SubTotal END) AS 'AUGTOT'
,SUM(CASE WHEN MONTH(OrderDate) = 9 THEN SubTotal END) AS 'SEPTOT'
,SUM(CASE WHEN MONTH(OrderDate) = 10 THEN SubTotal END) AS 'OCTTOT'
,SUM(CASE WHEN MONTH(OrderDate) = 11 THEN SubTotal END) AS 'NOVTOT'
,SUM(CASE WHEN MONTH(OrderDate) = 12 THEN SubTotal END) AS 'DECTOT'
FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate)
ORDER BY [Year]

But each record also has a discount that I want totaled something like:

SELECT [Year]=YEAR(OrderDate),
SUM(CASE WHEN MONTH(OrderDate) = 1 THEN SubTotal END) AS 'JANTOT', DscTotal AS 'JANDSC'
,SUM(CASE WHEN MONTH(OrderDate) = 2 THEN SubTotal END) AS 'FEBTOT', DscTotal AS 'FEBDSC'
,SUM(CASE WHEN MONTH(OrderDate) = 3 THEN SubTotal END) AS 'MARTOT', DscTotal AS 'MARDSC'
,SUM(CASE WHEN MONTH(OrderDate) = 4 THEN SubTotal END) AS 'APRTOT', DscTotal AS 'APRDSC'
,SUM(CASE WHEN MONTH(OrderDate) = 5 THEN SubTotal END) AS 'MAYTOT', DscTotal AS 'MAYDSC'
,SUM(CASE WHEN MONTH(OrderDate) = 6 THEN SubTotal END) AS 'JUNTOT', DscTotal AS 'JUNDSC'
,SUM(CASE WHEN MONTH(OrderDate) = 7 THEN SubTotal END) AS 'JULTOT', DscTotal AS 'JULDSC'
,SUM(CASE WHEN MONTH(OrderDate) = 8 THEN SubTotal END) AS 'AUGTOT', DscTotal AS 'AUGDSC'
,SUM(CASE WHEN MONTH(OrderDate) = 9 THEN SubTotal END) AS 'SEPTOT', DscTotal AS 'SEPDSC'
,SUM(CASE WHEN MONTH(OrderDate) = 10 THEN SubTotal END) AS 'OCTTOT', DscTotal AS 'OCTDSC'
,SUM(CASE WHEN MONTH(OrderDate) = 11 THEN SubTotal END) AS 'NOVTOT', DscTotal AS 'NOVDSC'
,SUM(CASE WHEN MONTH(OrderDate) = 12 THEN SubTotal END) AS 'DECTOT', DscTotal AS 'DECDSC'
FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate)
ORDER BY [Year]

But of course that is not the correct syntax.

Am I just approaching the whole thing wrong?

Thanks,
lance