Generate sequence of dates between 2 dates
i have a requirement to “fill in the gaps in time” per se on data from a system. Here is my use case:
A customer signed up with the company for $100 subscription monthly for 3 years. The company added services 6 months into the contract which bumped their subscription up to $120 / month, and again a couple months later. but then never again for a couple years. however our ERP only shows the effective dates based on the change. so the json from the system would look this this but I need to create records for every missing month using the previous month if no changes were made. Then when the next change made use that and generate the missing time between that month and subsequent months until the last change. this will allow us to show trending over time and the revenue for that customer by every month in which they have been a customer. what is the best way to achieve this? a parent customer can have multiple locations in which this could change for this example i’ve simplified it to just 1 customer with multiple records so i believe some level of grouping and min/max dates need to be used.
[
{
“Parent_Customer_Id”: 12345,
“Location_Site_ID”: 98765,
“Parent_Customer_Name”: “Acme”,
“Business_Name”: “Acme Store 1”,
“Monthly_Change_Tracking_Id”: 7,
“Effective_Date”: “2015-12-16T00:00:00.000”,
“Monthly_Revenue”: 100
},
{
“Parent_Customer_Id”: 12345,
“Location_Site_ID”: 98765,
“Parent_Customer_Name”: “Acme”,
“Business_Name”: “Acme Store 1”,
“Monthly_Change_Tracking_Id”: 21,
“Effective_Date”: “2016-06-16T00:00:00.000”,
“Monthly_Revenue”: 120
},
{
“Parent_Customer_Id”: 12345,
“Location_Site_ID”: 98765,
“Parent_Customer_Name”: “Acme”,
“Business_Name”: “Acme Store 1”,
“Monthly_Change_Tracking_Id”: 44,
“Effective_Date”: “2016-08-16T00:00:00.000”,
“Monthly_Revenue”: 130
},
{
“Parent_Customer_Id”: 12345,
“Location_Site_ID”: 98765,
“Parent_Customer_Name”: “Acme”,
“Business_Name”: “Acme Store 1”,
“Monthly_Change_Tracking_Id”: 55,
“Effective_Date”: “2020-12-16T00:00:00.000”,
“Monthly_Revenue”: 150
}
]
