Interval in the interval table

Question

I have a table with two columns:

+-----------+------------+
|   FROM    |     TO     |
+-----------+------------+
|2015-03-01 | 2015-03-04 |
|2015-03-05 | 2015-03-09 |
+-----------+------------+

And I want to write a function that will take two arguments - DateFrom and DateTo and check this interval. For example, if a function accepts DateFrom = 2015-03-03and DateTo = 2015-03-08as arguments, it should return true, because every day in this interval is in the table.

But if the table is as follows:

+-----------+------------+
|   FROM    |     TO     |
+-----------+------------+
|2015-03-01 | 2015-03-04 |
|2015-03-06 | 2015-03-09 |
+-----------+------------+

the function must return falsebecause it is 2015-03-05not in the table. Any idea for an algorithm? Thank you for your help.

Answer 1

, :

DECLARE @t TABLE
    (
      FromDate DATE ,
      ToDate DATE
    )

INSERT  INTO @t
VALUES  ( '2015-03-01', '2015-03-04' ),
        ( '2015-03-05', '2015-03-09' )

DECLARE @from DATE =  '2015-03-03', @to DATE = '2015-03-08'

;WITH cte1 AS(SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS d
             FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)
             CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)
             CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n)),
      cte2 AS(SELECT DATEADD(dd, d - 1, @from) AS d 
              FROM cte1 
              WHERE DATEADD(dd, d - 1, @from) <= @to)

SELECT  CASE WHEN EXISTS ( SELECT *
                           FROM cte2
                           WHERE NOT EXISTS ( SELECT *
                                              FROM   @t t
                                              WHERE  d BETWEEN t.FromDate AND t.ToDate ) )
             THEN 0
             ELSE 1
        END AS IntervalExists

1000 . cross joins ( cross join 10).

Answer 2

. usp (User Stored Procedure).

, :

CREATE PROC usp_CheckInterval
(
@DateFrom Date ,
@DateTo Date ,
@ReturnStatus bit OUTPUT
)
AS
BEGIN TRY

SET NOCOUNT ON;

IF(EXISTS (SELECT TOP 1 * FROM [YourTable] WHERE [StartDate] < @DateFrom AND [EndDate] > @DateTo))
BEGIN
SET @ReturnStatus = 0
END

ELSE
BEGIN
SET @ReturnStatus = 1
END

END TRY
BEGIN CATCH
    --Catch Here
END CATCH;

:

DECLARE @RC int
DECLARE @DateFrom date = GETDATE()
DECLARE @DateTo date =  GETDATE()
DECLARE @ReturnStatus bit

-- TODO: Set parameter values here.

EXECUTE @RC = [dbo].[usp_CheckInterval] 
   @DateFrom
  ,@DateTo
  ,@ReturnStatus OUTPUT   

SELECT @ReturnStatus

, .

Answer 3

- :

CREATE FUNCTION [dbo].[GetRunningNumbers](@counter INT=10000000, @StartAt INT=0)
RETURNS TABLE
AS 
RETURN
WITH E1(N) AS( -- 10 ^ 1 = 10 rows
    SELECT 1 FROM(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t(N)
),
E2(N) AS(SELECT 1 FROM E1 a CROSS JOIN E1 b), -- 10 ^ 2 = 100 rows
E4(N) AS(SELECT 1 FROM E2 a CROSS JOIN E2 b), -- 10 ^ 4 = 10,000 rows
E8(N) AS(SELECT 1 FROM E4 a CROSS JOIN E4 b), -- 10 ^ 8 = 10,000,000 rows
CteTally AS(
    SELECT TOP(ISNULL(@counter,1000000)) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) -1 + ISNULL(@StartAt,0) As Nmbr
    FROM E8
)
SELECT * FROM CteTally;
GO

:

    DECLARE @tbl TABLE(d1 DATETIME, d2 DATETIME);
    INSERT INTO @tbl VALUES({d'2015-03-01'},{d'2015-03-04'})
                          ,({d'2015-03-05'},{d'2015-03-09'}); 

    DECLARE @CheckStart DATETIME={d'2015-03-03'};
    DECLARE @CheckEnd DATETIME={d'2015-03-08'};

    --Check if all dates between these two checkdates are coverd within your table (be aware of the possible time component of a DATETIME!):
WITH AllCheckDates AS
(
    SELECT DATEADD(DAY,Nmbr,@CheckStart) AS RunningDate
    FROM dbo.GetRunningNumbers(DATEDIFF(DAY,@CheckStart,@CheckEnd)+1,0)
)
SELECT CASE WHEN EXISTS ( SELECT * FROM AllCheckDates WHERE NOT EXISTS ( SELECT * FROM @tbl AS t WHERE RunningDate BETWEEN t.d1 AND t.d2 ) )
            THEN 0 ELSE 1 END AS AllDatesCoveredInIntervall 

2015-04-05:

    DELETE FROM @tbl;
    INSERT INTO @tbl VALUES({d'2015-03-01'},{d'2015-03-04'})
                          ,({d'2015-03-06'},{d'2015-03-09'}); 

    --Same query, should deliver false now...
WITH AllCheckDates AS
(
    SELECT DATEADD(DAY,Nmbr,@CheckStart) AS RunningDate
    FROM dbo.GetRunningNumbers(DATEDIFF(DAY,@CheckStart,@CheckEnd)+1,0)
)
SELECT CASE WHEN EXISTS ( SELECT * FROM AllCheckDates WHERE NOT EXISTS ( SELECT * FROM @tbl AS t WHERE RunningDate BETWEEN t.d1 AND t.d2 ) )
            THEN 0 ELSE 1 END AS AllDatesCoveredInIntervall 
GO

...

DROP FUNCTION dbo.GetRunningNumbers;
GO