Convert to month records 1

[onwagena]

I have a table with the following records:

ID Begindate Enddate Amount
1 1-1-2005 15-9-2005 17
2 16-9-2005 31-12-2005 7

I need a table like this:

ID Month Amount
1 1 2
1 2 2
...
1 9 1
2 9 1
2 10 2
2 11 2
2 12 2

Is there a script possible to convert the records with a begin and enddate to a table with a record for each month?

 

[lewisp]

How do you know what the amout is per month? Or is it just a %age split over the number of months?

 

[onwagena]

The split is %aage over the number of months.

 

[SantaMufasa]

Onwagena,

Must the Amounts be whole numbers or can they have fractional components based upon the number of days that exist in the "month-piece" to which they apply?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Dagon]

Create a table month_year containing:

month_no begindate enddate
1 '01-jan-05' '31-jan-05'
2 '01-feb-05' '28-feb-05'
...

You could do this very easily using a PL/SQL script. Then your SQL would be something like:

select t.id, m.month_no,
(least(t.enddate, m.enddate) - greatest(m.begindate, t.begindate)/(t.enddate - t.begindate)) * amount
from table t, month_year m
where m.begindate between t.begindate and t.enddate
or m.enddate between t.begindate and t.enddate

You can obviously round numbers up if required.

 

[onwagena]

Thanks all of you and special Dagon.

The correct SQL statement is :
select t.id, m.month_no,
(least(t.enddate, m.enddate) - greatest(m.begindate, t.begindate)+1)/(t.enddate - t.begindate +1) * amount
from table t, month_year m
where m.begindate between t.begindate and t.enddate
or m.enddate between t.begindate and t.enddate

This will to the job that create the table that I need.