Month wise task completed in minutes between two dates in sql-CodePudding

I want to get month-wise minutes spent on any assigned task to an employee.

For example when I select date between 1-Jan-2021 to 1-Jan-2022 then it should give the number of minutes spent by each employee month-wise in minutes. Suppose in a filter I select a fromdate 1-Jan-2021 to date 1-Jan-2022 then if task start on Task_start_date='2021-09-02 10:00:00.000' and end on Task_end_date='2021-12-02 14:00:00.000' then I should get month wise report in minutes spent by employee on this assigned task.

 FILTER Date: 1-Jan-2021 to 1-Jan-2022

 Task Date:
 Task_start_date='2021-09-02 10:00:00.000'
 Task_end_date='2021-12-02 14:00:00.000'

Result 
       Employee  SEP-2021    | OCT-2021 | NOV-2021 | DEC-2021 
        X        39720min    | 43200min | 41160min | 1700min

If any body knows the query please share.

CodePudding user response：

Try the following recursive CTE:

with cte as 
(
    select empid,format(st,'yyyy-MM-dd HH:mm') st,en from MyTable
    union all
    select empid,format(dateadd(month,1,st),'yyyy-MM-01 00:00'),en from cte
    where dateadd(month,1,st)<=en
),
cte2 as 
(
    select empid, format(cast(st as datetime),'yyyy-MM') as Months,
    st, coalesce(lead(st) over (order by empid,st),dateadd(day,1,en)) as endOfMonths
    from cte
 )

select empid, Months, DateDiff(Minute, st, DateAdd(Day,-1,endOfMonths))
from cte2

See a demo from