Query Help needed in Report

[urchin556]

Hi guys ,
I need to make a matrix report like the following

Description Year(2002 Full),Year(2002 Till Date),Year(2002 Till Month),Year(2003 Full),Year(2003 Till Date),Year(2003 Till Month)

Year its easy to get in the query as the following

select item,sum(qty)to_char(v_date,'YYYY') from table group by item v_date
but how will i get the year to date and year till month

item will be my row
qty will be cell
and year will be column

If the current date is 25th oct 2004 so in above query i need as

2002 Full Year i.e jan 2002 to dec 2002
2002 YTD Jan 2002 - 25t oct 2002
2002 Year To Month - jan2002 oct 2002

2003 Full Year i.e jan 2003 to dec 2002
2003 YTD Jan 2003 - 25t oct 2003
2003 Year To Month - jan 2003 oct 2003

Any help will be appreciated

 

[nagornyi]

If you are on Oracle 9, you may take advantage of CASE operator and write your query something like this:

select
item
, sum(entire_year) entire_year
, sum(YTD) YTD
, sum(YTM) YTM
from
(
select
item
,
(
case when v_date between 
              ’01-JAN-‘||to_char(:p_date, ‘YYYY’) 
          and ’31-DEC-‘||to_char(:p_date, ‘YYYY’)
     then qty else 0
end
) entire_year
,
(
case when v_date between 
              ’01-JAN-‘||to_char(:p_date, ‘YYYY’) 
          and :p_date
     then qty else 0
end
) YTD

,
(
case when v_date between 
              ’01-JAN-‘||to_char(:p_date, ‘YYYY’) 
          and ’01-‘||to_char(:p_date, ‘MON-YYYY’)
     then qty else 0
end
) YTM
)

From the report layout prospective, this is not a matrix report, but a tabular one with columns
Item, Entire Year, YTD, YTM, etc.