Calculate Total Visits

I am trying to write a query where I have to calculate number of visits for a customer by taking care of overlapping days. Suppose for itemID 2009 start date is 23rd and end date is 26th therefore item 20010 is between these days we will not add this purchase date to our total count.

Example Scenario:

``````Item ID Start Date   End Date   Number of days     Number of days Candidate for visit count
20009   2015-01-23  2015-01-26     4                      4
20010   2015-01-24  2015-01-24     1                      0
20011   2015-01-23  2015-01-26     4                      0
20012   2015-01-23  2015-01-27     5                      1
20013   2015-01-23  2015-01-27     5                      0
20014   2015-01-29  2015-01-30     2                      2
``````

OutPut should be 7 VisitDays

Input Table:

``````CREATE TABLE #Items
(
CustID INT,
ItemID INT,
StartDate DATETIME,
EndDate DATETIME
)

INSERT INTO #Items
SELECT 11205, 20009, '2015-01-23',  '2015-01-26'
UNION ALL
SELECT 11205, 20010, '2015-01-24',  '2015-01-24'
UNION ALL
SELECT 11205, 20011, '2015-01-23',  '2015-01-26'
UNION ALL
SELECT 11205, 20012, '2015-01-23',  '2015-01-27'
UNION ALL
SELECT 11205, 20012, '2015-01-23',  '2015-01-27'
UNION ALL
SELECT 11205, 20012, '2015-01-28',  '2015-01-29'
``````

I have tried so far:

``````CREATE TABLE #VisitsTable
(
StartDate DATETIME,
EndDate DATETIME
)

INSERT  INTO #VisitsTable
SELECT DISTINCT
StartDate,
EndDate
FROM    #Items items
WHERE   CustID = 11205
ORDER BY StartDate ASC

