inefficientmkts inefficientmkts - 1 year ago 43
SQL Question

how to create list of unique dates from a date range

I have thousands of records with motel visit dates like Table A:

Name CheckInDate CheckOutDate
Pete 11/1/2016 11/1/2016
Mike 11/1/2016 11/2/2016
Don 11/1/2016 11/3/2016


Need to create query to generate a temp table with unique entry for every date of guests' visit. Like this:

Name VisitDate
Pete 11/1/2016
Mike 11/1/2016
Mike 11/2/2016
Don 11/1/2016
Don 11/2/2016
Don 11/3/2016


This one has me stumped. I appreciate your help so much!

Answer Source

A tally/calendar table would be optimal, but you can do this via an ad-hoc tally table in concert with a CROSS APPLY like so:

Example

Select A.[Name] 
      ,VisitDate = B.D
 From  YourTable A
 Cross Apply (
                Select Top (DateDiff(DD,[CheckInDate],[CheckOutDate])+1) D=DateAdd(DAY,-1+Row_Number() Over (Order By (Select Null)),[CheckInDate]) 
                 From  master..spt_values n1
             ) B

Returns

Name    VisitDate
Pete    2016-11-01
Mike    2016-11-01
Mike    2016-11-02
Don     2016-11-01
Don     2016-11-02
Don     2016-11-03

Edit - If you have (or want) a DateTable

Select A.[Name] 
      ,VistiDate = B.[Date]
 From  YourTable A
 Join  YourDateTable B
  on   B.[Date] between A.[CheckInDate] and A.[CheckOutDate]
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download