How to Group report output by results age group using Business Intelligent Project

• kagisobk

  SSC-Addicted

  Points: 413

  More actions

  August 5, 2008 at 8:17 am

  #190451

  How to Group report output by results age group

  my output supposed to be like this format

  ---------------------------------------------------------------

  30days60days90daysTotal

  ---------------------------------------------------------------

  600.00300.005.00 905.00

  eg

  I am trying to use AdventureWorks Database and Table Name is Product

  Kind Regards

  brian@ejm.co.za

• Carl Federl

  One Orange Chip

  Points: 25384

  More actions

  August 5, 2008 at 8:41 am

  #853221

  Three questions need to be answered before an exact solution can be determined:

  1. What datetime column is being used to determine the age? The example uses SellStartDate.

  2. What is the base datetime used to determine the age? The example uses current_timestamp.

  3. What numeric column is being summarized? The example uses ListPrice.

  Just substitute the example columns to whatever columns you actually need.

  selectSUM(CASE WHEN ProductAge between 0 and 30 then ListPrice else 0 end) as [30days]

  ,SUM(CASE WHEN ProductAge between 31 and 60 then ListPrice else 0 end) as [60days]

  ,SUM(CASE WHEN ProductAge between 61 and 90 then ListPrice else 0 end) as [90days]

  ,SUM(ListPrice) as [Total]

  from(select datediff(dd,SellStartDate,current_timestamp) as ProductAge

  ,ListPrice

  FROMProduction.Product

  ) As ProductCat

  SQL = Scarcely Qualifies as a Language