Find the exact week count based on date range

I need to calculate the count of total assigned week and total benched week for an employee.

An employee is assigned in different projects in some time duration which will always start from Monday and always end on Friday. Monday to Friday will be considered as 1 week. Any overlapping weeks of assigned status should be considered as 1 week if employee is working in assigned status week project and that project duration falls under benched status week project so that week will be not counted in total bench week count.

Below are the relevant scenario as below.

"AssignedHistory":[ 
   {"Project":1,"status":"Assigned","Startdate":"01/03/2022", "Enddate":"01/07/2022" }, 
   {"Project":2,"status":"Assigned","Startdate":"01/10/2022", "Enddate":"01/14/2022" }, 
   {"Project":3,"status":"Assigned", "Startdate":"01/10/2022", "Enddate":"01/21/2022" }, 
   {"Project":4,"status":"Bench","Startdate":"01/17/2022", "Enddate":"01/21/2022" },
   {"Project":5,"status":"Bench","Startdate":"02/07/2022", "Enddate":"02/11/2022" }    
]

Here I need to find the count of total assigned week and total benched week for an employee, and expected result should be :

Total assigned week:3
Total benched week :1

Since 1 week of assigned status is overlapping for 2 projects from 10th Jan to 14 Jan and also for benched week for project 4 it is overlapping with assigned status of project 3 from 17th Jan to 21st Jan.

This is how I am trying

var assignedweek = AssignedHistory.Where(x => new []{"Assigned"}.Contains(x.status)).ToList();
var benchedweek = AssignedHistory.Where(x => new []{"Bench"}.Contains(x.status)).ToList();
 
int AssignedWeekCount = assignedweek
    .SelectMany(a => {
        DateTime firstSunday = a.Startdate.AddDays(-(int)a.Startdate.DayOfWeek);
        DateTime lastSunday = a.Enddate.AddDays(-(int)a.Enddate.DayOfWeek); 
        int weeks = (lastSunday - firstSunday).Days / 7 + 1;

        // Enumerate one Sunday per week
        return Enumerable.Range(0, weeks).Select(i => firstSunday.AddDays(7 * i));
    })
    .Distinct()
    .Count();
    
    int BenchWeekCount = benchedweek
    .SelectMany(a => {
        DateTime firstSunday = a.Startdate.AddDays(-(int)a.Startdate.DayOfWeek);
        DateTime lastSunday = a.Enddate.AddDays(-(int)a.Enddate.DayOfWeek); 
        int weeks = (lastSunday - firstSunday).Days / 7 + 1;

        // Enumerate one Sunday per week
        return Enumerable.Range(0, weeks).Select(i => firstSunday.AddDays(7 * i));
    })
    .Distinct()
    .Count();

But it is giving incorrect bench week count if there is overlapping bench week with assigned week. I am not able to remove the overlapping week from benched week.

Any suggestions will be highly appreciated.

Solution 1:

Using .Except(AssignedWeeks intermediate result) should do the trick. Try:

var assignedweek = AssignedHistory.Where(x => new []{"Assigned"}.Contains(x.status)).ToList();
var benchedweek = AssignedHistory.Where(x => new []{"Bench"}.Contains(x.status)).ToList();
 
var AssignedWeeks = assignedweek
    .SelectMany(a => {
        DateTime firstSunday = a.Startdate.AddDays(-(int)a.Startdate.DayOfWeek);
        DateTime lastSunday = a.Enddate.AddDays(-(int)a.Enddate.DayOfWeek); 
        int weeks = (lastSunday - firstSunday).Days / 7 + 1;

        // Enumerate one Sunday per week
        return Enumerable.Range(0, weeks).Select(i => firstSunday.AddDays(7 * i));
    })
    .Distinct()
    .ToList();

int AssignedWeekCount = AssignedWeeks.Count();
    
int BenchWeekCount = benchedweek
    .SelectMany(a => {
        DateTime firstSunday = a.Startdate.AddDays(-(int)a.Startdate.DayOfWeek);
        DateTime lastSunday = a.Enddate.AddDays(-(int)a.Enddate.DayOfWeek); 
        int weeks = (lastSunday - firstSunday).Days / 7 + 1;

        // Enumerate one Sunday per week
        return Enumerable.Range(0, weeks).Select(i => firstSunday.AddDays(7 * i));
    })
    .Distinct()
    .Except(AssignedWeeks) // Exclude weeks already counted as assigned
    .Count();