Rolling Data - Previous 3 months, Current, future one month

• SteveEClarke

  SSCertifiable

  Points: 5161

  More actions

  August 15, 2014 at 1:40 am

  #294547

  I am trying to create a rolling data analysis of sales information for a given period, rolling 3 months, comparing to last years data.

  I have data in one table for Sales information;

  TrnYear

  TrnMonth

  SalesValue

  I need to display Current Month, Previous 1, Previous 2, Future Period

  So ;

  Result should look like ;

  TrnYear,TrnMonth, SalesValue

  2013, 06

  2013, 07,

  2013, 08,

  2013, 09,

  2014, 06,

  2014, 07,

  2014, 08,

  2014, 09

  Any thoughts would be appreciated.

  Thanks

  ________________________________________________________________________________________________
  Regards
  Steve
  SQL 2008 DBA/DBD - MCTS/MCITP

  Please don't trust me, test the solutions I give you before using them.

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  August 15, 2014 at 2:04 am

  #1738297

  SteveEClarke (8/15/2014)

  ---

  I am trying to create a rolling data analysis of sales information for a given period, rolling 3 months, comparing to last years data.

  I have data in one table for Sales information;

  TrnYear

  TrnMonth

  SalesValue

  I need to display Current Month, Previous 1, Previous 2, Future Period

  So ;

  Result should look like ;

  TrnYear,TrnMonth, SalesValue

  2013, 06

  2013, 07,

  2013, 08,

  2013, 09,

  2014, 06,

  2014, 07,

  2014, 08,

  2014, 09

  Any thoughts would be appreciated.

  Thanks

  what are the datatypes for TrnYear,TrnMonth ?

  do you have months with no data?

  assume 2014 09 will return blank...cos we are in 2014 08 at the moment?

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• SteveEClarke

  SSCertifiable

  Points: 5161

  More actions

  August 15, 2014 at 2:15 am

  #1738300

  Both TrnYear, TrnMonth are decimal.

  2014/09 will return NULL, I agree..... although I think finance want to put the forecast in there.... another topic !

  Thanks

  ________________________________________________________________________________________________
  Regards
  Steve
  SQL 2008 DBA/DBD - MCTS/MCITP

  Please don't trust me, test the solutions I give you before using them.

• SteveEClarke

  SSCertifiable

  Points: 5161

  More actions

  August 15, 2014 at 2:26 am

  #1738307

  Think I have solved it ...using Row_NUMBER()

  SELECT

  ROW_NUMBER() OVER ( ORDER BY TrnYear DESC, TrnMonth DESC ) as Row,TrnYear, right('00'+ rtrim(TrnMonth), 2) as TrnMonth, Count( Invoice ) as InvoiceCount

  FROM ApInvoicePay

  Where TrnYear >= Year( GetDate()) -2

  Group by TrnYear, TrnMonth

  This then can be used to select rows 1,2,3 and 12,13,14,15 - ..... Can I do that in one View ? using Having ?

  ________________________________________________________________________________________________
  Regards
  Steve
  SQL 2008 DBA/DBD - MCTS/MCITP

  Please don't trust me, test the solutions I give you before using them.

• SteveEClarke

  SSCertifiable

  Points: 5161

  More actions

  August 15, 2014 at 2:29 am

  #1738309

  cracked it ....

  With myCTE as

  (

  SELECT

  ROW_NUMBER() OVER ( ORDER BY TrnYear DESC, TrnMonth DESC ) as Row, TrnYear, right('00'+ rtrim(TrnMonth), 2) as TrnMonth, Count( Invoice ) as InvoiceCount

  FROM ApInvoicePay

  Where TrnYear >= Year( GetDate()) -2

  Group by TrnYear, TrnMonth

  )

  select

  Row,

  TrnYear,

  TrnMonth,

  InvoiceCount

  from myCTE

  where Row in ( 1,2,3,12,13,14,15 )

  ________________________________________________________________________________________________
  Regards
  Steve
  SQL 2008 DBA/DBD - MCTS/MCITP

  Please don't trust me, test the solutions I give you before using them.