b***@gmail.com
2016-07-18 12:44:36 UTC
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]
-----------------------------------------------------------------------------------------------
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]
-----------------------------------------------------------------------------------------------