Query in Oracle

[frasernm]

Hi,

I have a table containing three columns, a name, event and date. I'm trying to produce a report which details the number of events per person per week for a number of weeks so I can spot trends - ideally a report showing the person name followed by four columns showing the number of events for that person for each of the last four weeks. I can do one weekly column, but how do I get the four columns in one query?

Thanks,

Fraser

 

[carp]

See if this will work for you:

SELECT person,
SUM(DECODE(to_char(sysdate,'WW') - to_char(the_date,'WW'),4,1,0)) four_weeks_ago,
SUM(DECODE(to_char(sysdate,'WW') - to_char(the_date,'WW'),3,1,0)) three_weeks_ago,
SUM(DECODE(to_char(sysdate,'WW') - to_char(the_date,'WW'),2,1,0)) two_weeks_ago,
SUM(DECODE(to_char(sysdate,'WW') - to_char(the_date,'WW'),1,1,0)) one_week_ago
FROM frasernm
GROUP BY person;

The results will look something like:

PERSON FOUR_WEEKS_AGO THREE_WEEKS_AGO TWO_WEEKS_AGO ONE_WEEK_AGO
-------------------- -------------- --------------- ------------- ------------
joe 7 7 7 7
sam 0 6 7 3

 

[carp]

It occurred to me that the above approach has a bug in it. It won't work in January, since Decembers weeks will number in the 50s and January's will be 1,2,3,4 and 5. To accomodate this, you will need to expand the decode to handle values like -49, -50, etc. But the basic approach is the same.