I am trying to write a function that will update a table, 'Orders'. The rows in this table will have Item, Location, Qty, SendDate, and EndDate (SendDate and EndDate are in 'yyyymmdd' format). The difference between SendDate and Enddate will need to be taken and spread the qty out by the difference in the dates. Example: A row with a qty of 200 and the SendDate is 3/16 and the EndDate is 3/20, the function would need to update the current row to a qty of 40 and update the Senddate to 3/16 and create the rows for 3/17-3/20 with a qty of 40.
Sample Data:
- Item Location Qty SendDate EndDate
-Toy Store 200 20150316 20150320
Desired result:
- Item Location Qty SendDate EndDate
-Toy Store 40 20150316 20150320
-Toy Store 40 20150317 20150320
-Toy Store 40 20150318 20150320
-Toy Store 40 20150319 20150320
-Toy Store 40 20150320 20150320
Oracle 11g2
Example:
I assume I need a few steps here... But I think I need a CURSOR to do some type of datediff to populate these dates and changes in qty to a temp table. Any advice? I am a novice when it comes to CURSOR statements. Thanks in advance