IF EXISTS (SELECT TOP 1 1 FROM #VisitsTable)
BEGIN

SELECT  ISNULL(SUM(VisitDays),1)
FROM    ( SELECT DISTINCT
abc.StartDate,
abc.EndDate,
DATEDIFF(DD, abc.StartDate, abc.EndDate) + 1 VisitDays
FROM      #VisitsTable abc
INNER JOIN #VisitsTable bc ON bc.StartDate NOT BETWEEN abc.StartDate AND abc.EndDate
) Visits

END

--DROP TABLE #Items
--DROP TABLE #VisitsTable
``````

This first query creates different Start Date and End Date ranges with no overlaps.

Note:

• Your sample(`id=0`) is mixed with a sample from Ypercube (`id=1`)
• This solution may not scale well with huge amount of data for each id or huge number of id. This has the advantage of not requiring a number table. With large dataset, a number table will very likely give better performances.

Query:

``````SELECT DISTINCT its.id
, Start_Date = its.Start_Date
, End_Date = COALESCE(DATEADD(day, -1, itmax.End_Date), CASE WHEN itmin.Start_Date > its.End_Date THEN itmin.Start_Date ELSE its.End_Date END)
--, x1=itmax.End_Date, x2=itmin.Start_Date, x3=its.End_Date
FROM @Items its
OUTER APPLY (
SELECT Start_Date = MAX(End_Date) FROM @Items std
WHERE std.Item_ID <> its.Item_ID AND std.Start_Date < its.Start_Date AND std.End_Date > its.Start_Date
) itmin
OUTER APPLY (
SELECT End_Date = MIN(Start_Date) FROM @Items std
WHERE std.Item_ID <> its.Item_ID+1000 AND std.Start_Date > its.Start_Date AND std.Start_Date < its.End_Date
) itmax;
``````

Output:

``````id  | Start_Date                    | End_Date
0   | 2015-01-23 00:00:00.0000000   | 2015-01-23 00:00:00.0000000   => 1
0   | 2015-01-24 00:00:00.0000000   | 2015-01-27 00:00:00.0000000   => 4
0   | 2015-01-29 00:00:00.0000000   | 2015-01-30 00:00:00.0000000   => 2
1   | 2016-01-20 00:00:00.0000000   | 2016-01-22 00:00:00.0000000   => 3
1   | 2016-01-23 00:00:00.0000000   | 2016-01-24 00:00:00.0000000   => 2
1   | 2016-01-25 00:00:00.0000000   | 2016-01-29 00:00:00.0000000   => 5
``````

If you use these Start Date and End Date with DATEDIFF:

``````SELECT DATEDIFF(day
, its.Start_Date
, End_Date = COALESCE(DATEADD(day, -1, itmax.End_Date), CASE WHEN itmin.Start_Date > its.End_Date THEN itmin.Start_Date ELSE its.End_Date END)
) + 1
...
``````

Output (with duplicates) is:

• 1, 4 and 2 for id 0 (your sample => `SUM=7`)
• 3, 2 and 5 for id 1 (Ypercube sample => `SUM=10`)

You then only need to put everything together with a `SUM` and `GROUP BY`:

``````SELECT id
, Days = SUM(
DATEDIFF(day, Start_Date, End_Date)+1
)
FROM (
SELECT DISTINCT its.id
, Start_Date = its.Start_Date
, End_Date = COALESCE(DATEADD(day, -1, itmax.End_Date), CASE WHEN itmin.Start_Date > its.End_Date THEN itmin.Start_Date ELSE its.End_Date END)
FROM @Items its
OUTER APPLY (
SELECT Start_Date = MAX(End_Date) FROM @Items std
WHERE std.Item_ID <> its.Item_ID AND std.Start_Date < its.Start_Date AND std.End_Date > its.Start_Date
) itmin
OUTER APPLY (
SELECT End_Date = MIN(Start_Date) FROM @Items std
WHERE std.Item_ID <> its.Item_ID AND std.Start_Date > its.Start_Date AND std.Start_Date < its.End_Date
) itmax
) as d
GROUP BY id;
``````

Output:

``````id  Days
0   7
1   10
``````

Data used with 2 different ids:

``````INSERT INTO @Items
(id, Item_ID, Start_Date, End_Date)
VALUES
(0, 20009, '2015-01-23', '2015-01-26'),
(0, 20010, '2015-01-24', '2015-01-24'),
(0, 20011, '2015-01-23', '2015-01-26'),
(0, 20012, '2015-01-23', '2015-01-27'),
(0, 20013, '2015-01-23', '2015-01-27'),
(0, 20014, '2015-01-29', '2015-01-30'),

(1, 20009, '2016-01-20', '2016-01-24'),
(1, 20010, '2016-01-23', '2016-01-26'),
(1, 20011, '2016-01-25', '2016-01-29')
``````

There are a lot of questions and articles about packing time intervals. For example, Packing Intervals by Itzik Ben-Gan.

You can pack your intervals for the given user. Once packed, there will be no overlaps, so you can simply sum up the durations of packed intervals.

If your intervals are dates without times, I'd use a `Calendar` table. This table simply has a list of dates for several decades. If you do not have a Calendar table, simply create one:

``````CREATE TABLE [dbo].[Calendar](
[dt] [date] NOT NULL,
CONSTRAINT [PK_Calendar] PRIMARY KEY CLUSTERED
(
[dt] ASC
));
``````

There are many ways to populate such a table.

For example, 100K rows (~270 years) from 1900-01-01:

``````INSERT INTO dbo.Calendar (dt)
SELECT TOP (100000)
DATEADD(day, ROW_NUMBER() OVER (ORDER BY s1.[object_id])-1, '19000101') AS dt
FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2
OPTION (MAXDOP 1);
``````

Once you have a `Calendar` table, here is how to use it.

Each original row is joined with the `Calendar` table to return as many rows as there are dates between `StartDate` and `EndDate`.

Then we count distinct dates, which removes overlapping dates.

``````SELECT COUNT(DISTINCT CA.dt) AS TotalCount
FROM
#Items AS T
CROSS APPLY
(
SELECT dbo.Calendar.dt
FROM dbo.Calendar
WHERE
dbo.Calendar.dt >= T.StartDate
AND dbo.Calendar.dt <= T.EndDate
) AS CA
WHERE T.CustID = 11205
;
``````

Result

``````TotalCount
7
``````

I strongly agree that a `Numbers` and a `Calendar` table are very useful and if this problem can be simplified a lot with a Calendar table.

I'll suggest another solution though (that doesn't need either a calendar table or windowed aggregates - as some of the answers from the linked post by Itzik do). It may not be the most efficient in all cases (or may be the worst in all cases!) but I don't think it harms to test.

It works by first finding start and end dates that do not overlap with other intervals, then puts them in two rows (separately the start and end dates) in order to assign them row numbers and finally matches the 1st start date with the 1st end date, the 2nd with the 2nd, etc.:

``````WITH
start_dates AS
( SELECT CustID, StartDate,
Rn = ROW_NUMBER() OVER (PARTITION BY CustID
ORDER BY StartDate)
FROM items AS i
WHERE NOT EXISTS
( SELECT *
FROM Items AS j
WHERE j.CustID = i.CustID
AND j.StartDate < i.StartDate AND i.StartDate <= j.EndDate
)
GROUP BY CustID, StartDate
),
end_dates AS
( SELECT CustID, EndDate,
Rn = ROW_NUMBER() OVER (PARTITION BY CustID
ORDER BY EndDate)
FROM items AS i
WHERE NOT EXISTS
( SELECT *
FROM Items AS j
WHERE j.CustID = i.CustID
AND j.StartDate <= i.EndDate AND i.EndDate < j.EndDate
)
GROUP BY CustID, EndDate
)
SELECT s.CustID,
Result = SUM( DATEDIFF(day, s.StartDate, e.EndDate) + 1 )
FROM start_dates AS s
JOIN end_dates AS e
ON  s.CustID = e.CustID
AND s.Rn = e.Rn
GROUP BY s.CustID ;
``````

