Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

? SQL for multiple series bar chart comparing current verse last year Expand / Collapse
Author
Message
Posted Monday, October 21, 2013 1:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 02, 2013 6:03 AM
Points: 1, Visits: 52
Trying to expand on a report that reports the last N months of electric usage for an account.

So far I have this:

CREATE TABLE [dbo].[TestTable](
[id] [int] IDENTITY(1,1) NOT NULL,
[Acct] [int] NULL,
[Month] [int] NULL,
[Year] [int] NULL,
[Hours] [int] NULL
) ON [PRIMARY]

SELECT *
FROM
(SELECT acct, sum(hours) AS TotalHours, year, month,
row_number()
OVER (ORDER BY Year DESC, month DESC) AS rn
FROM TestTable
WHERE Acct = 100
GROUP BY acct, year, month) AS rst1
WHERE rn <= 4

Sample Data returned going backwards from last billing:

Acct	Hours	Year	Mth	rn
100 4 2013 2 1
100 20 2013 1 2
100 3 2012 12 3
100 33 2012 11 4



I can then create a bar chart in SSRS based on the TotalHours; Hours for the Y Axis and YearMonth for the X Axis.
I get the electric usage for each month in a bar.

Now I'm trying to duplicate what is on the electric bill. Once multiple years of data are added, how would I get another series matching on the previous year/month?

So for reporting month 2, I would have two bars side by side, one for 2014-2, and another one for 2013-2.

Ideas?

Post #1506832
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse