January 8, 2010 at 3:10 am
I have a query that currently generates a single line financial summary. I'm attempting to amend the query to output multiple records Group on a customer by customer basis (obs_customer) but I'm having no luck (please see below).
SELECT lastmonthminus1,lastmonth,currentmonth,lastyear,lastyearytd,ytd
FROM(SELECT 'Order' AS [type],
SUM(CASE monthsrelative WHEN -2 THEN obs_neworders + obs_newcredit
ELSE 0 END) AS lastmonthminus1,
SUM(CASE monthsrelative WHEN -1 THEN obs_neworders + obs_newcredit
ELSE 0 END) AS lastmonth,
SUM(CASE monthsrelative WHEN 0 THEN obs_neworders + obs_newcredit
ELSE 0 END) AS currentmonth,
SUM(CASE yearsrelative WHEN -1 THEN obs_neworders + obs_newcredit
ELSE 0 END) AS lastyear,
SUM(lastyearytd) AS lastyearytd,
SUM(CASE yearsrelative WHEN 0 THEN obs_neworders + obs_newcredit
ELSE 0 END) AS ytd
FROM(SELECT (Datepart("mm",obs_startdate)+((Datepart("yyyy",obs_startdate)-1)*12))-
(Datepart("mm",Getdate()) + ((Datepart("yyyy",Getdate())-1)*12)) AS monthsrelative,
Datepart("yyyy",Dateadd("mm",2,obs_startdate)) - Datepart("yyyy",Dateadd("mm",2,Getdate())) AS yearsrelative,
CASEWHEN (Datepart("yyyy",Dateadd("mm",2,obs_startdate)) - Datepart("yyyy",Dateadd("mm",2,Getdate()))) = -1
AND Datepart("mm",Dateadd("mm",2,obs_startdate)) <= Datepart("mm",Dateadd("mm",2,Getdate()))
AND Datepart("dd",Dateadd("mm",2,obs_startdate)) <= Datepart("dd",Dateadd("mm",2,Getdate()))
THEN obs_neworders + obs_newcredit ELSE 0 END AS lastyearytd,obs_startdate,obs_customer,obs_neworders,
obs_sloc,obs_newcredit
FROMcomp.obstats
WHEREobs_sloc = 'UK') AS monthdata) AS orders
January 8, 2010 at 3:18 am
Please post some sample data with create and insert statements.. Please look at my first link on the signature.
January 8, 2010 at 5:37 am
Test data as suggested by SS but a 10000 feet view from my side is that you need an 'UNPIVOT'. Read this,
http://technet.microsoft.com/en-us/library/ms177410(SQL.90).aspx
---------------------------------------------------------------------------------
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply