Need Help with Date Buckets - Logic

[milinds]

Hello Friends,
I want to know a best way to generate a report on a product sales by weekly, monthly, quarterly and yearly.

Here is the sample data:

Date Product Count
01/01/2005 apple 50
01/01/2005 apple 50
01/03/2005 apple 100
01/05/2005 apple 80
01/10/2005 apple 100


I want the results in this format :

Product WeeklySale Monthly Quarterly Yearly
Apple 50 100 200 1000

Can you please help me with the logic or any oracle function I could use ?

[LKBrwn_DBA]

And you want someone to do this homework assignment for you?

If you really want to learn something, the best way is to try doing it yourself.


Hint: Take a look at these functions: TO_CHAR(), CASE, DECODE(), COUNT() and SUM().
Also check out GROUP BY...

[milinds]

Well, that was not really a homework assignment ..but i was able to do it myself .. just needed some idea on logic .. not plsql really ..

I did it myself with select-case

Select /*+ PARALLEL(DomainCountWeekly,8) */ product,
SUM (CASE WHEN Week_Starting_Day BETWEEN sysdate-6 AND sysdateTHEN 1 ELSE 0 END) AS "0-6d",
SUM (CASE WHEN Week_Starting_Day BETWEEN sysdate-7 AND sysdate THEN 1 ELSE 0 END) AS "Weekly",
SUM (CASE WHEN Week_Starting_Day BETWEEN sysdate-30 AND sysdate THEN 1 ELSE 0 END) AS "Monthly",
SUM (CASE WHEN Week_Starting_Day BETWEEN sysdate-90 AND sysdate THEN 1 ELSE 0 END) AS "Qtrly",
SUM (CASE WHEN Week_Starting_Day BETWEEN sysdate-365 AND sysdate THEN 1 ELSE 0 END) AS "Yearly"
From Salestable
Where ...
...