Find date from non-contiguous ranges

Question

I am looking to find the best way to find a date from date ranges that may or may not be contiguous (I try to avoid a cursor or a heavy function if possible).

Suppose I have hotel guests who come and go (check-in, check-out). I want to find the date when a guest stayed with us on the 45th night . The database used records data as follows:

Create Table #GuestLog(
    ClientId int, 
    StartDate DateTime, 
    EndDate DateTime)

Here are some details

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 include functions with temporary tables and such crazy things, I feel like I think about it.

Thanks in advance.

EDIT: small solution @Andriy M.

DECLARE @ClientID int, @NightNo int;
SET @ClientID = 1;
SET @NightNo = 45;

SELECT *
FROM ( SELECT  gl.ClientId
        , Date = gl.StartDate + v.number - 1
        , rownum = ROW_NUMBER() OVER ( PARTITION BY gl.ClientId ORDER BY gl.StartDate, v.Number)
    FROM #GuestLog gl
    INNER JOIN master..spt_values v ON v.type = 'P'
    AND v.number BETWEEN 1  AND gl.EndDate - gl.StartDate + 1) as s //--added "+ 1"
WHERE ClientId = @ClientId
AND rownum = @NightNo

Answer 1

, ( ?).

: "45- ", , , , . , - 1, Date.

DECLARE @ClientID int, @NightNo int;
SET @ClientID = 1;
SET @NightNo = 45;

SELECT *
FROM (
  SELECT
    gl.ClientId,
    Date = gl.StartDate + v.number - 1,
    rownum = ROW_NUMBER() OVER (
      PARTITION BY gl.ClientId
      ORDER BY gl.StartDate, v.Number
    )
  FROM #GuestLog gl
    INNER JOIN master..spt_values v ON v.type = 'P'
      AND v.number BETWEEN 1 AND gl.EndDate - gl.StartDate
) s
WHERE ClientId = @ClientId
  AND rownum = @NightNo

Answer 2

( , - .):

WITH logd 
     AS (SELECT a.*, 
                (SELECT SUM(Datediff(d, startdate, enddate)) 
                 FROM   #guestlog b 
                 WHERE  b.clientid = a.clientid 
                        AND b.startdate <= a.startdate) dayssofar 
         FROM   #guestlog a) 
SELECT a.*, 
       Dateadd(d, ( 45 - dayssofar ), enddate) 
FROM   (SELECT b.*, 
               Row_number() OVER(PARTITION BY clientid ORDER BY dayssofar)rn 
        FROM   logd b 
        WHERE  dayssofar > 44) a 
WHERE  rn = 1  

Answer 3

, , :

tally ( , ).

, :

SELECT DATEADD(d,n.number,'01/01/2000') AS StayedDate
FROM numbers n
INNER JOIN #GuestLog g ON DATEADD(d,n.number,'01/01/2000') BETWEEN g.StartDate AND g.EndDate)
ORDER BY n.number

CTE ROW_NUMBER() 45- .

, ( , ), DATEADD.

Answer 4

I tested my solution in SQL Server 2008 R2 in a database with compatibility 90 (SQL Server 2005), so I believe that this will do what you want:

-- PLEASE NOTE: MAXRECURSION at the bottom needs to have a number that is higher than the
--  number of stored stays that any guest this will run on will have. Otherwise you'll need
--  to find a way to do this without recursion.

-- Parameterized because...why not? :)
DECLARE @CustomerID INT = 1
    , @NthStayDay INT = 45;

-- This does nothing but get the rows out of GuestLog that we care about. From my experience
--  it a good idea to do a simple data grab from a physical table or indexed view using
--  a seek, then play with that smaller subset of data in other CTE's. Though I'm sure that
--  those with more performance knowledge could give better answers. RowNumber is added for
--  recursion in the next CTE.
WITH OrderedStays(RowNumber, StartDate, EndDate) AS
(
    SELECT
        ROW_NUMBER() OVER(ORDER BY StartDate) AS RowNumber
        , StartDate
        , EndDate
    FROM @GuestLog GuestLog
    WHERE GuestLog.ClientId = @CustomerID
)
-- This is a recusive CTE, but I don't imagine it will preform to badly because there is no IO
--  at this point, simply processing the previous CTE. You'll have to be the judge of that.
--  The purpose of this CTE is to be able to limit down to the start date that we care about.
, StayRanges(RowNumber, StartDate, EndDate, FirstDayCount, LastDayCount) AS
(
    -- This is our anchor row. It is the first date range at which the guest stayed with you.
    --  The DATEDIFF returns 9 with dates of 20100101 - 20100110, but since I don't think the 
    --  0th day stayed makes sense, I'm making it return 10 in that case since we're starting
    --  at 1.
    SELECT
        RowNumber
        , StartDate
        , EndDate
        , 1 AS FirstDayCount
        , DATEDIFF(DAY, StartDate, EndDate) + 1 AS LastDayCount
    FROM OrderedStays
    WHERE RowNumber = 1

    UNION ALL

    -- This is the recursion. This joins the first CTE on what we have where the first CTE's
    --  RowNumber is 1 more than whatever is in our StayRanges CTE. The column logic is the
    --  same as above, but now we need to add in the LastDayCount from our previous iteration.
    SELECT
        OrderedStays.RowNumber
        , OrderedStays.StartDate
        , OrderedStays.EndDate
        , StayRanges.LastDayCount + 1 AS FirstDayCount
        , DATEDIFF(DAY, OrderedStays.StartDate, OrderedStays.EndDate) + StayRanges.LastDayCount + 1 AS LastDayCount
    FROM OrderedStays
    INNER JOIN StayRanges
        ON (StayRanges.RowNumber + 1) = OrderedStays.RowNumber
)
-- Now that we have our ranges, we can select the range that has the day we want in it with a
--  simple between. Once that done, take out the FirstDayCount from the day we care about so
--  that you're left with the difference from the StartDate and the date we want, and add that
--  to the StartDate. Done!
SELECT
    DATEADD(DAY, @NthStayDay - FirstDayCount, StartDate) AS DateOfNthStayDate
FROM StayRanges
WHERE @NthStayDay BETWEEN FirstDayCount AND LastDayCount
OPTION(MAXRECURSION 5000)