How to get all continuous months between two dates

[ndorciak]

Hello! 

I am working on loading a data set looking at patient eligibility for a certain insurance. For this data set, I have a patient ID, start date of eligibility, and stop date of eligibility. See example of data below:

I would like to create a "Yes/No" field in the load script for each patient ID on whether or not they are eligible for the insurance for each month. For example, all the months between 3/1/2022 and 12/31/2022 would say "Yes" for eligibility for patient ID 4, but all the months between 12/31/2022 and 4/1/2023 would say "No" for eligibility for patient ID 4. We thought about using a "For Each" loop, but it seems inefficient given the size of our data set. What would be the best way to achieve this result?

[Sayed_Mannan]

Hi, You can achieve this by creating a master calendar and then joining it with your data. Here's how you can do it:

// Load your data
LOAD
[Patient ID],
[Start Date],
[Stop Date]
FROM [YourDataSource];

// Create a master calendar
TempCalendar:
LOAD
date(mindate + IterNo()) as TempDate
AUTOGENERATE 1 WHILE mindate + IterNo() <= maxdate;

LET varMinDate = Num(Peek('mindate', 0, 'TempCalendar'));
LET varMaxDate = Num(Peek('maxdate', -1, 'TempCalendar'));

DROP Table TempCalendar;

MasterCalendar:
LOAD
$(varMinDate) + IterNo() - 1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate $(varMaxDate) - $(varMinDate) + 1;

// Join the master calendar with your data
LEFT JOIN (MasterCalendar)
LOAD
[Patient ID],
[Start Date] as TempDate,
'Yes' as Eligibility
RESIDENT YourData;

LEFT JOIN (MasterCalendar)
LOAD
[Patient ID],
[Stop Date] as TempDate,
'No' as Eligibility
RESIDENT YourData;

This script will create a new field Eligibility in your data that indicates whether a patient is eligible for each date. Please replace [YourDataSource] with your actual data source.

Let me know if this helps! If it's still not working, could you please provide more details about the error or issue you're encountering? This will help me assist you better.

[ndorciak]

Hi, thank you for the response! I get the error 'mindate not found' when loading this script.

[Sayed_Mannan]

I forgot to find mindate and maxdate, here is the updated script,

// Load your data
YourData:
LOAD
[Patient ID],
[Start Date],
[Stop Date]
FROM [YourDataSource];

// Calculate min and max dates
TempCalendar:
LOAD
min([Start Date]) as minDate,
max([Stop Date]) as maxDate
RESIDENT YourData;

LET varMinDate = Num(Peek('minDate', 0, 'TempCalendar'));
LET varMaxDate = Num(Peek('maxDate', 0, 'TempCalendar'));

DROP Table TempCalendar;

// Create a master calendar
MasterCalendar:
LOAD
$(varMinDate) + IterNo() - 1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate $(varMaxDate) - $(varMinDate) + 1;

// Join the master calendar with your data
LEFT JOIN (MasterCalendar)
LOAD
[Patient ID],
[Start Date] as TempDate,
'Yes' as Eligibility
RESIDENT YourData;

LEFT JOIN (MasterCalendar)
LOAD
[Patient ID],
[Stop Date] as TempDate,
'No' as Eligibility
RESIDENT YourData;