ORACLE Question – Split data in 90 days chunk
I need to aggregate data in 90 days chunk. For example, I have following data.
Date Custorm Qty
1/5/05 1 7
3/31/05 1 9
5/10/05 1 10
I need to sum(qty) for chunk of 90 days. The chunks should be such that the first chunk starts on 1/5/05, and goes to 4/5/05 (1/5/05 + 90 Days).
My second chunk would start from 5/10/05 and go 90 days from there (something like 8/9/05) and so forth and so on.
How can I accompalish this?
Please let me know, you can either send me an e-mail at dsheth@netzero.com or reply to this message.
DSheth
How can
Software/Hardware used:
Software/Hardware used:
ASKED:
October 27, 2005 12:34 PM
UPDATED:
October 28, 2005 10:04 AM
Answer Wiki:
Hi,
If you need to aggregate it in 90 days lot ,then just create MVs in oracle with your periodicity required, all further process can be continued from the MVs rather than complex scripts to extarct and calculate.
If you are looking more please the full requirement.
Regards
Deva.
To see all answers submitted to the Answer Wiki: View Answer History.
You can use an analytic query to get the sums
select qty, date, sum(qty) over (order by date range between current row and interval ’90′ day following) 90daysum from…..
then you’ll have to work an outer query to only show the dates within the 90 day span–probably using lag and lead. AskTom has some good examples, as well as the Oracle Data Warehousing Guide documentation
You didn’t mention what version on Oracle, but I think you are looking for a CASE statement. I believe it might be a version 9i feature. Here is a sample piece of code:
SQL> r
1 select case
2 when creation_date_time between to_date(’05-JAN-2005′) and to_date(’05-JAN-2005′)+90
3 then 1
4 when creation_date_time between to_date(’10-MAY-2005′) and to_date(’10-MAY-2005′)+90
5 then 2
6 else 3
7 end date_grouping
8 , count(*)
9 from mtg_partner
10 group by case
11 when creation_date_time between to_date(’05-JAN-2005′) and to_date(’05-JAN-2005′)+90
12 then 1
13 when creation_date_time between to_date(’10-MAY-2005′) and to_date(’10-MAY-2005′)+90
14 then 2
15 else 3
16* end
DATE_GROUPING COUNT(*)
————- ———-
1 63
2 57
3 229
3 rows selected.
I hope this helps!
((MrO))