Home » SQL & PL/SQL » SQL & PL/SQL » cross tab query for last 12 months data
cross tab query for last 12 months data [message #252732] Thu, 19 July 2007 16:23 Go to next message
mohapatra
Messages: 24
Registered: August 2006
Location: New Jersey
Junior Member
I have a query like this..

SELECT team ,
,MAX(DECODE( across,1,runs,0)) May_2006
,MAX(DECODE( across,2,runs,0)) Jun_2006,
,MAX(DECODE( across,2,runs,0)) July_2006
,MAX(DECODE( across,4,runs,0)) Aug_2006
,MAX(DECODE( across,5,runs,0)) Sep_2006
,MAX(DECODE( across,6,runs,0)) Oct_2006
,MAX(DECODE( across,7,runs,0)) Nov_2006
,MAX(DECODE( across,8,runs,0)) Dec_2006
,MAX(DECODE( across,9,runs,0)) Jan_2007
,MAX(DECODE( across,10,runs,0)) Feb_2007
,MAX(DECODE( across,11,runs,0)) Mar_2007
,MAX(DECODE( across,12,runs,0)) Apr_2007

FROM (
SELECT team,
DECODE (TO_CHAR (MONTH, 'MM/dd/YYYY'),
'05/01/2006', 1,
'06/01/2006', 2,
'07/01/2006', 3,
'08/01/2006', 4,
'09/01/2006', 5,
'10/01/2006', 6,
'11/01/2006', 7,
'12/01/2006', 8,
'01/01/2007', 9,
'02/01/2007', 10,
'03/01/2007', 11,
'04/01/2007', 12
) across,
runs

FROM (SELECT fd.team team,
TRUNC (fsdr.match_date, 'MM') MONTH,
ROUND (SUM (fsdr.runs)) runs
FROM Teams fd,
score_details fsdr
WHERE 1 = 1
AND fd.team = fsdr.team(+)
AND fsdr.match_date(+) >= '01-MAY-2006'
AND fsdr.match_date(+) < '01-May-2007'
GROUP BY fd.team,
TRUNC (fsdr.match_date, 'MM'))
GROUP BY team,
MONTH,
runs
)
GROUP BY team ;

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

The report dates are hardcoded - '01-MAY-2006' and '01-MAY-2007'
.But How to remove thehardcoding and report for 1 year data.
Suppose today is July 19 , and i m running the report , it should give the report from July 1st 2006 to July 1 2007.
Re: cross tab query for last 12 months data [message #252770 is a reply to message #252732] Thu, 19 July 2007 23:05 Go to previous message
SnippetyJoe
Messages: 63
Registered: March 2007
Location: Toronto, Canada
Member
...
SELECT
  team,
  months_between( MONTH, trunc( sysdate, 'MM' ) ) + 1 as across,
  runs
FROM (SELECT fd.team team,
TRUNC (fsdr.match_date, 'MM') MONTH,
ROUND (SUM (fsdr.runs)) runs
FROM Teams fd,
score_details fsdr
WHERE 1 = 1
AND fd.team = fsdr.team(+)
AND fsdr.match_date(+) >= trunc( sysdate, 'MM' )
AND fsdr.match_date(+) <  add_months( trunc( sysdate, 'MM' ), 12 )
GROUP BY fd.team,
TRUNC (fsdr.match_date, 'MM'))
GROUP BY team,
MONTH,
runs
)
GROUP BY team ;


--
Joe Fuda
SQL Snippets
Previous Topic: PL/SQL error
Next Topic: Querying XML
Goto Forum:
  


Current Time: Wed Dec 07 14:49:33 CST 2016

Total time taken to generate the page: 0.07996 seconds