Need SQL script for: Rolling Total By Year and Month

[bingo...@gmail.com]

Using version: SQL Server 2008 R2

I have data as seen in the [Input] table. The output needs to be data as seen in the [Output] table.
I will have data for Years: 2016 and 2017 (Basically current and next year) .

The calculations need to be from Next Month (August) onwards based on the current month values.
The rolling total needs to be calculated till the end of next year (ie Dec 2017).

Logic:
Category:Actuals (High/Medium/Low) need to be calculated from Aug 2016 onwards
based on the High/Medium/Low values from category: Approved_Totals (If High/Medium/Low...whichever row is available).

For ex: Actuals-High (Aug 2016) = Actuals-High (Jul 2016) + Approved_Totals-High (Jul 2016) (If its available)

Further the data grouping on the first column also needs to be maintained.(I am showing just one value [A], but there could be other values in this same column).
Please help with SQL script, assuming that data provided is for current and next year. And the calculation needs to start for Next Month onwards (based on current month value).



------------------------------------------------------------------------------------------------


CREATE TABLE [dbo].[Input](
[ResourceType] [nvarchar](255) NULL,
[Category] [nvarchar](255) NULL,
[Cost] [nvarchar](255) NULL,
[Yr] [float] NULL,
[Jan] [float] NULL,
[feb] [float] NULL,
[Mar] [float] NULL,
[Apr] [float] NULL,
[may] [float] NULL,
[Jun] [float] NULL,
[Jul] [float] NULL,
[Aug] [float] NULL,
[Sep] [float] NULL,
[Oct] [float] NULL,
[Nov] [float] NULL,
[Dec] [float] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Input] ([ResourceType], [Category], [Cost], [Yr], [Jan], [feb], [Mar], [Apr], [may], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec]) VALUES (N'A', N'Actuals', N'HIGH', 2016, 0, 0, 0, 0, 0, 0, 1992, 0, 0, 0, 0, 0)
INSERT [dbo].[Input] ([ResourceType], [Category], [Cost], [Yr], [Jan], [feb], [Mar], [Apr], [may], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec]) VALUES (N'A', N'Actuals', N'LOW', 2016, 0, 0, 0, 0, 0, 0, 1044, 0, 0, 0, 0, 0)
INSERT [dbo].[Input] ([ResourceType], [Category], [Cost], [Yr], [Jan], [feb], [Mar], [Apr], [may], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec]) VALUES (N'A', N'Actuals', N'MED', 2016, 0, 0, 0, 0, 0, 0, 1521, 0, 0, 0, 0, 0)
INSERT [dbo].[Input] ([ResourceType], [Category], [Cost], [Yr], [Jan], [feb], [Mar], [Apr], [may], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec]) VALUES (N'A', N'Approved_Total', N'HIGH', 2016, 0, 0, 0, 0, 0, 0, 0, 1, 1, -2, 0, 1)
INSERT [dbo].[Input] ([ResourceType], [Category], [Cost], [Yr], [Jan], [feb], [Mar], [Apr], [may], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec]) VALUES (N'A', N'Approved_Total', N'LOW', 2016, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0)
INSERT [dbo].[Input] ([ResourceType], [Category], [Cost], [Yr], [Jan], [feb], [Mar], [Apr], [may], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec]) VALUES (N'A', N'Approved_Total', N'MED', 2016, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0)
INSERT [dbo].[Input] ([ResourceType], [Category], [Cost], [Yr], [Jan], [feb], [Mar], [Apr], [may], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec]) VALUES (N'A', N'Actuals', N'HIGH', 2017, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
INSERT [dbo].[Input] ([ResourceType], [Category], [Cost], [Yr], [Jan], [feb], [Mar], [Apr], [may], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec]) VALUES (N'A', N'Actuals', N'LOW', 2017, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
INSERT [dbo].[Input] ([ResourceType], [Category], [Cost], [Yr], [Jan], [feb], [Mar], [Apr], [may], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec]) VALUES (N'A', N'Actuals', N'MED', 2017, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
INSERT [dbo].[Input] ([ResourceType], [Category], [Cost], [Yr], [Jan], [feb], [Mar], [Apr], [may], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec]) VALUES (N'A', N'Approved_Total', N'HIGH', 2017, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)

GO


