Calculation based on dates from a range falling within a month

[Zak2]

Hi,

My sickness absence data has rows of absence episodes of varying length, and which can spread across more than one month. I would like to be able to make a calculation based on absence days that fall within a selected month (month/year).

In the below example, the 3 first columns represent an extract from the absence data. How could I calculate the FTE * the number of days that fall in June 2024, as per the final 2 columns? For each row, I need to calculate how many days in each row are in June 2024.

Many thanks

[Kushal_Chawda]

@Zak2  try below

Data:
Load *, 
     Floor([Absence Start Date]) &'@'& Floor([Absence End Date]) as Key 
Inline [
Absence Start Date, Absence End Date, FTE
07/05/2024,02/08/2024,0.5
03/06/2024,24/06/2024,1
28/05/2024,08/06/2024,0.75
15/04/2024,20/05/2024,0.5
11/07/2024, 25/08/2024,1
];

Calendar:
Load *,
     monthname(Date) as Month;
Load *,
     Date(SubField(Key,'@',1)+IterNo()-1) as Date
while SubField(Key,'@',1)+IterNo()-1 <= SubField(Key,'@',2);
Load FieldValue('Key',RecNo()) as Key
AutoGenerate FieldValueCount('Key');

[QFabian]

@Zak2 , here another simple option to undestand the logic

Data_Aux:

Load * INLINE [

Start, End, FTE

07/05/2024, 02/08/2024, 0.5

03/06/2024, 24/06/2024, 1

28/05/2024, 08/06/2024, 0.75

];

Data:

Load

*,

Required * FTE as Result;

Load

*,

    if(month(End) = 6 and month(Start) = 6,

    End - Start,

    if(month(End) = 6 and month(Start) <> 6,

    End - '31/05/2024',

if(month(End) <> 6 and month(Start) = 6,        

    '01/06/2024' - Start,

if(month(End) <> 6 and month(Start) <> 6,        

    '31/06/2024' - '01/06/2024'

    )))) as Required

Resident Data_Aux;

drop table Data_Aux;

[madelonjansen]

Do you need only june, or do you need this every month?

For a more flexible solution you could do this:

AbsencePerMonth;
Load
   Sum(FTE) as Result,
   StartDate,
   EndDate,
   AbsenceMonth,
   EmployeeID
Group By  StartDate, EndDate, AbsenceMonth, EmployeeID;
Load
   StartDate,
   EndDate,
   MonthName(StartDate + iterno()-1) as AbsenceMonth,
   FTE,
   EmployeeID
Resident absence
While Date(StartDate + iterno()-1) <= EndDate;

Drop Table absence;