Splitting Query Results by Date Ranges

I have a table lineitems in which every record has a start date and an end date:

+---------+---------------------+---------------------+
| id      | startDate           | endDate             |
+---------+---------------------+---------------------+
| 1       | 2021-10-06          | 2021-10-07          |
| 2       | 2021-10-20          | 2021-10-22          |
+---------+---------------------+---------------------+

I'm trying to convert that structure to the following, where every day in the range of a given lineitems record becomes its own row:

+---------+---------------+
| li_id   | Date          | 
+---------+---------------+
| 1       | 2021-10-06    |
| 1       | 2021-10-07    |
| 2       | 2021-10-20    |
| 2       | 2021-10-21    |
| 2       | 2021-10-22    |
+---------+---------------+

I have another table all_dates that has a record for every calendar day that I could ever need, structured as follows:

+---------------------+----------------------------+-----------+
| shortDate           | longDate                   | dayOfWeek |
+---------------------+----------------------------+-----------+
| 2010-01-01          | Friday, January 01, 2010   | Friday    |
| 2010-01-02          | Saturday, January 02, 2010 | Saturday  |
| 2010-01-03          | Sunday, January 03, 2010   | Sunday    |
| 2010-01-04          | Monday, January 04, 2010   | Monday    |
| 2010-01-05          | Tuesday, January 05, 2010  | Tuesday   |

I can cross join these, but then I get results for dates that don't actually exist within the individual lineitems records. This approach was my best guess on how this could be achieved, but I don't need to pull from the all_dates table if there's a way to transform the lineitems table directly.

Thank you for any guidance that you may have!

lbecket

4 years ago

Best Answer

Level 39

For anyone who may stumble upon this in the future, I was able to get the desired result with the following query:

select li.id, shortDate
from lineitems li
inner join all_dates ad on ad.shortDate >= li.startDate and ad.shortDate <= li.endDate

I realize that this is pure SQL and my post is labelled as "Eloquent," but this certainly conveys the idea.

Please or to participate in this conversation.