Help With Date Analysis Required Please...

[samvile18]

Afternoon all,

I have a question that I'm hopeful someone in here can help with. I have the following date fields that I need to assign value to dependant on how many times that date fell into each month. 

For example:
Start Date - 27/02/2025

End Date - 02/04/2025

So I'd need to split the value so it shows 2 days worth in February, 31 days in March and then 2 days in April. 

Is this actually possible? I can't seem to think of a way to do it.

Any help is greatly appreciated. 

[Lauri · Accepted solution]

One way to do this is to iterate over the months and generate rows of data in the load script. This first creates two sample rows of data and then creates rows for all of the months, and then calculates the number of days in each month. Thanks to Rob Wunderlich for inspiration.

mytable:

Load Id, 

start,

    end,

    moName,

    ceil(

    if(moName=MonthName(start), MonthEnd(start)-start+1,

    if(moName=MonthName(end), Day(end),

    Day(MonthEnd(moName))))) as Days

;    

Load Id,

start, 

    end,

    MonthName(AddMonths(start, IterNo()-1)) as moName

    While MonthName(AddMonths(start, IterNo()-1)) <= MonthName(end)

;

Load * Inline [

start, end, Id

2/27/2025, 4/2/2025, 1

11/15/2024, 5/2/2025, 2

];

[samvile18]

Amazing, I was thinking along similar lines but wouldn't have gotten there without your help. Thanks very much.

[samvile18]

Hi Lauri - can I reopen this case at all? I've ran into some discrepancies with the code and I can't seem to get it to work. Is there any chance you could have a quick look and let me know what you think the possible cause is?

You can see on the screen snip that any case falling within the same month - doesn't calculate the days difference correctly. It uses the end of month and takes the start date off, instead of taking the end date and take the start off. I just can't seem to get it to work.

Can you help at all please? @Lauri 

[samvile18]

@Lauri this is the script I'm using btw...

mytable:

Load 

Id, 

start,

    end,

    moName,

    ceil(

    if(moName=MonthName(start), MonthEnd(start)-start+1,

    if(moName=MonthName(end), Day(end),

    Day(MonthEnd(moName))))) as Days;    

Load 

Id,

start, 

    end,

    MonthName(AddMonths(start, IterNo()-1)) as moName

While MonthName(AddMonths(start, IterNo()-1)) <= MonthName(end);

Load * Inline [

start, end, Id

27/02/2025, 02/04/2025, 1

15/11/2024, 02/05/2025, 2

    02/05/2022, 29/05/2022, 3

];

Exit Script;

[Lauri]

Aha, I didn't think about the case with only one row of data. Here's a way to handle it: Use the Window function to calculate the number of months for each Id, and if it's 1, simply subtract [start] from [end]. (I also load [TotalMonths] to see that calculation, but you don't need it.)

mytable:
Load
Id,
start,
end,
moName,
MonthEnd(start)-start+1 as result1,
Day(end) as result2,
Day(MonthEnd(moName)) as result3,
ceil(
if(window(count(moName),Id)=1, end-start,
if(moName=MonthName(start), MonthEnd(start)-start+1,
if(moName=MonthName(end), Day(end),
Day(MonthEnd(moName)))))) as Days,
window(count(moName),Id) as TotalMonths;
Load
Id,
start,
end,
MonthName(AddMonths(start, IterNo()-1)) as moName
While MonthName(AddMonths(start, IterNo()-1)) <= MonthName(end);
Load * Inline [
start, end, Id
02/05/2022, 29/05/2022, 1
27/02/2025, 02/04/2025, 3
15/11/2024, 02/05/2025, 2
];

[ali_hijazi]

You can simply use interval match by matching dates of a calendar to the range of dates you have
here is an example:

Test:
Load customer,num(DayStart(from)) as from, num(DayStart(to)) as to;
Load customer, MakeDate(right(from,4),TextBetween(from,'/','/'),left(from,2)) as from
, MakeDate(right(to,4),TextBetween(to,'/','/'),left(to,2)) as to
;
Load * inline [
customer, from, to
A,27/02/2025,02/04/2025
];

let StartDate = num(DayStart(YearStart(Today())));
let EndDate = num(daystart(YearEnd(Today())));

//Create a temporary calendar
      TempCalendar:
      load recno() as Date_Key,
      '$(StartDate)'+recno()-1 as PeriodDate
      autogenerate(EndDate-StartDate+1);
      
 inner join intervalMatch(PeriodDate)
load from, to resident Test;

inner join(Test)
Load from,to, date(PeriodDate) as PeriodDate
Resident TempCalendar;
drop table TempCalendar;

[seanbruton]

Yes, this is a good simple solution.