SQL Server - Get the Date Ranges I have a table with start date and end date. For example,
PlaceID | StartDate  | EndDate
1       | 2020-01-01 | 2020-01-04
1       | 2020-01-10 | 2020-01-15
2       | 2020-01-03 | 2020-01-06
2       | 2020-01-20 | 2020-01-23

I need to write a script to get the date ranges that is not in the table for each PlaceID. (For now considering the month of January.)
For PlaceID 1 - I need the output date ranges as
2020-01-05 to 2020-01-09
2020-01-16 to 2020-01-31

For PlaceID 2 - I need the output date ranges as
2020-01-01 to 2020-01-02
2020-01-07 to 2020-01-19
2020-01-24 to 2020-01-31

What is the SQL logic behind this that I can use?
This is essentially an islands problem, except you are trying to find all the rows that don't belong to an island (these are gaps, but conceptually I find it easier to think about the islands). Basically, we want to find all of the possible days in your overall range, then identify the days that are &quot;covered&quot; within that range. The end result you want are all of the islands of days that are not covered.
My solution assumes you are on a currently supported version of SQL Server (2012+). Please always use a tag for the minimum version of SQL Server you need to support, since that can change the answer and prevent people from wasting time.
-- sample table + data:
DECLARE @t table(PlaceID int, StartDate date, EndDate date);

INSERT @t(PlaceID, StartDate, EndDate) VALUES
(1,'20200101','20200104'),(1,'20200110','20200115'),
(2,'20200103','20200106'),(2,'20200120','20200123');


-- input parameters (place you care about, start and end of date range):
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 &lt; @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 &gt;= t.StartDate 
      AND r.d &lt;= t.EndDate
      AND t.PlaceID = @PlaceIDofInterest
    WHERE t.PlaceID IS NULL
)
SELECT MIN(d), MAX(d) -- for each island, grab the start and end
  FROM islands 
  GROUP BY island 
  ORDER BY MIN(d);

Generating the contiguous range of dates is a topic of its own. I like the recursive CTE option because it's quick and easy for me to remember, and doesn't require any other objects to exist. But it has a quirk: If the range of days could be more than 100, you'll need to add OPTION (MAXRECURSION x) to the end of the statement (where x is the largest number of days you would need to support). If x &gt; 32,767 (89 years or so), x will need to be 0.
If you don't like recursive CTEs, but you already have a calendar table (say, dbo.Calendar with a [date] column), the date_range CTE can simply be:
;WITH date_range(d) AS
(
  SELECT [date] FROM dbo.Calendar
  WHERE [date] &gt;= @StartDateOfInterest
    AND [date] &lt;= @EndDateOfInterest
),

If you don't like recursive CTEs, and don't have a calendar table, but you do have a numbers table (say, dbo.Numbers, with an int column named num), you can do this for the initial CTE:
;WITH date_range(d) AS
(
  SELECT TOP (DATEDIFF(DAY, @StartDateOfInterest, @EndDateOfInterest) + 1)
    DATEADD(DAY, num-1, @StartDateOfInterest)
  FROM dbo.Numbers
  WHERE num &gt;= 1 ORDER BY num
),

And if you don't like recursive CTEs and you don't have a calendar or numbers table, you can do this:
;WITH date_range(d) AS
(
  SELECT TOP (DATEDIFF(DAY, @StartDateOfInterest, @EndDateOfInterest) + 1)
    DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY [object_id])-1, @StartDateOfInterest)
  FROM sys.all_columns
  ORDER BY [object_id]
),

If you don't like recursive CTEs, you don't have a calendar or numbers table, and the caller doesn't have access to catalog views like sys.all_columns, I give up.
More information here:

Generate a set or sequence without loops – part 1
Generate a set or sequence without loops – part 2
Generate a set or sequence without loops – part 3
The SQL Server Numbers Table, Explained - Part 1
The SQL Server Numbers Table, Explained - Part 2
Creating a date dimension or calendar table in SQL Server