TSQL - dates overlapping - number of days

Question

I have the following table on SQL Server:

ID	FROM	TO	OFFER NUMBER
1	2022.01.02	9999.12.31	1
1	2022.01.02	2022.02.10	2
2	2022.01.05	2022.02.15	1
3	2022.01.02	9999.12.31	1
3	2022.01.15	2022.02.20	2
3	2022.02.03	2022.02.25	3
4	2022.01.16	2022.02.05	1
5	2022.01.17	2022.02.13	1
5	2022.02.05	2022.02.13	2

The range includes the start date but excludes the end date. The date 9999.12.31 is given (comes from another system), but we could use the last day of the current quarter instead. I need to find a way to determine the number of days when the customer sees exactly one, two, or three offers. The following picture shows the method upon id 3:

The expected results should be like (without using the last day of the quarter):

ID	# of days when the customer sees only 1 offer	# of days when the customer sees 2 offers	# of days when the customer sees 3 offers
1	2913863	39	0
2	41	0	0
3	2913861	24	17
4	20	0	0
5	19	8	0

I've found this article but it did not enlighten me. Also I have limited privileges that is I am not able to declare a variable for example so I need to use "basic" TSQL.

Please provide a detailed explanation besides the code.

Thanks in advance!

As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. — Dale K, Feb 25 '22 at 00:19
`I have limited privileges that is I am not able to declare a variable`. You don't required any special privileges to declare a variable — Squirrel, Feb 25 '22 at 00:27
Thanks @Squirrel that was helpful! Still the situation is given — CsCs, Feb 25 '22 at 00:36
(1) Once you provide usable data (not an image), can you also confirm that the TO date is an *exclusive* date - meaning that the range includes the start date, but excludes the end date? (2) For unclosed offers, I would suggest using a NULL end date rather than a magic number. (3) Also for unclosed offers, would it be acceptable to calculate through a substitute end date (perhaps "today" or max(TO)) rather than 9999-12-31? Some solutions using calendar generators may work much more efficiently. — T N, Feb 25 '22 at 00:39
I believe I have an efficient solution, but need real data to test it out. Please replace your images with data that can be cut & pasted. — T N, Feb 25 '22 at 01:11

T N · Answer 1 · 2022-02-25T02:08:54.923

The following will (for each ID) extract all distinct dates, construct non-overlapping date ranges to test, and will count up the number of offers per range. The final step is to sum and format.

The fact that the start dates are inclusive and the end dates are exclusive while sometimes non-intuitive for the human, actually works well in algorithms like this.

DECLARE @Data TABLE (Id INT, FromDate DATETIME, ToDate DATETIME, OfferNumber INT)
INSERT @Data
VALUES
    (1, '2022-01-02', '9999-12-31', 1),
    (1, '2022-01-02', '2022-02-10', 2),
    (2, '2022-01-05', '2022-02-15', 1),
    (3, '2022-01-02', '9999-12-31', 1),
    (3, '2022-01-15', '2022-02-20', 2),
    (3, '2022-02-03', '2022-02-25', 3),
    (4, '2022-01-16', '2022-02-05', 1),
    (5, '2022-01-17', '2022-02-13', 1),
    (5, '2022-02-05', '2022-02-13', 2)
;

WITH Dates AS ( -- Gather distinct dates
    SELECT Id, Date = FromDate FROM @Data
    UNION --(distinct)
    SELECT Id, Date = ToDate FROM @Data
),
Ranges AS ( --Construct non-overlapping ranges (The ToDate = NULL case will be ignored later)
    SELECT ID, FromDate = Date, ToDate = LEAD(Date) OVER(PARTITION BY Id ORDER BY Date)
    FROM Dates
),
Counts AS ( -- Calculate days and count offers per date range
    SELECT R.Id, R.FromDate,  R.ToDate,
        Days = DATEDIFF(DAY, R.FromDate, R.ToDate),
        Offers = COUNT(*)
    FROM Ranges R
    JOIN @Data D ON D.Id = R.Id
        AND D.FromDate <= R.FromDate
        AND D.ToDate >= R.ToDate
    GROUP BY R.Id, R.FromDate,  R.ToDate
)
SELECT Id
    ,[Days with 1 Offer] = SUM(CASE WHEN Offers = 1 THEN Days ELSE 0 END)
    ,[Days with 2 Offers] = SUM(CASE WHEN Offers = 2 THEN Days ELSE 0 END)
    ,[Days with 3 Offers] = SUM(CASE WHEN Offers = 3 THEN Days ELSE 0 END)
FROM Counts
GROUP BY Id

The WITH clause introduces Common Table Expressions (CTEs) which progressively build up intermediate results until a final select can be made.

Results:

Id	Days with 1 Offer	Days with 2 Offers	Days with 3 Offers
1	2913863	39	0
2	41	0	0
3	2913861	24	17
4	20	0	0
5	19	8	0

Alternately, the final select could use a pivot. Something like:

SELECT Id,
    [Days with 1 Offer] = ISNULL([1], 0),
    [Days with 2 Offers] = ISNULL([2], 0),
    [Days with 3 Offers] = ISNULL([3], 0)
FROM (SELECT Id, Offers, Days FROM Counts) C
PIVOT (SUM(Days) FOR Offers IN ([1], [2], [3])) PVT
ORDER BY Id

See This db<>fiddle for a working example.

score 1 · Answer 2 · answered Feb 25 '22 at 01:37

Find all date points for each ID. For each date point, find the number of overlapping.

Refer to comments within query

with 
dates as
(
    -- get all date points
    select ID, theDate = FromDate from offers
    union   -- union to exclude any duplicate
    select ID, theDate = ToDate from offers
),
cte as
(
    select ID         = d.ID, 
           Date_Start = d.theDate, 
           Date_End   = LEAD(d.theDate) OVER (PARTITION BY ID ORDER BY theDate), 
           TheCount   = c.cnt
    from   dates d
           cross apply
           (
                -- Count no of overlapping
                select cnt         = count(*)
                from   offers x
                where  x.ID        = d.ID
                and    x.FromDate <= d.theDate
                and    x.ToDate   >  d.theDate
           ) c
)
select ID, TheCount, days = sum(datediff(day, Date_Start, Date_End))
from   cte
where  Date_End is not null
group by ID, TheCount
order by ID, TheCount

Result :

ID	TheCount	days
1	1	2913863
1	2	39
2	1	41
3	1	2913861
3	2	29
3	3	12
4	1	20
5	1	19
5	2	8

To get to the required format, use PIVOT

dbfiddle demo

TSQL - dates overlapping - number of days

2 Answers2