2

I have a table with StartDate and EndDate. I wanted to get the missing Date and The script I have does it. But if given this data:

LocationID  StartDate   EndDate
----------  ----------  ----------  
1           2020-01-01  2020-01-03  
1           2020-01-04  2020-01-05  
1           2020-01-10  2020-01-15  

This query:

DECLARE @t table(PlaceID int, StartDate date, EndDate date);

INSERT @t(PlaceID, StartDate, EndDate) VALUES
(1,'20200101','20200103'),(1,'20200104','20200105'),(1,'20200110','20200115');

--(2,'20200103','20200106'),(2,'20200107','20200110'),(2,'20200120','20200123');

-- input parameters 
DECLARE @PlaceIDofInterest   int  = 1,
        @StartDateOfInterest date = '20200101', 
        @EndDateOfInterest   date = '20200131';
    
;WITH date_range(d) AS -- the entire range of days we care about
(
  SELECT @StartDateOfInterest UNION ALL
  SELECT DATEADD(DAY, 1, d) FROM date_range
         WHERE d < @EndDateOfInterest
),
islands AS -- grouped sets of days _not_ covered
(
  SELECT r.d, island = DATEADD(DAY, DENSE_RANK() OVER (ORDER BY r.d) * -1, r.d) 
    FROM date_range AS r
    LEFT OUTER JOIN @t AS t
      ON  r.d >= t.StartDate 
      AND r.d <= t.EndDate
      AND t.PlaceID = @PlaceIDofInterest
    WHERE t.PlaceID IS NULL
)
SELECT  
      MIN(d) as STARTDATE , 
      MAX(d) as ENDDATE-- for each island, grab the start and end
  FROM 
      islands 
 
  GROUP BY island 
  ORDER BY MIN(d);

Returns this output:

STARTDATE   ENDDATE  
---------   ----------    
2020-01-06  2020-01-09  
2020-01-16  2020-01-31  

But I wanted the output as:

STARTDATE   ENDDATE
---------   ----------    
2020-01-03  2020-01-04  
2020-01-05  2020-01-10  
2020-01-15  2020-01-31  

What changes can I make ?

Edited to add: A location can be booked from night to morning. So StartDate Night to EndDate Morning. SO the Location will be free on EndDate night, unless it is booked. Also the Location will be free on Startdate Morning.

1st of Jan to 3rd of Jan is booked - that means from 1st night to 3rd morning. next booking is only on 4th night. So the location is free from 3rd night to 4th morning too. So i would need the result to have 3rd an as start date and 4thJan as enddate in one row. Hope this is clear.

  • Cross posting is frowned upon by the community. Also, if you do get answers, to the same question on multiple sites, and one solves the problem you should accept at least one of them as the solution. – Larnu Oct 17 at 11:58
1

You really don't need a cte for that

Window Function LEAD can do what you need

It can be refined if the dates correspond

CREATE table t1(PlaceID int, StartDate date, EndDate date);
GO
INSERT INTO  t1 (PlaceID, StartDate, EndDate) VALUES
(1,'20200101','20200103')
,(1,'20200104','20200111')
,(1,'20200111','20200113')
,(1,'20200114','20200121')
,(1,'20200121','20200123')
,(2,'20200103','20200106')
,(2,'20200107','20200110')
,(2,'20200120','20200123');
GO
SELECT
PlaceID
,EndDate
,Nextdate
FROM
(SELECT
PlaceID
,EndDate,
COALESCE(DATEADD(DAY,0,lead(StartDate,1,NULL)  OVER (partition by PlaceID ORDER BY StartDate)),EOMONTH(StartDate)) AS Nextdate
FROM t1) t2
WHERE EndDate != Nextdate
GO
PlaceID | EndDate    | Nextdate  
------: | :--------- | :---------
      1 | 2020-01-03 | 2020-01-04
      1 | 2020-01-13 | 2020-01-14
      1 | 2020-01-23 | 2020-01-31
      2 | 2020-01-06 | 2020-01-07
      2 | 2020-01-10 | 2020-01-20
      2 | 2020-01-23 | 2020-01-31

