ORACLE Question – Split data in 90 days chunk

0 pts.
Tags:
Oracle
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
ASKED: October 27, 2005  12:34 PM
UPDATED: October 28, 2005  10:04 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Structsound
    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
    0 pointsBadges:
    report
  • MrOracle
    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))
    0 pointsBadges:
    report
  • Eaiexpert
    [...] Address: http://itknowledgeexchange.techtarget.com/itanswers/oracle-question-split-data-in-90-days-chunk/ (0) Comments Read [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following