Determine Number of Visits and Length of Stay by Client

Question

I have a set of Client Records which only have ClientID and ServiceDate

I want to determine the Number of Visits and the Length of Stay of each Visit

I have Created some code in MS Access and Read Several of the similar questions recommended in the search of this question and other suggestions returned from Google searches

Current Code:

SELECT 
  a.Client, 
  a.ServiceDate, 
  DateAdd ( "d", -1, a.ServiceDate ) AS [Prior], 
  DateAdd ( "d", 1, a.ServiceDate ) AS [Next], ( SELECT COUNT(*)
FROM Services_Table b
WHERE a.Client = b.Client 
      AND DateAdd ( "d", -1, a.ServiceDate ) = b.ServiceDate ) AS [Index]
FROM Services_Table AS a
ORDER BY a.Client, a.ServiceDate;

Input Data:

Client  ServiceDate
1   8/4/2018
1   8/5/2018
1   8/6/2018
6   7/11/2018
6   7/12/2018
6   7/17/2018
6   7/18/2018
6   7/19/2018
6   7/30/2018
6   7/31/2018
6   8/1/2018
6   8/2/2018
15  1/13/2019
15  1/14/2019
5   9/13/2018
5   9/14/2018
5   9/15/2018

Current Output:

Clt ServiceDate Prior           Next          Index
1   8/4/2018    8/3/2018    8/5/2018    0
1   8/5/2018    8/4/2018    8/6/2018    1
1   8/6/2018    8/5/2018    8/7/2018    1
5   9/13/2018   9/12/2018   9/14/2018   0
5   9/14/2018   9/13/2018   9/15/2018   1
5   9/15/2018   9/14/2018   9/16/2018   1
6   7/11/2018   7/10/2018   7/12/2018   0
6   7/12/2018   7/11/2018   7/13/2018   1
6   7/17/2018   7/16/2018   7/18/2018   0
6   7/18/2018   7/17/2018   7/19/2018   1
6   7/19/2018   7/18/2018   7/20/2018   1
6   7/30/2018   7/29/2018   7/31/2018   0
6   7/31/2018   7/30/2018   8/1/2018    1
6   8/1/2018    7/31/2018   8/2/2018    1
6   8/2/2018    8/1/2018    8/3/2018    1
15  1/13/2019   1/12/2019   1/14/2019   0
15  1/14/2019   1/13/2019   1/15/2019   1

Desired Result:

Clt Start           End     Length
1   8/4/2018    8/6/2018      3
5   9/13/2018   9/15/2018     3
6   7/11/2018   7/12/2018     2
6   7/17/2018   7/19/2018     3
6   7/30/2018   8/2/2018      4
15  1/13/2019   1/14/2019     2

Answer 1

Let's start by identifying the starts:

A start is a date where there's no consecutive previous date, so:

SELECT Client, ServiceDate
FROM Services_Table t
WHERE NOT EXISTS(SELECT 1 FROM Services_Table s WHERE s.Client = t.Client AND s.ServiceDate = DateAdd("d", -1, t.ServiceDate))

Similarly, we can identify the ends:

SELECT Client, ServiceDate
FROM Services_Table t
WHERE NOT EXISTS(SELECT 1 FROM Services_Table s WHERE s.Client = t.Client AND s.ServiceDate = DateAdd("d", 1, t.ServiceDate))

Then, we can join the starts and the ends together, and select the end date that's closest to the start:

SELECT Start.Client, Start.ServiceDate As Start, Min(End.ServiceDate) As End, Min(End.ServiceDate) - Start.ServiceDate As Length
FROM
(
    SELECT Client, ServiceDate
    FROM Services_Table t
    WHERE NOT EXISTS(SELECT 1 FROM Services_Table s WHERE s.Client = t.Client AND s.ServiceDate = DateAdd("d", -1, t.ServiceDate))
) Start
INNER JOIN (
    SELECT Client, ServiceDate
    FROM Services_Table t
    WHERE NOT EXISTS(SELECT 1 FROM Services_Table s WHERE s.Client = t.Client AND s.ServiceDate = DateAdd("d", 1, t.ServiceDate))
) End ON (Start.Client = End.Client AND Start.ServiceDate <= End.ServiceDate)
GROUP BY Start.Client, Start.ServiceDate