Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

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

Need SQL script for: Rolling Total By Year and Month

Started bybingopinath@gmail.com
First post2016-07-18 05:44 -0700
Last post2016-07-18 20:54 +0200
Articles 2 — 2 participants

Back to article view | Back to microsoft.public.sqlserver.programming

Contents

  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

#31299 — Need SQL script for: Rolling Total By Year and Month

Frombingopinath@gmail.com
Date2016-07-18 05:44 -0700
SubjectNeed SQL script for: Rolling Total By Year and Month
Message-ID<1f6326ba-b444-4d13-bc28-8905aa2dbc6b@googlegroups.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] 
-----------------------------------------------------------------------------------------------

[toc] | [next] | [standalone]

#31300

FromErland Sommarskog <esquel@sommarskog.se>
Date2016-07-18 20:54 +0200
Message-ID<XnsA649D4B02F3AYazorman@127.0.0.1>
In reply to#31299
 (bingopinath@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, esquel@sommarskog.se

[toc] | [prev] | [standalone]

Back to top | Article view | microsoft.public.sqlserver.programming

csiph-web