MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » August 2014

RE: Another SQL question



fixed

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





Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact