Groups > microsoft.public.sqlserver.programming > #31299

Need SQL script for: Rolling Total By Year and Month

X-Received	by 10.107.144.86 with SMTP id s83mr27202126iod.26.1468845877200; Mon, 18 Jul 2016 05:44:37 -0700 (PDT)
X-Received	by 10.157.5.98 with SMTP id 89mr280750otw.9.1468845877157; Mon, 18 Jul 2016 05:44:37 -0700 (PDT)
Path	csiph.com!usenet.blueworldhosting.com!feeder01.blueworldhosting.com!news.glorb.com!f6no2003836ith.0!news-out.google.com!d68ni4690ith.0!nntp.google.com!f6no2003832ith.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail
Newsgroups	microsoft.public.sqlserver.programming
Date	Mon, 18 Jul 2016 05:44:36 -0700 (PDT)
Complaints-To	groups-abuse@google.com
Injection-Info	glegroupsg2000goo.googlegroups.com; posting-host=192.193.216.148; posting-account=wjh-wgoAAAAoekqXDuka9RpB7dyszo8b
NNTP-Posting-Host	192.193.216.148
User-Agent	G2/1.0
MIME-Version	1.0
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
Injection-Date	Mon, 18 Jul 2016 12:44:37 +0000
Content-Type	text/plain; charset=UTF-8
Content-Transfer-Encoding	quoted-printable
Xref	csiph.com microsoft.public.sqlserver.programming:31299

Show key headers only | View raw

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 | Unroll thread

Thread

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