SQL. Split data over month based on expected hours

Question

I really hope you can help me with this problem, which seems pretty complicated for me.

Dealid:   DealprojectStartDate:   expectedhours:
3534          2021-01-01                200

What I want is to split the weightamount out on different month in the future based on the expected number of hours.

I have following distribution key for expected hours:

0-500 = 2 month
500-1500 = 4 month
1500 - 4000 = 6 month
4000 - above = 8 month. 

So forexample: in the above observation the start date is 01/01 and expected hours of 100 therefore weightamount should be split over 2 month -> Month 1 = 100 and Month 2 = 100.

Important note: If it is the first of the month then it should be allocated to that month. So for the above exapmle because it is the first of the monst (01/05) then it should be allocated to month 5 and 6, but if the start date wat 07/05 then i should have been allocated to month 6 and 7.

What i think would work if to get a new tabel that would split above observation into this:

Dealid:    allocation date:           expectedhours:
3534          2021-01-01(jan)                100
3534          2021-02-01(feb)                100

Hope you guy can help. Thanks

Answer 1

Consider the following table:

min   max        count month_number  
0     500        2     1
0     500        2     2
500   1500       4     1
500   1500       4     2
500   1500       4     3
500   1500       4     4
1500  4000       6     1
1500  4000       6     2
1500  4000       6     3
1500  4000       6     4
1500  4000       6     5
1500  4000       6     6
4000  1000000000 8     1
4000  1000000000 8     2
4000  1000000000 8     3
4000  1000000000 8     4
4000  1000000000 8     5
4000  1000000000 8     6
4000  1000000000 8     7
4000  1000000000 8     8

If we call that table calc_lookup and your table atable then this query will give you the results you want

 SELECT a.Dealid, 
        dateadd(month, dealprojectstartdate, l.month_number-1) as alocation_date,
         a.expectedhours / l.count as expectedhours
 FROM atable a
 JOIN calc_lookup l on a.expectedhours between l.min and l.max

You don't give detail of the edge cases and such -- so there may be some changes needed (off by one, rounding, etc.)