TREATMENT GAPS OF 2 OR MORE DAYS USING SERVICE DATES

Question

Post reply

TREATMENT GAPS OF 2 OR MORE DAYS USING SERVICE DATES

Mick Lovell

Old Hand

Points: 345
More actions
November 4, 2015 at 2:40 pm

#302011

Hi Folks,
*SQL_Server 2008*
I thought this would be fairly simple but I've researched this and cannot find anything to works, from LEAD and LAG to SELF JOINS. I'm missing something.
For a large data set, I am trying to find treatment gaps where there are 2 or more consecutive service dates (episodes) and a gap of 3 days or more between the episodes. So:
Episode = 2 or more consecutive days
Gap = 3 or more consecutive days
New Episode = 2 or more consecutive days
DATA: NAME/SERVICE DATE
JOHN DOE20150423
JOHN DOE20150424
JOHN DOE20150425
JOHN DOE20150426<- 1 EPISODE
JOHN DOE20150503<- 1 EPISODE
JOHN DOE20150510<- 1 EPISODE
JOHN DOE20150616
JOHN DOE20150617
JOHN DOE20150618<- 1 EPISODE
JOHN DOE20150814<- 1 EPISODE
JOHN DOE20150908
JOHN DOE20150909
JOHN DOE20150910
JOHN DOE20150911
JOHN DOE20150912<- 1 EPISODE
Any help is greatly appreciated.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

DROP TABLE #MyBigTable

SELECT * INTO #MyBigTable

FROM (VALUES

('JOHN DOE',CAST('20150423' AS DATE),''),

('JOHN DOE','20150424',''),

('JOHN DOE','20150425',''),

('JOHN DOE','20150426','<- 1 EPISODE'),

('JOHN DOE','20150503','<- 1 EPISODE'),

('JOHN DOE','20150510','<- 1 EPISODE'),

('JOHN DOE','20150616',''),

('JOHN DOE','20150617',''),

('JOHN DOE','20150618','<- 1 EPISODE'),

('JOHN DOE','20150814','<- 1 EPISODE'),

('JOHN DOE','20150908',''),

('JOHN DOE','20150909',''),

('JOHN DOE','20150910',''),

('JOHN DOE','20150911',''),

('JOHN DOE','20150912','<- 1 EPISODE')) d (Name, ServiceDate, Comment)

-- This isn't part of the solution, it shows you how the date aggregation works.

-- Comment it out when it's clear what's happening with rn and consecutive dates.

SELECT Name, ServiceDate, Comment,

rn = 1-ROW_NUMBER() OVER(PARTITION BY Name ORDER BY ServiceDate),

DateGroup = DATEADD(DAY,1-ROW_NUMBER() OVER(PARTITION BY Name ORDER BY ServiceDate),ServiceDate)

FROM #MyBigTable

-- This is most of the solution - it aggregates consecutive visits as groups with a start and end date

-- and discards single visits.

-- Now you can use LAG or LEAD to determine the gaps between the episodes.

-- Probably best to run the result into a #temp table for the final step.

;WITH GroupedData AS (

SELECT Name, ServiceDate, Comment,

DateGroup = DATEADD(DAY,1-ROW_NUMBER() OVER(PARTITION BY Name ORDER BY ServiceDate),ServiceDate)

FROM #MyBigTable

)

SELECT

Name,

ServiceStart = MIN(ServiceDate),

ServiceEnd = MAX(ServiceDate)

FROM GroupedData

GROUP BY Name, DateGroup

HAVING COUNT(*) >= 2

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Mick Lovell Old Hand Points: 345 More actions · Answer 2

Thanks, Chris! I will begin understanding this right now!

Mick

Mick Lovell Old Hand Points: 345 More actions · Answer 3

This has worked to pull 2 or more service dates MOST of the time. In validating, I'm seeing some bizarre behavior that I'm not able to figure out:

This is the data in the service table I'm using:

Name ServiceDate

Jane Doe 1/1/2015 0:00

Jane Doe 1/4/2015 0:00

Jane Doe 1/5/2015 0:00

Jane Doe 1/6/2015 0:00

Jane Doe 1/7/2015 0:00

Jane Doe 1/8/2015 0:00

Jane Doe 1/9/2015 0:00

This is what the above code is picking up (some of the time):

Name ServiceStart ServiceEnd

Jane Doe1/1/2015 0:001/9/2015 0:00

Jane Doe1/6/2015 0:001/8/2015 0:00

Jane Doe1/4/2015 0:001/6/2015 0:00

For some reason, that I can't figure out, it is breaking up 1/1/2015 into 3 segments. I would expect not to see 1/1/2015 (because it is not part of two consecutive records) and would also expect to see 1/4/2015 - 1/9/2015 as one episode.

Any thoughts?

Thanks!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Do any of the dates have times?

Do you have multiple visits on the same day?

Is there another column which would distinguish between two people with the same name?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Mick Lovell Old Hand Points: 345 More actions · Answer 5

1. The field I'm using for service date is a datetime. They all appear to have '12:00:00 AM' as the TIME for each service date.

2. I am only pulling in the member NUMBER and service date, so multiple services per day shouldn't be an issue. ????

3. In the examples I'm posting, I'm using a NAME but my data is actual an alph-numeric member NUMBER that is unique.

Could it be something with my grouping? (I will play around with this.)

(It runs so clean and accurately, except for this problem scenario! Thank you! I'm more of a Crystal Reports users so I'm learning here.)

I will continue to research the original data for issues!

Thanks!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Mick Lovell (11/5/2015)
1. The field I'm using for service date is a datetime. They all appear to have '12:00:00 AM' as the TIME for each service date.
2. I am only pulling in the member NUMBER and service date, so multiple services per day shouldn't be an issue. ????
3. In the examples I'm posting, I'm using a NAME but my data is actual an alph-numeric member NUMBER that is unique.
Could it be something with my grouping? (I will play around with this.)
(It runs so clean and accurately, except for this problem scenario! Thank you! I'm more of a Crystal Reports users so I'm learning here.)
I will continue to research the original data for issues!
Thanks!

Can you reproduce the effect using sample data? You could use the script I originally posted and just slip in a bunch of extra rows.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Mick Lovell Old Hand Points: 345 More actions · Answer 7

ChrisM@Work (11/5/2015)
Mick Lovell (11/5/2015)
1. The field I'm using for service date is a datetime. They all appear to have '12:00:00 AM' as the TIME for each service date.
2. I am only pulling in the member NUMBER and service date, so multiple services per day shouldn't be an issue. ????
3. In the examples I'm posting, I'm using a NAME but my data is actual an alph-numeric member NUMBER that is unique.
Could it be something with my grouping? (I will play around with this.)
(It runs so clean and accurately, except for this problem scenario! Thank you! I'm more of a Crystal Reports users so I'm learning here.)
I will continue to research the original data for issues!
Thanks!
Can you reproduce the effect using sample data? You could use the script I originally posted and just slip in a bunch of extra rows.

I don't know if I can reproduce by adding to the sample data because the issue appears to be some less-than-clean data on my end. What I did was create a subset of the larger table and that seems to have cleaned things up enough for your solution to work for me.

Thanks again, Chris!