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

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

Re: Need SQL script for: Rolling Total By Year and Month

Show key headers only | View raw

 (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

Back to microsoft.public.sqlserver.programming | Previous | Next — Previous in thread | Find similar

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