mssql Next 12 months concatenate with year dynamically

Question

I have this query below where I'm retrieving some data and displaying by month. Would like to make this dynamic so I don't have any hard-coded dates there. What would be the most efficient way to declare and use some dates variable in my query?

SELECT Description, ComponentDescription,
SUM(CASE WHEN FMonth+CAST(YearOrder AS NVARCHAR(10)) = 'Dec2015' THEN    Forecast ELSE 0 END ) AS 'Dec2015',
SUM(CASE WHEN FMonth+CAST(YearOrder AS NVARCHAR(10)) = 'Jan2016' THEN Forecast ELSE 0 END ) AS 'Jan2016',
SUM(CASE WHEN FMonth+CAST(YearOrder AS NVARCHAR(10)) = 'Feb2016' THEN Forecast ELSE 0 END ) AS 'Feb2016',
SUM(CASE WHEN FMonth+CAST(YearOrder AS NVARCHAR(10)) = 'Mar2016' THEN Forecast ELSE 0 END ) AS 'Mar2016',
SUM(CASE WHEN FMonth+CAST(YearOrder AS NVARCHAR(10)) = 'Apr2016' THEN Forecast ELSE 0 END ) AS 'Apr2016',
SUM(CASE WHEN FMonth+CAST(YearOrder AS NVARCHAR(10)) = 'May2016' THEN Forecast ELSE 0 END ) AS 'May2016',
SUM(CASE WHEN FMonth+CAST(YearOrder AS NVARCHAR(10)) = 'Jun2016' THEN Forecast ELSE 0 END ) AS 'Jun2016',
SUM(CASE WHEN FMonth+CAST(YearOrder AS NVARCHAR(10)) = 'Jul2016' THEN Forecast ELSE 0 END ) AS 'Jul2016',
SUM(CASE WHEN FMonth+CAST(YearOrder AS NVARCHAR(10)) = 'Aug2016' THEN Forecast ELSE 0 END ) AS 'Aug2016',
SUM(CASE WHEN FMonth+CAST(YearOrder AS NVARCHAR(10)) = 'Sep2016' THEN Forecast ELSE 0 END ) AS 'Sep2016',
SUM(CASE WHEN FMonth+CAST(YearOrder AS NVARCHAR(10)) = 'Oct2016' THEN Forecast ELSE 0 END ) AS 'Oct2016',
SUM(CASE WHEN FMonth+CAST(YearOrder AS NVARCHAR(10)) = 'Nov2016' THEN Forecast ELSE 0 END ) AS 'Nov2016',
SUM(CASE WHEN FMonth+CAST(YearOrder AS NVARCHAR(10)) = 'Dec2016' THEN Forecast ELSE 0 END ) AS 'Dec2016'

FROM (SELECT Description, ComponentDescription, FMonth, Forecast, YearOrder FROM Table1 ) AS NormalizedData
GROUP BY Description, ComponentDescription
ORDER BY Description, ComponentDescription;

Thanks in advance.

A recursive CTE would be pretty efficient. – Tab Alleman Dec 28 '15 at 18:48 — Tab Alleman, Dec 28 '15 at 18:48

jgreve · Answer 1 · 2015-12-31T23:44:38.110

edit: followup r.e. comment "add another column (total) which will sum horizontally. i.e. for item x, I want to show sum of all months. How can I achieve that?"

Let's try this query, see the /* new */ comment below for an AllMonths total column. That sub-query has a where-clause which ignores year & month so the sum(totalForecast) should generate the grand total for each of the Description + ComponentDescription (temp columns d & cd, respectively).

changes: Fixed temp column names in sub-queries where clause (was where y.c = x.c earlier, changed to where y.d = x.d, just a typo).

