CODEWITHSUNDEEP
sqlsql-server
Mara
Mara

Reputation: 371

Need query for somewhat complicated aggregation

I haven't found a way to solve this (in an elegant way), so I'd like to ask for your help with calculating the average week-hours for specific month.

declare @person table (pers_id int, [from] date, [to] date, hrs decimal(4, 2));
insert into @person values (72, '2017-09-01', '2017-11-13', 20);
insert into @person values (72, '2017-11-14', null, 35);

declare @months table (ym date);
insert into @months values ('2017-09-01');
insert into @months values ('2017-10-01');
insert into @months values ('2017-11-01');
insert into @months values ('2017-12-01');

/* so I need a query whouch would output average week-hours: */
2017-09-01 = 20
2017-10-01 = 20
2017-11-01 = 28.5
  = (13/30)*20 + (17/30)*35 ; (assumed each month has 30 days) 
2017-12-01 = 35

Anybody willing to help me out? (am on Azure SQL)

Upvotes: 0

Views: 49

Answers (1)

podiluska
podiluska

Reputation: 51494

This assumes at most one change in hours per month. If you have more, then you'd need more complex calculations around the proportions

select 
    start,  
    round(sum(

    case 
        when [from]>[start] then (datediff(d,[from],finish)+1) * hrs/days
        when [to]<[finish] then (datediff(d,start,[to])+1) * hrs/days
        else hrs end
    ),2)
from
    (select ym as start, EOMONTH(ym) as finish, 
        30
        --datepart(d, EOMONTH(ym)) 
        as days from @months) months
    inner join (    select pers_id, [from], isnull([to], EOMONTH(getdate()))as[to], hrs from @person) p
        on months.finish>=p.[from] and months.start<=p.[to]
group by start

Upvotes: 1

Related Questions