date calculations

[sunny1234]

In table xx code status need a filter on code status and  its count for the last month, last two months, last three months etc...

data

Codestatus    cal_dt                        
c2               2007-08-08
c3               2004-06-02
c4               2003-02-01
c5

Report output

codestatuscount     last month          last two months

45                         23                     10

Please recommend how to join these two queries.

count(codestatus) where code='c1'
and
add_months(caldt, -1)

Where clause is not available in Cognos is there any alternative to accomplish this please suggest.

[chow]

If you want to show last 12 months data in your report .it is easy to show in crosetab report..

try to create 1 data iteam and put this exprestion in that
trim(to_char([Date],'MONTH')) || ', ' || to_char([ Date],'YYYY')
in that trim is for deleting time if u r data iteam inclueds time too use trim other wise not need. ofther that create 1 filter like this
[Date] >= _first_of_month(_add_months({sysdate},-12))and [ Date] <={sysdate}

it will gives form today's date to last 12 months data..

it will help .try to play with this exprestion..

cheers