SQL Server - Query With Multiple Date Ranges in subquery-CodePudding

I have used conditional aggregation in another query where I needed to use multiple date ranges. In this case the date ranges are needed in a sub-query.

I would like to know if can I get desired results in one single query (without using UNION).

I need to check if a given record EXISTS in the subquery with date-range. Since I need to use EXISTS rather than a join - I am running into this issue.

Here is a sample script/data. The expected results table is for demonstration.

IF OBJECT_ID('tempdb..#Entity') IS NOT NULL DROP TABLE #Entity
IF OBJECT_ID('tempdb..#EntityDate') IS NOT NULL DROP TABLE #EntityDate
IF OBJECT_ID('tempdb..#ExpectedOutput') IS NOT NULL DROP TABLE #ExpectedOutput

> `DECLARE @FortnightStart DATETIME = '2020/08/01', @FortnightEnd DATETIME = '2020/08/14 23:59:59'
DECLARE @QuarterStart DATE = '2020/04/01', @QuarterEnd DATE = '2020/06/30 23:59:59'


> `SELECT 'Fortnight' DateRange, @FortnightStart 'Start', @FortnightEnd 'End'
UNION
SELECT 'Quarter', @QuarterStart, @QuarterEnd 

CREATE TABLE #Entity (
    EntityId INT IDENTITY(1, 1),
    EntityName VARCHAR(50)
)

CREATE TABLE #EntityDate (
    EntityDateId INT IDENTITY(1, 1),
    EntityId INT,
    SubmittedDate DATETIME
)

ALTER TABLE #EntityDate ADD CONSTRAINT FK_EntityDate_Entity FOREIGN KEY (EntityId) REFERENCES Entity(EntityId)


INSERT INTO #Entity (EntityName) 
SELECT 'Alice'
UNION
SELECT 'Bob'
UNION
SELECT 'Cameron'
UNION
SELECT 'Diego'
UNION
SELECT 'Elliot'



SELECT * FROM #Entity


INSERT INTO #EntityDate(EntityId, SubmittedDate)
SELECT 1, '08/01/2020 11:00:00' -- only 1 record is expected in the output for this Entity
UNION
SELECT 1, '08/10/2020 10:00:00' 
UNION
SELECT 1, '04/10/2020 10:00:00'  -- this record should show up for the quarter date range
UNION
SELECT 2, '06/01/2020 11:00:00'  -- 
UNION
SELECT 3, '05/01/2020'  -- only 1 record is expected in the output for this Entity
UNION
SELECT 3, '06/01/2020' 
UNION
SELECT 4, '10/01/2021' -- does not fit in any date range
UNION
SELECT 5, '08/02/2020' 


SELECT *
FROM #EntityDate d
    INNER JOIN #Entity e ON d.EntityId = e.EntityId


SELECT * 
FROM #Entity E
WHERE EXISTS (  SELECT 1 
                FROM #EntityDate d
                WHERE SubmittedDate BETWEEN @FortnightStart AND @FortnightEnd AND e.EntityId = D.EntityId
                )

SELECT * 
FROM #Entity E
WHERE EXISTS (  SELECT 1 
                FROM #EntityDate d
                WHERE SubmittedDate BETWEEN @QuarterStart AND @QuarterEnd AND e.EntityId = D.EntityId
                )

CREATE TABLE #ExpectedOutput
(       
    EntityId INT,   
    DateRange VARCHAR(50)
)

INSERT INTO #ExpectedOutput (EntityId, DateRange)
SELECT 1, 'Fortnight'
UNION 
SELECT 5, 'Fortnight'
UNION 
SELECT 1, 'Quarter'
UNION 
SELECT 2, 'Quarter'
UNION 
SELECT 3, 'Quarter'


SELECT o.*, e.EntityName
FROM #ExpectedOutput o
    INNER JOIN #Entity e ON o.EntityId = e.EntityId
ORDER BY O.DateRange, o.EntityId

CodePudding user response：

try something like this

SELECT * FROM (VALUES(1, '08/01/2020 11:00:00'),
(1, '08/10/2020 10:00:00'),
(1, '04/10/2020 10:00:00'),
(2, '06/01/2020 11:00:00'),  -- 
(3, '05/01/2020'),
(3, '06/01/2020'), 
(4, '10/01/2021'),
(5, '08/02/2020') 
) EntityIDate(EntityId,SubmittedDate)

Documentation: https://docs.microsoft.com/en-us/u-sql/statements-and-expressions/select/from/select-selecting-from-the-values-table-value-constructor

CodePudding user response：

Using the virtual Dates table you created at the top of your script, you need to join that to Entity, using the EXISTS as the ON condition

DECLARE @FortnightStart DATETIME = '2020/08/01', @FortnightEnd DATETIME = '2020/08/14 23:59:59';
DECLARE @QuarterStart DATE = '2020/04/01', @QuarterEnd DATE = '2020/06/30 23:59:59';

WITH Dates AS (
    SELECT 'Fortnight' DateRange, @FortnightStart Start, @FortnightEnd [End]
    UNION ALL
    SELECT 'Quarter', @QuarterStart, @QuarterEnd
)
SELECT
  e.EntityId,
  d.DateRange
FROM Dates d
JOIN #Entity E ON EXISTS (SELECT 1 
    FROM #EntityDate ed
    WHERE ed.SubmittedDate BETWEEN d.Start AND d.[End]
      AND ed.EntityId = e.EntityId
    );

db<>fiddle