CREATE TABLE [dbo].[Output](
[ResourceType] [nvarchar](255) NULL,
[Category] [nvarchar](255) NULL,
[Cost] [nvarchar](255) NULL,
[Yr] [float] NULL,
[Jan] [float] NULL,
[feb] [float] NULL,
[Mar] [float] NULL,
[Apr] [float] NULL,
[may] [float] NULL,
[Jun] [float] NULL,
[Jul] [float] NULL,
[Aug] [float] NULL,
[Sep] [float] NULL,
[Oct] [float] NULL,
[Nov] [float] NULL,
[Dec] [float] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Output] ([ResourceType], [Category], [Cost], [Yr], [Jan], [feb], [Mar], [Apr], [may], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec]) VALUES (N'A', N'Actuals', N'HIGH', 2016, 0, 0, 0, 0, 0, 0, 1992, 1992, 1993, 1994, 1992, 1992)
INSERT [dbo].[Output] ([ResourceType], [Category], [Cost], [Yr], [Jan], [feb], [Mar], [Apr], [may], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec]) VALUES (N'A', N'Actuals', N'LOW', 2016, 0, 0, 0, 0, 0, 0, 1044, 1045, 1045, 1045, 1045, 1045)
INSERT [dbo].[Output] ([ResourceType], [Category], [Cost], [Yr], [Jan], [feb], [Mar], [Apr], [may], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec]) VALUES (N'A', N'Actuals', N'MED', 2016, 0, 0, 0, 0, 0, 0, 1521, 1521, 1521, 1521, 1521, 1523)
INSERT [dbo].[Output] ([ResourceType], [Category], [Cost], [Yr], [Jan], [feb], [Mar], [Apr], [may], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec]) VALUES (N'A', N'Approved_Total', N'HIGH', 2016, 0, 0, 0, 0, 0, 0, 0, 1, 1, -2, 0, 1)
INSERT [dbo].[Output] ([ResourceType], [Category], [Cost], [Yr], [Jan], [feb], [Mar], [Apr], [may], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec]) VALUES (N'A', N'Approved_Total', N'LOW', 2016, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0)
INSERT [dbo].[Output] ([ResourceType], [Category], [Cost], [Yr], [Jan], [feb], [Mar], [Apr], [may], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec]) VALUES (N'A', N'Approved_Total', N'MED', 2016, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0)
INSERT [dbo].[Output] ([ResourceType], [Category], [Cost], [Yr], [Jan], [feb], [Mar], [Apr], [may], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec]) VALUES (N'A', N'Actuals', N'HIGH', 2017, 1993, 1994, 1994, 1994, 1994, 1994, 1994, 1994, 1994, 1994, 1994, 1995)
INSERT [dbo].[Output] ([ResourceType], [Category], [Cost], [Yr], [Jan], [feb], [Mar], [Apr], [may], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec]) VALUES (N'A', N'Actuals', N'LOW', 2017, 1045, 1045, 1045, 1045, 1045, 1045, 1045, 1045, 1045, 1045, 1045, 1045)
INSERT [dbo].[Output] ([ResourceType], [Category], [Cost], [Yr], [Jan], [feb], [Mar], [Apr], [may], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec]) VALUES (N'A', N'Actuals', N'MED', 2017, 1523, 1523, 1523, 1523, 1523, 1523, 1523, 1523, 1523, 1523, 1523, 1523)
INSERT [dbo].[Output] ([ResourceType], [Category], [Cost], [Yr], [Jan], [feb], [Mar], [Apr], [may], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec]) VALUES (N'A', N'Approved_Total', N'HIGH', 2017, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0)





select * from [dbo].[Input]


select * from [dbo].[Output]
-----------------------------------------------------------------------------------------------

[Erland Sommarskog]

(bingo...@gmail.com) writes:
> Using version: SQL Server 2008 R2
>
> I have data as seen in the [Input] table. The output needs to be data
> as seen in the [Output] table.

And how did the data end up in this format?

That format may be good for a spreadsheet, where rows and columns are
more or less interchangable.

Not so in a relational database. Each column is supposed to model a unique
attribute of the entity whereas row is a tuple of data describing one
instance of the entity.

Thus, there are functions to compute sums across rows, but if you want
to compute sum across columns, you will have to type it yourself. That
is, the expression for for December will be:

A.Jan + ... + A.Jul + A.Aug + A.Sep + A.Oct + A.Nov + A.Dec +
AT.Jan + ... + AT.Jul + AT.Aug + AT.Sep + AT.Oct + AT.Nov + AT.Dec

Where A is an alias for the Actual rows and AT for the Approved Totals rows.

That is not very fun. In practice, it may be preferrable to unpivot the data
to rows to make the computations and then pivot back to the unrelational
format.

Or even better is to keep the data in relational format all the way through.

Exactly how that format should be I don't know, since I don't know the the
business rules. It could be this:

CREATE TABLE data AS (ResourceType char(1) NOT NULL,
Month char(6) NOT NULL,
Actuals_high int NOT NULL DEFAULT 0,
Actuals_low int NOT NULL DEFAULT 0,
Actuals_med int NOT NULL DEFAULT 0,
Approved_total_high int NOT NULL DEFAULT 0,
Approved_total_low int NOT NULL DEFAULT 0,
Approved_total_med int NOT NULL DEFAULT 0,
PRIMARY KEY (ResourceType, Month)
)

But if there could appear more costs and categories, maybe it should be:

CREATE TABLE data AS (ResourceType char(1) NOT NULL,
Month char(6) NOT NULL,
Category_id int NOT NULL REFERENCES Categories,
Cost char(4) NOT NULL
CHECK Cost IN ('High', 'Low', 'Med'),
Value int NOT NULL,
PRIMARY KEY (ResourceType, Month, Category_id, Cost)
)



--
Erland Sommarskog, Stockholm, esq...@sommarskog.se