Solved

SQL Loop Through Months

Posted on 2014-04-08
8
1,265 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 69

Accepted Solution

by:
Scott Pletcher 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 49

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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 1

Author Comment

by:LCNW
ID: 39987365
I have 14 fields so it's 168 CASE statements.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
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 49

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:Scott Pletcher
Scott Pletcher 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

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…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

729 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