Expand dates for table in SQL Server

Question

I'd like to get better sense of the historical inventory of my objects table.
Thus Objects has basically three relevant columns:

car_id, incoming_date, outgoing_date

Fine, my objects are cars.

For graphing purposes, I'd like to expand the dates on which each car was held.

So the resulting table would look like:

car_id, held_date and where I can get that held_date is between incoming and outgoing for each car.

I did find this post that creates the dates in between, but I haven't been able to incorporate this with respect to each car. Help.

Also as a side note, I don't have permission to create calendar tables, so hopefully there's a way around that as well. Thanks.

Some initial data to make picture more clear and not abstract? — JohnyL, Aug 03 '18 at 17:03

score 1 · Answer 1 · edited Aug 03 '18 at 16:02

Assuming you have made yourself a calendar table which you definitely 100% should because it's horribly useful. You can then just do something like:

 SELECT car_id, calendar.date as held_date
 FROM yourtable
     INNER JOIN calendar 
         ON calendar.date BETWEEN yourtable.incoming_date and yourtable.outgoing_date;

And I just read you don't have permission. If you could get permission to build a a table to hold a list of dates, then do it. Otherwise... you can get funky and make a recursive CTE to generate a derived table of dates and use that. It's not going to be fast and it makes for some ugly sql:

WITH Calendar AS  
(  
  select DATEADD(day, 1, '2010-01-01') as date
  UNION ALL  
  select DATEADD(day, 1, date) 
  from Calendar 
  where date < '2020-12-31'  
)
 SELECT car_id, calendar.date as held_date
     FROM yourtable
         INNER JOIN calendar 
             ON calendar.date BETWEEN yourtable.incoming_date and yourtable.outgoing_date
     OPTION (MAXRECURSION 0);

Expand dates for table in SQL Server

1 Answers1