August 5, 2008 at 8:17 am
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
August 5, 2008 at 8:41 am
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply