Solved

SQL Loop Through Months

Posted on 2014-04-08
8
1,084 Views
Last Modified: 2014-04-11
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
Comment
Question by:LCNW
  • 3
  • 3
  • 2
8 Comments
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 333 total points
Comment Utility
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
 
LVL 1

Author Comment

by:LCNW
Comment Utility
I'm doing it just as you stated. I was hoping to avoid 12 CASE statements.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
>>"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
 
LVL 1

Author Comment

by:LCNW
Comment Utility
I have 14 fields so it's 168 CASE statements.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
You can generate the CASE statements using system tables, it's not as if you have to type them out yourself :-) .
0
 
LVL 1

Author Comment

by:LCNW
Comment Utility
Can you elaborate? Just to clarify, this is MS SQL.
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 167 total points
Comment Utility
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
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 333 total points
Comment Utility
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now