Two indexes, on `(CustID, StartDate, EndDate)` and on `(CustID, EndDate, StartDate)` would be useful for improving performance of the query.

An advantage over the Calendar (perhaps the only one) is that it can easily adapted to work with `datetime` values and counting the length of the "packed intervals" in different precision, larger (weeks, years) or smaller (hours, minutes or seconds, milliseconds, etc) and not only counting dates. A Calendar table of minute or seconds precision would be quite big and (cross) joining it to a big table would be a quite interesting experience but possibly not the most efficient one.

(thanks to Vladimir Baranov): It is rather difficult to have a proper comparison of performance, because performance of different methods would likely depend on the data distribution. 1) how long are the intervals - the shorter the intervals, the better Calendar table would perform, because long intervals would produce a lot of intermediate rows 2) how often intervals overlap - mostly non-overlapping intervals vs. most intervals covering the same range. I think performance of Itzik's solution depends on that. There could be other ways to skew the data and it's hard to tell how efficiency of the various methods would be affected.

• I see 2 copies. Or maybe 3 if we count the anti-semijoins as 2 halfs ;) Feb 23, 2016 at 16:50
• lol no combat here @Monkpit. Perfectly valid reasons and a serious conversation about performance.
– wBob
Feb 23, 2016 at 17:02
• @wBob, it is rather difficult to have a proper comparison of performance, because performance of different methods would likely depend on the data distribution. 1) how long are the intervals - the shorter the intervals, the better Calendar table would perform, because long intervals would produce a lot of intermediate rows 2) how often intervals overlap - mostly non-overlapping intervals vs. most intervals covering the same range. I think performance of Itzik's solution depends on that. There could be other ways to skew the data, these are just few that come to mind. Feb 23, 2016 at 22:46

I think this would be straightforward with a calendar table, eg something like this:

``````SELECT i.CustID, COUNT( DISTINCT c.calendarDate ) days
FROM #Items i
INNER JOIN calendar.main c ON c.calendarDate Between i.StartDate And i.EndDate
GROUP BY i.CustID
``````

Test rig

``````USE tempdb
GO

-- Cutdown calendar script
IF OBJECT_ID('dbo.calendar') IS NULL
BEGIN

