? SQL for multiple series bar chart comparing current verse last year

• SQLMan131

  Newbie

  Points: 2

  More actions

  October 21, 2013 at 1:29 pm

  #300301

  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:

  AcctHoursYearMthrn

  1004201321

  10020201312

  10032012123

  100332012114

  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?