db<>fiddle here

| improve this answer | |
  • Thanks for your quick reply.. Doesn't seem to work If I have the below bookings, LocationID, StartDate, EndDate 1, 2020-01-04, 2020-01-11 1, 2020-01-11, 2020-01-13 1, 2020-01-14, 2020-01-21 1, 2020-01-21, 2020-01-25 – Jeni Oct 16 at 14:41
  • this works, so you have to explain what exactly you don't i a m guessing you don't want the rows with the same day? – nbk Oct 16 at 14:59
  • as said in my last comment, If the Enddate and Startdate has got the date 11th Jan.. Then I do not need them in the output. But the script is bringing up 11th as well. – Jeni Oct 16 at 15:02
  • see changed answer – nbk Oct 16 at 15:18
  • thanks a lot. much appreciated – Jeni Oct 16 at 15:37
0

The answer by @nbk will get you what you want, I'm just putting this here as it may prove interesting for you; although it is not complete. At the moment, it gets the list of possible booking dates and shows whether it's booked or not according to the rules you outlined. It may be useful as a calendar view or something like it for you.

DECLARE @Source TABLE
    (
    LocationID INT NOT NULL
    , StartDate DATE NOT NULL
    , EndDate DATE NOT NULL
    )

INSERT INTO @Source 
    (LocationID, StartDate, EndDate)
VALUES (1, '1/1/2020', '1/3/2020')
    , (1, '1/4/2020', '1/5/2020')
    , (1, '1/10/2020', '1/15/2020')

DECLARE @LocationOfInterest INT = 1
DECLARE @StartDate DATE = '1/1/2020'
DECLARE @EndDate DATE = '1/31/2020'

;WITH CTE_I AS
    (
    SELECT *
        , PriorEndDate = LAG(EndDate) OVER (PARTITION BY LocationID ORDER BY StartDate)
        , NextStart = LEAD(StartDate) OVER (PARTITION BY LocationID ORDER BY StartDate)
    FROM @Source AS S
    WHERE LocationID = @LocationOfInterest
    )
, CTE_G AS
    (
    SELECT I.LocationID
        , I.StartDate
        , I.EndDate
        , I.PriorEndDate 
        , I.NextStart
        , IslandStart = COALESCE(PriorEndDate, EndDate)
        , IslandEnd = I.NextStart 
    FROM CTE_I AS I
    )
SELECT * FROM CTE_G


;WITH CTE_Calendar AS
    (
    SELECT @StartDate AS D
    UNION ALL
    SELECT DATEADD(DAY, 1, D)
    FROM CTE_Calendar
    WHERE D < @EndDate
    )
, CTE_BookingCalendar AS
    (
    SELECT C.D AS BookingStart 
        , DATEADD(DAY, 1, C.D) AS BookingEnd
    FROM CTE_Calendar AS C
    )
, CTE_WhatIsBooked AS
    (
    SELECT B.BookingStart
        , B.BookingEnd
        , S.LocationID
        , S.StartDate
        , S.EndDate
    FROM CTE_BookingCalendar AS B
        LEFT OUTER JOIN @Source AS S ON B.BookingStart BETWEEN S.StartDate AND S.EndDate AND S.EndDate >= B.BookingEnd
    WHERE S.LocationID IS NULL OR S.LocationID = @LocationOfInterest
    ORDER BY B.BookingStart
    )
| improve this answer | |
  • Thanks fo ryour quick reply.. Doesn't seem to work If I have the below bookings, LocationID, StartDate, EndDate 1, 2020-01-04, 2020-01-11 1, 2020-01-11, 2020-01-13 1, 2020-01-14, 2020-01-21 1, 2020-01-21, 2020-01-25 – Jeni Oct 16 at 14:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.