Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1428
  • Last Modified:

SQL Loop Through Months

I have several values that I need to get on a per month basis making a column for each month.

Rather than doing a DATEPART with a CASE to get through the 12 months I would like to know if there is a function or SQL that could loop through this to reduce my code.
0
LCNW
Asked:
LCNW
  • 3
  • 3
  • 2
3 Solutions
 
Scott PletcherSenior DBACommented:
Not that I know of.

Btw, while you can use DATEPART in the column CASE statements, you should avoid it in the WHERE clause, instead using >= and <, similar to this:

SELECT
    SUM(CASE WHEN DATEPART(MONTH, datetime_column) = 1 THEN value_column ELSE 0 END) AS Jan_Total,
    SUM(CASE WHEN DATEPART(MONTH, datetime_column) = 2 THEN value_column ELSE 0 END) AS Feb_Total,
    ...
FROM dbo.tablename
WHERE
    datetime_column >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 12, 0)
0
 
LCNWAuthor Commented:
I'm doing it just as you stated. I was hoping to avoid 12 CASE statements.
0
 
PortletPaulCommented:
>>"I was hoping to avoid 12 CASE statements."
is 12 case expressions really that bad?

You might be able to use "pivot" (depending on the overall query), but in truth that's not going to be any simpler than those 12 case expressions.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LCNWAuthor Commented:
I have 14 fields so it's 168 CASE statements.
0
 
Scott PletcherSenior DBACommented:
You can generate the CASE statements using system tables, it's not as if you have to type them out yourself :-) .
0
 
LCNWAuthor Commented:
Can you elaborate? Just to clarify, this is MS SQL.
0
 
PortletPaulCommented:
I suspect Scott is referring to a query against the meta data of your table, and using concatenations to produce an output that is the case expressions.

I'd just do the first 12 using an editor, then replicate that and replace field1 with field2 etc and copy/paste each block of 12 into another file until I had all the case expressions I needed
, SUM(CASE WHEN DATEPART(MONTH, datetime_column) = 1  THEN field1 ELSE 0 END) AS Jan_field1
, SUM(CASE WHEN DATEPART(MONTH, datetime_column) = 2  THEN field1 ELSE 0 END) AS Feb_field1
, SUM(CASE WHEN DATEPART(MONTH, datetime_column) = 3  THEN field1 ELSE 0 END) AS Mar_field1
, SUM(CASE WHEN DATEPART(MONTH, datetime_column) = 5  THEN field1 ELSE 0 END) AS May_field1
, SUM(CASE WHEN DATEPART(MONTH, datetime_column) = 6  THEN field1 ELSE 0 END) AS Jun_field1
, SUM(CASE WHEN DATEPART(MONTH, datetime_column) = 7  THEN field1 ELSE 0 END) AS Jul_field1
, SUM(CASE WHEN DATEPART(MONTH, datetime_column) = 8  THEN field1 ELSE 0 END) AS Aug_field1
, SUM(CASE WHEN DATEPART(MONTH, datetime_column) = 9  THEN field1 ELSE 0 END) AS Sep_field1
, SUM(CASE WHEN DATEPART(MONTH, datetime_column) = 10 THEN field1 ELSE 0 END) AS Oct_field1
, SUM(CASE WHEN DATEPART(MONTH, datetime_column) = 11 THEN field1 ELSE 0 END) AS Nov_field1
, SUM(CASE WHEN DATEPART(MONTH, datetime_column) = 12 THEN field1 ELSE 0 END) AS Dec_field1

, SUM(CASE WHEN DATEPART(MONTH, datetime_column) = 1  THEN field2 ELSE 0 END) AS Jan_field2
, SUM(CASE WHEN DATEPART(MONTH, datetime_column) = 2  THEN field2 ELSE 0 END) AS Feb_field2
, SUM(CASE WHEN DATEPART(MONTH, datetime_column) = 3  THEN field2 ELSE 0 END) AS Mar_field2
, SUM(CASE WHEN DATEPART(MONTH, datetime_column) = 5  THEN field2 ELSE 0 END) AS May_field2
, SUM(CASE WHEN DATEPART(MONTH, datetime_column) = 6  THEN field2 ELSE 0 END) AS Jun_field2
, SUM(CASE WHEN DATEPART(MONTH, datetime_column) = 7  THEN field2 ELSE 0 END) AS Jul_field2
, SUM(CASE WHEN DATEPART(MONTH, datetime_column) = 8  THEN field2 ELSE 0 END) AS Aug_field2
, SUM(CASE WHEN DATEPART(MONTH, datetime_column) = 9  THEN field2 ELSE 0 END) AS Sep_field2
, SUM(CASE WHEN DATEPART(MONTH, datetime_column) = 10 THEN field2 ELSE 0 END) AS Oct_field2
, SUM(CASE WHEN DATEPART(MONTH, datetime_column) = 11 THEN field2 ELSE 0 END) AS Nov_field2
, SUM(CASE WHEN DATEPART(MONTH, datetime_column) = 12 THEN field2 ELSE 0 END) AS Dec_field2

, SUM(CASE WHEN DATEPART(MONTH, datetime_column) = 1  THEN field3 ELSE 0 END) AS Jan_field3
, SUM(CASE WHEN DATEPART(MONTH, datetime_column) = 2  THEN field3 ELSE 0 END) AS Feb_field3
, SUM(CASE WHEN DATEPART(MONTH, datetime_column) = 3  THEN field3 ELSE 0 END) AS Mar_field3
, SUM(CASE WHEN DATEPART(MONTH, datetime_column) = 5  THEN field3 ELSE 0 END) AS May_field3
, SUM(CASE WHEN DATEPART(MONTH, datetime_column) = 6  THEN field3 ELSE 0 END) AS Jun_field3
, SUM(CASE WHEN DATEPART(MONTH, datetime_column) = 7  THEN field3 ELSE 0 END) AS Jul_field3
, SUM(CASE WHEN DATEPART(MONTH, datetime_column) = 8  THEN field3 ELSE 0 END) AS Aug_field3
, SUM(CASE WHEN DATEPART(MONTH, datetime_column) = 9  THEN field3 ELSE 0 END) AS Sep_field3
, SUM(CASE WHEN DATEPART(MONTH, datetime_column) = 10 THEN field3 ELSE 0 END) AS Oct_field3
, SUM(CASE WHEN DATEPART(MONTH, datetime_column) = 11 THEN field3 ELSE 0 END) AS Nov_field3
, SUM(CASE WHEN DATEPART(MONTH, datetime_column) = 12 THEN field3 ELSE 0 END) AS Dec_field3

Open in new window

0
 
Scott PletcherSenior DBACommented:
Should be something along these lines:

SELECT REPLACE(REPLACE(
    'SUM(CASE WHEN DATEPART(MONTH, $datetime_column$) = 1 THEN $value_column$ ELSE 0 END) AS [Jan_Total_$value_column$],',
    '$datetime_column$', 'datetime_column'),
    '$value_column$', CAST(c.name AS varchar(128)))
FROM sys.columns c
INNER JOIN sys.types t ON
    t.system_type_id = c.system_type_id
WHERE
    c.object_id = OBJECT_ID('table_name') AND
    (c.name LIKE 'value%') -- OR name NOT IN/LIKE ('...', '...', ) --OR t.name = 'decimal' --OR ...
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now