Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]
Groups > microsoft.public.sqlserver.programming > #31299
| Newsgroups | microsoft.public.sqlserver.programming |
|---|---|
| Date | 2016-07-18 05:44 -0700 |
| Message-ID | <1f6326ba-b444-4d13-bc28-8905aa2dbc6b@googlegroups.com> (permalink) |
| Subject | Need SQL script for: Rolling Total By Year and Month |
| From | bingopinath@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] -----------------------------------------------------------------------------------------------
Back to microsoft.public.sqlserver.programming | Previous | Next — Next in thread | Find similar
Need SQL script for: Rolling Total By Year and Month bingopinath@gmail.com - 2016-07-18 05:44 -0700 Re: Need SQL script for: Rolling Total By Year and Month Erland Sommarskog <esquel@sommarskog.se> - 2016-07-18 20:54 +0200
csiph-web