Poll
Question:
Join two queries
Option 1: Join two queries
votes: 1
Option 2: Date calculations
votes: 0
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.
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