Fixed the sub-queries (changed from select * from monthlyCommissions y" to just "select totalForecast from monthlyCommissions); select * was just wrong, needs a single value to work (this is what happens when I don't test my code).

Modified query:

     with monthlyCommissions ( d, cd, YearOrder, FMonth,  totalForecast ) as ( 
       -- Column order has to match in the both the with...as... & this nested select.
       -- I try keep names similar to simplify editing.
       select Description, ComponentDescription, YearOrder, FMonth,  sum(Forecast) as totalForecast
       from Table1
       -- I added the where clause to limit results to Dec2015 or anything in 2016.
       where (YearOrder = 2015 and FMonth = 'Dec')
          or (YearOrder = 2016)
       group by Description, ComponentDescription, YearOrder, FMonth
     )
     select Description
          , ComponentDescription
          , count(*) as entries
/* new */ , (select sum(totalForecast) from monthlyCommissions y where y.d = x.d and y.cd = x.cd ) as AllMonths
          , (select totalForecast from monthlyCommissions y where y.d = x.d and y.cd = x.cd and y.YearOrder = 2015 y.FMonth = 'Dec') as prev_Dec
          , (select totalForecast from monthlyCommissions y where y.d = x.d and y.cd = x.cd and y.YearOrder = 2016 y.FMonth = 'Jan') as Jan
          , (select totalForecast from monthlyCommissions y where y.d = x.d and y.cd = x.cd and y.YearOrder = 2016 y.FMonth = 'Feb') as Feb
          ...etc...
          , (select totalForecast from monthlyCommissions y where y.d = x.d and y.cd = x.cd and y.YearOrder = 2016 y.FMonth = 'Dec') as Dec
     from monthlyCommissions x
     group by d, cd
     order by d, cd

original answer:

What if you start this way...? Caution: untested code ahead. I don't have a sqlserver instance nearby to test this, but it should point you in a useful (if not optimal) direction.

At any rate, this would get rid of many of the hardcoded values:

Note that I'm assuming the original table has FMonth as char-like and YearOrder as some kind of integer.

with monthlyCommissions ( Description, ComponentDescription, YearOrder, FMonth,  totalForecast ) as ( 
  -- Column order has to match in the both the with...as... & this nested select.
  -- I try keep names the same to simplify editing.
  select Description, ComponentDescription, YearOrder, FMonth,  sum(Forecast) as totalForecast
  from Table1
  -- I added the where clause to limit results to Dec2015 or anything in 2016.
  where (YearOrder = 2015 and FMonth = 'Dec')
     or (YearOrder = 2016)
  group by Description, ComponentDescription, FMonth, YearOrder
)
select * from monthlyCommissions
order by Description, ComponentDescription, FMonth, YearOrder

My goal in using the with..as.. syntax is to set up a temporary result set in 'monthlyCommissions' that you can use for generating the kind of result set your current query does. (If you haven't used the "with...as.." style before, start with executing the inner select by itslef to see what it does.)

Assuming the above code worked, let's make something that looks more like your original query by consolidating it into the single row per Description + ComponentDescription combination:

with monthlyCommissions ( Description, ComponentDescription, YearOrder, FMonth,  totalForecast ) as ( 
  -- Column order has to match in the both the with...as... & this nested select.
  -- I try keep names the same to simplify editing.
  select Description as Description, ComponentDescription, YearOrder, FMonth,  sum(Forecast) as totalForecast
  from Table1
  -- I added the where clause to limit results to Dec2015 or anything in 2016.
  where (YearOrder = 2015 and FMonth = 'Dec')
     or (YearOrder = 2016)
  group by Description, ComponentDescription, FMonth, YearOrder
)
select Description
     , ComponentDescription
     , count(*) as entries
     , (select * from monthlyCommissions y where y.c = x.c and y.cd = x.cd and y.YearOrder = 2015 y.FMonth = 'Dec') as prev_Dec
     , (select * from monthlyCommissions y where y.c = x.c and y.cd = x.cd and y.YearOrder = 2016 y.FMonth = 'Jan') as Jan
     , (select * from monthlyCommissions y where y.c = x.c and y.cd = x.cd and y.YearOrder = 2016 y.FMonth = 'Feb') as Feb
     ...etc...
     , (select * from monthlyCommissions y where y.c = x.c and y.cd = x.cd and y.YearOrder = 2016 y.FMonth = 'Dec') as Dec
from monthlyCommissions x
group by Description, ComponentDescription
order by Description, ComponentDescription

There are a number of ways you could parameterize your target year.

The easiest would probably be to use the T-SQL variable notation...

declare @targetYear int = 2016
with monthlyCommissions ( Description, ComponentDescription, YearOrder, FMonth,  totalForecast ) as ( 
  -- Column order has to match in the both the with...as... & this nested select.
  -- I try keep names the same to simplify editing.
  select Description, ComponentDescription as cd, YearOrder, FMonth,  sum(Forecast) as totalForecast
  from Table1
  -- Adjusted where clause to pay attention to @targetYear
  where (YearOrder + 1 = @targetYear and FMonth = 'Dec')
     or (YearOrder = @targetYear )
  group by Description, ComponentDescription, FMonth, YearOrder
)
select Description
     , ComponentDescription
     , count(*) as entries
     , (select * from monthlyCommissions y where y.c = x.c and y.cd = x.cd and y.YearOrder + 1 = @targetYear y.FMonth = 'Dec') as prev_Dec
     , (select * from monthlyCommissions y where y.c = x.c and y.cd = x.cd and y.YearOrder = @targetYear y.FMonth = 'Jan') as Jan
     , (select * from monthlyCommissions y where y.c = x.c and y.cd = x.cd and y.YearOrder = @targetYear y.FMonth = 'Feb') as Feb
     ...etc...
     , (select * from monthlyCommissions y where y.c = x.c and y.cd = x.cd and y.YearOrder = @targetYear y.FMonth = 'Dec') as Dec
from monthlyCommissions x
group by Description, ComponentDescription
order by Description, ComponentDescription

Note that I'm doing something strange in the Where clauses. Specifically I'm doing this sort of thing:

where YearOrder + 1 = @targetYear

I remember that sqlserver complains if you try modifying the @targetYear in expressions: e.g. I expect this (the intuitive way) to cause problems:

where YearOrder = @targetYear - 1

You could also set up two variables... (I'll have to test the syntax and update this if I have any of it wrong):

declare @targetYear int = 2016
declare @priorYear int = @targetYear - 1

I'm sure there are other cleaner ways to do this, but I hope this is somewhat helpful.

jgreve, Thanks for your prompt response. I'm not very good with sql and having a little trouble understanding the solution you provided. I came across another solution http://sqlfiddle.com/#!6/21f89/1 If you'll be kind enough and look at it and see what you think? the only problem with this one is, I'm not able to display the dates in a sequential order i.e. (Dec-15, Jan-16, Feb-16..... Dec-16) — Eclipse, Dec 29 '15 at 14:46
Looks ok, I'm realizing I need to study up on Pivots. I modified the end of your SQL example (over on sqlfiddle) to say PIVOT(SUM(Forecast) FOR date IN(' + @cols + N')) AS P order by P.YearOrder, P.MonthOrder which seems to work, the sample data in table1 comes out in year order 2015, then month order (only MonthOrder=12 for the 2015 records). — jgreve, Dec 29 '15 at 16:56
I meant to say, the test data in Table1 for the 2015 records only has months=12 . Also, take a look here and see if anything is helpful: https://stackoverflow.com/questions/24470/sql-server-pivot-examples — jgreve, Dec 29 '15 at 17:29
one quick question, If I want to add another column (total) which will sum horizontally. i.e. for item x, I want to show sum of all months. How can I achieve that? — Eclipse, Dec 31 '15 at 18:59
Hmm... I'm going to post a modified version at the top of the answer, let me know if that gets close - part of it fixes an error I think I made where I was selecting * instead of just totalForecast from the sub-selects. — jgreve, Dec 31 '15 at 20:06

mssql Next 12 months concatenate with year dynamically

1 Answers1