Distribute amount monthly with respect to start date and end date

Question

I have table as

Project_ID
Start_Date
End_Date
BUDGET_Amount

For example:

I Need to Return with SQL 12 Row Each Row Represent the Month-Year between the Two Date and the Value of Budget = 1200 / No of months between two dates "12" = 100$

So The Result to be like this Proj_ID , START_DATE , END_DATE , AMOUNT

"1","1-JAN-2017","31-JAN-2017",100$
"1","1-FEB-2017","27-FEB-2017",100$m
"1","1-MAR-2017","31-MAR-2017",100$
"1","1-APR-2017","31-APR-2017",100$
"1","1-MAY-2017","31-MAY-2017",100$
"1","1-JUN-2017","31-JUN-2017",100$
"1","1-JUL-2017","31-JUL-2017",100$
"1","1-AUG-2017","31-AUG-2017",100$
"1","1-SEP-2017","31-SEP-2017",100$
"1","1-OCT-2017","31-OCT-2017",100$
"1","1-NOV-2017","31-NOV-2017",100$
"1","1-DEC-2017","31-DEC-2017",100$

I removed the incompatible database tags. Please tag with the database you are really using . . . Oracle or SQL Server? — Gordon Linoff

M_Idrees M_Idrees · Accepted Answer · 2017-08-12T12:19:12

Using Common Table Expression(CTE) you can generate dates in given range. This will generate the output you need:

;with mycte as
     (
    select cast('1 jan 2017' as datetime) as DateValue
    union all
    select DATEADD(MONTH,1, DateValue)
    from    mycte  
    where   DATEADD(MONTH,1, DateValue) <= '31 dec 2017'
    )

   select 
   1 Proj_ID,
   REPLACE(CONVERT(VARCHAR(11), DateValue, 106), ' ', '-')  START_DATE ,
   REPLACE(CONVERT(VARCHAR(11), DATEADD(DAY, -1, DATEADD(MONTH,1, DateValue))), ' ', '-')   END_DATE  ,
   '100$' AMOUNT
   from    mycte

This is will display months' first and last days.

Distribute amount monthly with respect to start date and end date

1 Answers