Hi I have a SQL question, I'm trying to get end of month records for each person within a certain date range. Essentially I want this record to be tracking historically (years worth of data) using some sort of End of Month record if their start and end dates fall within the last day of each month. So the data currently looks like this (using just 2022 for simplicity)..
| Name | StartDate | EndDate |
|---|---|---|
| John Smith | 2022-01-15 | 2022-04-10 |
| Jane Doe | 2022-01-18 | 2022-03-05 |
| Rob Johnson | 2022-03-07 | 2022-07-18 |
And what I'm looking for is something like this
| Name | StartDate | EndDate | EndMonth |
|---|---|---|---|
| John Smith | 2022-01-15 | 2022-04-10 | 2022-01-31 |
| Jane Doe | 2022-01-18 | 2022-03-05 | 2022-01-31 |
| John Smith | 2022-01-15 | 2022-04-10 | 2022-02-28 |
| Jane Doe | 2022-01-18 | 2022-03-05 | 2022-02-28 |
| John Smith | 2022-01-15 | 2022-04-10 | 2022-03-31 |
| Rob Johnson | 2022-03-07 | 2022-07-18 | 2022-03-31 |
| Rob Johnson | 2022-03-07 | 2022-07-18 | 2022-04-30 |
| Rob Johnson | 2022-03-07 | 2022-07-18 | 2022-05-31 |
etc...
I tried connecting the Records table with a Calendar table i have that has End of Month data for each day for several years back but can't figure this out. The Calendar table looks something like this..
| Date | EndMonth |
|---|---|
| 2022-01-01 | 2022-01-31 |
| 2022-01-02 | 2022-01-31 |
| ..... |
CodePudding user response：
JOINing two tables should give the desired result.
You didn't mention a table name for your initial table,
so I will refer to it as the person table.
Your calendar table is a good start.
I don't believe you need the Date column.
Just a single EndMonth column should suffice.
JOIN person against calendar, WHERE EndMonth BETWEEN StartDate AND EndDate.
And you're done!
Once you've worked out the SELECT syntax details, please share them here.