Find Date From Non Contiguous Ranges
- by AGoodDisplayName
I am looking to find an best way to find a date from date ranges that may or may not be contiguous (I am trying to avoid a cursor, or a heavy function if possible).
Lets say I have hotel guests that come and go (check in, check out). I want to find the date that a certain guest stayed their 45th night with us. The database we use records the data as so:
Create Table #GuestLog(
ClientId int,
StartDate DateTime,
EndDate DateTime)
Here is some data
Insert Into #GuestLog Values(1, '01/01/2010', '01/10/2010')
Insert Into #GuestLog Values(1, '01/16/2010', '01/29/2010')
Insert Into #GuestLog Values(1, '02/13/2010', '02/26/2010')
Insert Into #GuestLog Values(1, '04/05/2010', '06/01/2010')
Insert Into #GuestLog Values(1, '07/01/2010', '07/21/2010')
So far I can only think of solutions that involve functions with temp tables and crazy stuff like that, I feel like I'm over thinking it.
Thanks ahead of time.
