Summarizing month columns to fiscal year with SQL Server

Question

I want to summarize a table that is mostly made up of a sequence of months. Each month's column has a header in the format of MMMYY. I need to dynamically summarize this table by fiscal year. Our fiscal year starts in November, so the input to output would be as follows:

Input:

Output:

Does anyone have any suggestions on how I can go about doing this using SQL Server? I've thought about partitioning a temporary table containing all of the given months and years by the monthly fiscal year break (NovYY), but I haven't figured out how to even partition based on a LIKE keyword search. Bottom line - I'm completely stumped as to how to effectively do this without hard coding the dates.

Let's say the sample input above is dbo.USA_Historic_Txg_Revenue. I have the following so far:

select * into #temp from dbo.USA_Historic_Txg_Revenue

with MMMYY as
(
    select Name
    from tempdb.sys.columns 
    where object_id = OBJECT_ID('tempdb..#temp')
    and isnumeric(right(name, 2)) = 1
)
select * from MMMYY

You probably want to `UNPIVOT` then do a SUM statement that partitions based on whatever your fiscal year is. You may need to use dynamic SQL to produce all the columns for the UNPIVOT if there's too many columns or you don't know all the columns or you just don't want to make a mistake with leaving one out. — ZLK, Jan 18 '16 at 23:45

score 1 · Accepted Answer · answered Jan 19 '16 at 01:07

Something like this is what I think you're looking for:

--CREATE TABLE #table (Name NVARCHAR(100), Oct12 INT, Nov12 INT, Dec12 INT, Jan13 INT, Feb13 INT, Mar13 INT, Apr13 INT, May13 INT, Jun13 INT, Jul13 INT, Aug13 INT, Sep13 INT, Oct13 INT, Nov13 INT, Dec13 INT, Jan14 INT, Feb14 INT, Mar14 INT)
--INSERT #table VALUES ('Record1',5,3,2,0,1,5,4,3,6,5,4,2,1,0,1,1,1,2)
--, ('Record2',4,1,3,2,1,3,3,1,4,4,3,2,1,0,2,2,2,2);

DECLARE @monthlist NVARCHAR(MAX)
SELECT @monthlist = ISNULL(@monthlist + ',', '') + Name -- select top 100 *
FROM tempdb.sys.columns
WHERE [object_id] = OBJECT_ID('tempdb..#table')
AND Name <> 'Name';

DECLARE @SQL NVARCHAR(MAX) = 
'SELECT DISTINCT Name
    , SUM(Val) OVER (PARTITION BY Name, CASE WHEN Mth LIKE ''Nov%'' OR Mth LIKE ''Dec%'' THEN RIGHT(Mth, 2) ELSE RIGHT(Mth, 2) - 1 END) InsertColName
    , CASE WHEN Mth LIKE ''Nov%'' OR Mth LIKE ''Dec%'' THEN ''Nov'' + RIGHT(Mth, 2) ELSE ''Nov'' + CONVERT(NVARCHAR, RIGHT(Mth, 2) - 1) END FiscalStart
FROM #table
UNPIVOT (Val FOR Mth IN (' + @monthlist + ')) a
ORDER BY Name, CASE WHEN Mth LIKE ''Nov%'' OR Mth LIKE ''Dec%'' THEN ''Nov'' + RIGHT(Mth, 2) ELSE ''Nov'' + CONVERT(NVARCHAR, RIGHT(Mth, 2) - 1) END'
EXEC(@SQL)

For your particular query, you can derive monthlist directly from the table without the need of a temporary table...

e.g.

DECLARE @monthlist NVARCHAR(MAX)
SELECT @monthlist = ISNULL(@monthlist + ',', '') + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'USA_Historic_Txg_Revenue'
AND COLUMN_NAME <> 'Name';

DECLARE @SQL NVARCHAR(MAX) = 
'SELECT DISTINCT Name
    , SUM(Val) OVER (PARTITION BY Name, CASE WHEN Mth LIKE ''Nov%'' OR Mth LIKE ''Dec%'' THEN RIGHT(Mth, 2) ELSE RIGHT(Mth, 2) - 1 END) InsertColName
    , CASE WHEN Mth LIKE ''Nov%'' OR Mth LIKE ''Dec%'' THEN ''Nov'' + RIGHT(Mth, 2) ELSE ''Nov'' + CONVERT(NVARCHAR, RIGHT(Mth, 2) - 1) END FiscalStart
FROM dbo.USA_Historic_Txg_Revenue
UNPIVOT (Val FOR Mth IN (' + @monthlist + ')) a
ORDER BY Name, CASE WHEN Mth LIKE ''Nov%'' OR Mth LIKE ''Dec%'' THEN ''Nov'' + RIGHT(Mth, 2) ELSE ''Nov'' + CONVERT(NVARCHAR, RIGHT(Mth, 2) - 1) END'
EXEC(@SQL)

This is perfect. Thanks so much for your help @ZLK. The one additional step I had to do was re-pivot the results back so that they could be joined to the original table. — user1185790, Jan 19 '16 at 15:42

pavan kumar · Answer 2 · 2016-01-19T03:15:54.163

[Summarizing months columns using pivot][1]

---creating the tables
    CREATE TABLE [dbo].[Table1]([PID] [int] NULL,[ProductDesc] [nvarchar](50) NULL,[ProductCode] [nvarchar](10) NULL) ON [PRIMARY]
    CREATE TABLE [dbo].[Table2]([Date] [varchar](50) NULL,[PID] [int] NULL) ON [PRIMARY]

    ---insert script---
    INSERT [dbo].[Table1] ([PID], [ProductDesc], [ProductCode]) VALUES (1, N'Packet-Eye', N'P001')
    INSERT [dbo].[Table1] ([PID], [ProductDesc], [ProductCode]) VALUES (2, N'Wiggy', N'W099 ')
    INSERT [dbo].[Table1] ([PID], [ProductDesc], [ProductCode]) VALUES (3, N'Wimax-Lite', N'W001')
    INSERT [dbo].[Table1] ([PID], [ProductDesc], [ProductCode]) VALUES (4, N'Wimax-Home', N'e W002 ')
    INSERT [dbo].[Table2] ([Date], [PID]) VALUES (N'1/14/2009 ', 1)
    INSERT [dbo].[Table2] ([Date], [PID]) VALUES (N'1/15/2009 ', 1)
    INSERT [dbo].[Table2] ([Date], [PID]) VALUES (N'2/1/2009', 2)
    INSERT [dbo].[Table2] ([Date], [PID]) VALUES (N'3/3/2009', 3)
    GO
    SELECT *
    FROM
    (
      SELECT t1.productdesc as pd,COUNT(month(t2.date))as dates,
      DateName( month ,  DateAdd( month , MONTH(t2.date) , 0 ) - 1 ) as mon
      FROM table1 t1 inner join table2 t2 on t1.pid=t2.pid
      where year(date) between 2009
        and 2010 group by productdesc,month(t2.date),month (t2.date)
    ) AS D
    PIVOT
    (
    sum(dates)
       FOR mon IN( [January],[February],[March],[April],[May],[June],[July],[August],[September],[October],[November],[December])
    ) AS P


    [using pivot in sqlserver please find the result in the below image][1]

  [1]: https://i.stack.imgur.com/jio6c.png

Summarizing month columns to fiscal year with SQL Server

2 Answers2