CREATE TABLE dbo.calendar (
calendarId      INT IDENTITY(1,1) NOT NULL,
calendarDate    DATE NOT NULL,

CONSTRAINT PK_calendar__main PRIMARY KEY ( calendarDate ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT UK_calendar__main UNIQUE NONCLUSTERED ( calendarId ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO

-- Populate calendar table once only
IF NOT EXISTS ( SELECT * FROM dbo.calendar )
BEGIN

-- Populate calendar table
WITH cte AS
(
SELECT 0 x
UNION ALL
SELECT x + 1
FROM cte
WHERE x < 11323 -- Do from year 1 Jan 2000 until 31 Dec 2030 (extend if required)
)
INSERT INTO dbo.calendar ( calendarDate )
SELECT
calendarDate
FROM
(
SELECT
DATEADD( day, x, '1 Jan 2010' ) calendarDate,
FROM cte
) x
WHERE calendarDate < '1 Jan 2031'
OPTION ( MAXRECURSION 0 )

ALTER INDEX ALL ON dbo.calendar REBUILD

END
GO

IF OBJECT_ID('tempdb..Items') IS NOT NULL DROP TABLE Items
GO

CREATE TABLE dbo.Items
(
CustID INT NOT NULL,
ItemID INT NOT NULL,
StartDate DATE NOT NULL,
EndDate DATE NOT NULL,

INDEX _cdx_Items CLUSTERED ( CustID, StartDate, EndDate )
)
GO

INSERT INTO Items ( CustID, ItemID, StartDate, EndDate )
SELECT 11205, 20009, '2015-01-23',  '2015-01-26'
UNION ALL
SELECT 11205, 20010, '2015-01-24',  '2015-01-24'
UNION ALL
SELECT 11205, 20011, '2015-01-23',  '2015-01-26'
UNION ALL
SELECT 11205, 20012, '2015-01-23',  '2015-01-27'
UNION ALL
SELECT 11205, 20012, '2015-01-23',  '2015-01-27'
UNION ALL
SELECT 11205, 20012, '2015-01-28',  '2015-01-29'
GO

-- Scale up : )
;WITH cte AS (
SELECT TOP 1000000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
CROSS JOIN master.sys.columns c2
CROSS JOIN master.sys.columns c3
)
INSERT INTO Items ( CustID, ItemID, StartDate, EndDate )
SELECT 11206 + rn % 999, 20012 + rn, DATEADD( day, rn % 333, '1 Jan 2015' ), DATEADD( day, ( rn % 333 ) + rn % 7, '1 Jan 2015' )
FROM cte
GO
--:exit

-- My query: Pros: simple, one copy of items, easy to understand and maintain.  Scales well to 1 million + rows.
-- Cons: requires calendar table.  Others?
SELECT i.CustID, COUNT( DISTINCT c.calendarDate ) days
FROM dbo.Items i
INNER JOIN dbo.calendar c ON c.calendarDate Between i.StartDate And i.EndDate
GROUP BY i.CustID
--ORDER BY i.CustID
GO

-- Vladimir query: Pros: Effectively same as above
-- Cons: I wouldn't use CROSS APPLY where it's not necessary.  Fortunately optimizer simplifies avoiding RBAR (I think).
-- Point of style maybe, but in terms of queries being self-documenting I prefer number 1.
SELECT T.CustID, COUNT( DISTINCT CA.calendarDate ) AS TotalCount
FROM
Items AS T
CROSS APPLY
(
SELECT c.calendarDate
FROM dbo.calendar c
WHERE
c.calendarDate >= T.StartDate
AND c.calendarDate <= T.EndDate
) AS CA
GROUP BY T.CustID
--ORDER BY T.CustID
--WHERE T.CustID = 11205
GO

/*  WARNING!! This is commented out as it can't compete in the scale test.  Will finish at scale 100, 1,000, 10,000, eventually.  I got 38 mins for 10,0000.  Pegs CPU.

-- Julian:  Pros; does not require calendar table.
-- Cons: over-complicated (eg versus Query 1 in terms of number of lines of code, clauses etc); three copies of dbo.Items table (we have already shown
-- this query is possible with one); does not scale (even at 100,000 rows query ran for 38 minutes on my test rig versus sub-second for first two queries).  <<-- this is serious.
-- Indexing could help.
SELECT DISTINCT
CustID,
StartDate = CASE WHEN itmin.StartDate < its.StartDate THEN itmin.StartDate ELSE its.StartDate END
, EndDate = CASE WHEN itmax.EndDate > its.EndDate THEN itmax.EndDate ELSE its.EndDate END
FROM Items its
OUTER APPLY (
SELECT StartDate = MIN(StartDate) FROM Items std
WHERE std.ItemID <> its.ItemID AND (
(std.StartDate <= its.StartDate AND std.EndDate >= its.StartDate)
OR (std.StartDate >= its.StartDate AND std.StartDate <= its.EndDate)
)
) itmin
OUTER APPLY (
SELECT EndDate = MAX(EndDate) FROM Items std
WHERE std.ItemID <> its.ItemID AND (
(std.EndDate >= its.StartDate AND std.EndDate <= its.EndDate)
OR (std.StartDate <= its.EndDate AND std.EndDate >= its.EndDate)
)
) itmax
GO
*/

-- ypercube:  Pros; does not require calendar table.
-- Cons: over-complicated (eg versus Query 1 in terms of number of lines of code, clauses etc); four copies of dbo.Items table (we have already shown
-- this query is possible with one); does not scale well; at 1,000,000 rows query ran for 2:20 minutes on my test rig versus sub-second for first two queries.
WITH
start_dates AS
( SELECT CustID, StartDate,
Rn = ROW_NUMBER() OVER (PARTITION BY CustID
ORDER BY StartDate)
FROM items AS i
WHERE NOT EXISTS
( SELECT *
FROM Items AS j
WHERE j.CustID = i.CustID
AND j.StartDate < i.StartDate AND i.StartDate <= j.EndDate
)
GROUP BY CustID, StartDate
),
end_dates AS
( SELECT CustID, EndDate,
Rn = ROW_NUMBER() OVER (PARTITION BY CustID
ORDER BY EndDate)
FROM items AS i
WHERE NOT EXISTS
( SELECT *
FROM Items AS j
WHERE j.CustID = i.CustID
AND j.StartDate <= i.EndDate AND i.EndDate < j.EndDate
)
GROUP BY CustID, EndDate
)
SELECT s.CustID,
Result = SUM( DATEDIFF(day, s.StartDate, e.EndDate) + 1 )
FROM start_dates AS s
JOIN end_dates AS e
ON  s.CustID = e.CustID
AND s.Rn = e.Rn
GROUP BY s.CustID ;
``````