CODEWITHSUNDEEP
sql-serverdatetimepartition
baobobs
baobobs

Reputation: 703

Summarizing month columns to fiscal year with SQL Server

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:

enter image description here

Output:

enter image description here

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

Upvotes: 0

Views: 710

Answers (2)

pavan kumar
pavan kumar

Reputation: 322

[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.sstatic.net/jio6c.png

Upvotes: 1

ZLK
ZLK

Reputation: 2874

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)

Upvotes: 1

Related Questions