Finding the total number of "MTD_Days_Worked" based on "Capture_Date" in SQL Server

user_12240484 0
Viewed 12

I would like to ask my question clearly based on the below table:

Required table, Employee_Working_Table:

Employee_Working_Table

Table description: The table shows the monthly working data for employees (intentionally showed data for employee "Alpha" for simplicity). The Capture_date shows only the business days excluding weekends and holidays. For each Capture_date, if the planned_time_off = 1 then Days_worked = 0 and viceversa.

Problem to be solved: I intend to generate the MTD_Days_Worked as shown in the last column. i.e. the MTD_Days_worked should reset after every month

Code: I have tried the below code:

Select Capture_date, Month_end_date, Employee, Planned_time_off, Days_worked
       ,sum(Days_worked) over(PARTITION BY Employee order by Capture_DATE,MONTH_END_DATE) MTD_days_worked

from Employee_Working_table where Employee = 'Alpha'

I get the below results:

Current_results

As you can see as highlighted in red, the MTD_Days_Worked doesn't get reset after month end is passed.

I would appreciate if someone can guide me on how to achieve this.

Thanks in advance

partitioning sql-server
1 Answers

Include the MONTH_END_DATE in the partitioning criteria of your windowing function rather than in the ordering criteria.

SUM(Days_worked) OVER (PARTITION BY Employee, MONTH_END_DATE ORDER BY Capture_DATE) AS MTD_days_worked

That will cause the ordering to reset at each break in the Employee/MONTH_END_DATE combination.

user_57905840
Related