Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1588
  • 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
 
PortletPaulfreelancerCommented:
>>"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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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
 
PortletPaulfreelancerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

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