Solved

SQL Loop Through Months

Posted on 2014-04-08
8
1,115 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
ID: 39987344
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
ID: 39987351
I'm doing it just as you stated. I was hoping to avoid 12 CASE statements.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39987361
>>"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
ID: 39987365
I have 14 fields so it's 168 CASE statements.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39987415
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
ID: 39987481
Can you elaborate? Just to clarify, this is MS SQL.
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 167 total points
ID: 39987525
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
ID: 39988754
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

912 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

17 Experts available now in Live!

Get 1:1 Help Now