Setting Beginning Dates for Each Episode

[Khanson2]

Out of a data set of one patient, I'm trying to locate the first date and set it as a value 1 in a new colum(episodestart)
Then I want to go through each row in order and figure out if it's within 30 days of the previous episodestart column record that was a 1 and mark it with a 0.. If it is over 30 days it will need to be marked as a 1 and the process starts over again.
Example below should help. Any ideas?

So I have a data set that looks like this:
id Pid did ddate cdate
99999 52388 15230 2012-05-13 2012-05-15
99999 52388 16609 2012-05-19 2012-05-21
99999 52388 23890 2012-06-19 2012-06-21
99999 52388 24940 2012-06-24 2012-06-26
99999 52388 38048 2012-08-20 2012-08-22
99999 52388 39755 2012-08-28 2012-08-30
99999 52388 42062 2012-09-07 2012-09-09
99999 52388 48094 2012-10-02 2012-10-04
99999 52388 48917 2012-10-06 2012-10-08

My desired results would look like thisadded episodestart colum)
id Pid did ddate cdate EpisodeStart
99999 52388 15230 2012-05-13 2012-05-15 1
99999 52388 16609 2012-05-19 2012-05-21 0
99999 52388 23890 2012-06-19 2012-06-21 1
99999 52388 24940 2012-06-24 2012-06-26 0
99999 52388 38048 2012-08-20 2012-08-22 1
99999 52388 39755 2012-08-28 2012-08-30 0
99999 52388 42062 2012-09-07 2012-09-09 0
99999 52388 48094 2012-10-02 2012-10-04 1
99999 52388 48917 2012-10-06 2012-10-08 0

 

[Khanson2]

Using sqlserver2008R2

 

[gmmastros]

Nicely worded question. However... I think your sample data is wrong, or else I don't understand the question.

[tt]
99999 52388 15230 2012-05-13 2012-05-15 1
99999 52388 16609 2012-05-19 2012-05-21 0
99999 52388 23890 2012-06-19 2012-06-21 1
99999 52388 24940 2012-06-24 2012-06-26 0
99999 52388 38048 2012-08-20 2012-08-22 1
99999 52388 39755 2012-08-28 2012-08-30 0
99999 52388 42062 [!]2012-09-07[/!] 2012-09-09 0
99999 52388 48094 [!]2012-10-02[/!] 2012-10-04 [!]1[/!]
99999 52388 48917 2012-10-06 2012-10-08 0
[/tt]

There are only 25 days separating sept. 7 and October 2, so I think the 1 should be a zero. Is this a simple mistake in the sample data, or am I not understanding something?



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Khanson2]

The 10-2-2012 record would be looking for the last episodestart of 1 to do its calculations which would be 8-20-2012. That is why it is a 1.

 

[gmmastros]

Ah... gotcha. Thanks for clarifying.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Khanson2]

George:
Did you have any breakthrough with this idea I posed? I'm still treading water on the idea..

 

[DaveInIowa]

There may be a way to accomplish this without a cursor based solution but I can't figure it out. Will this help?

DECLARE @Id INT, @DDate DATE
DECLARE @IdPrevious INT = -1, @EpisodeExpiresOn DATE
DECLARE @EpisodeStartRows TABLE ( Id INT, DDate DATE )

DECLARE csr CURSOR FAST_FORWARD FOR 
SELECT Id, DDate
  FROM YourTable
 ORDER BY Id, DDate

OPEN csr 
FETCH NEXT FROM csr INTO @Id, @DDate
WHILE @@FETCH_STATUS = 0 BEGIN
	IF (@Id > @IdPrevious OR @DDate > @EpisodeExpiresOn) BEGIN
		INSERT INTO @EpisodeStartRows (Id, DDate) VALUES(@Id, @DDate)
		SET @IdPrevious = @Id
		SET @EpisodeExpiresOn = DATEADD(DAY, 30, @DDate)
	END
	FETCH NEXT FROM csr INTO @Id, @DDate
END

CLOSE csr 
DEALLOCATE csr

SELECT yt.*, CASE WHEN esr.Id IS NULL THEN 0 ELSE 1 END As EpisodeStart
  FROM YourTable yt
  LEFT
  JOIN @EpisodeStartRows esr
    ON yt.Id = esr.Id AND yt.DDate = esr.DDate

 

[Khanson2]

I'll check this out today and update..Thanks so much!

 

[Khanson2]

Dave:
The code is working great.. Thanks again..Now onto my next complex step using this new data.

Thanks!

 

[Khanson2]

Another update on this beast...

So now that I have the desired table from the cursor, i am needing to join on an appointments table and tie the appointments to episodestart records with a "1" but with a few clauses and a count. Here we go...

So for one patient I have this.
ID2 PID PDID DDATE EpisodeStart
10788 97572 15230 5/13/2012 1
10789 97572 16609 5/19/2012 0
10790 97572 23890 6/19/2012 1
10791 97572 24940 6/24/2012 0
10792 97572 38048 8/20/2012 1
10793 97572 39755 8/28/2012 0
10794 97572 42062 9/7/2012 0
10795 97572 48094 10/2/2012 1
10796 97572 48917 10/6/2012 0
10797 97572 50272 10/12/2012 0
[highlight #FCE94F]10798 97572 57158 11/12/2012 1
10799 97572 62290 12/5/2012 0
10800 97572 63847 12/12/2012 0[/highlight]
10801 97572 66774 12/24/2012 1
10802 97572 69133 1/2/2013 0
10803 97572 71567 1/12/2013 0
10804 97572 72033 1/14/2013 0
10805 97572 76852 2/6/2013 1
10806 97572 78018 2/12/2013 0
10807 97572 79542 2/19/2013 0

And they have these appointments:

PID BookingDate
97572 6/5/2012
97572 6/28/2012
97572 6/28/2012
97572 9/15/2012
[highlight #FCE94F]97572 12/19/2012[/highlight]
97572 12/26/2012
97572 1/29/2013
97572 1/29/2013
97572 3/5/2013
97572 3/5/2013
97572 3/5/2013
97572 3/12/2013
97572 3/12/2013

I need each appointment if meets criteria to only tie to one episodestart that equals "1" record. The bookingdate needs to be greater than the DDate and within 30 days of the DDate. The problem I'm getting is for instance, the record in the appts table with a booking date of 12/19/2012. It is within 30 days of a record in the main table with DDates of 12/5/2012 and 12/12/2005. I need this appointment to count as an appointment coming from the 11/12/2012 DDate with an Episodestart of "1" since that is the last episode "1" relating to those records with an episodestart of "0" that matches this appointment criteria. I highlighted the section Im referring to. It happens in other spots also, but this is one example..
Others are booking dates of 1/29/2013 and 3/12/2013.

 

[TamarGranor]

I'm not sure what you want in the result, so I'm not including the field list, but something like this.

SELECT <fields you want>
    FROM Appts
      JOIN Main 
    WHERE EpisodeStart = 1 
      AND Appts.BookingDate BETWEEN DateAdd(day, Main.DDate, -30) and Main.DDate

Tamar

 

[Khanson2]

Tamar:
That won't work for this because the bookingdate needs to be within 30 days of a ddate, but then it needs to count it as an appointment only associated with the previous record with an episodestart of 1..

So we have a patient with many discharges and we set an episode start of 1 only on certain ones..Then we are trying to track appts after a discharge and associate them to a discharge with an episode 1 record. It's super hard to explain but my example is very thorough and you will notice that with the solution you gave it wouldn't work for the highlighted ones since it's over 30 days of the episodestart record of 1 but it is within 30 days of some of the other records so it needs to tie back to the previous episode 1 record.

Thanks,

 

[Khanson2]

One last bump on this thread to see if anyone can figure out the last issue.. Maybe DaveInIowa?

Thanks again,

 

[DaveInIowa]

If you defer inserting the episode start rows as shown below you can calculate and include the maximum booking date.

DECLARE @Id INT, @DDate DATE
DECLARE @EpisodeStartId INT, @EpisodeStartsOn DATE, @EpisodeExpiresOn DATE, @CoversBookingsUntil DATE
DECLARE @EpisodeStartRows TABLE ( EpisodeStartId INT, EpisodeStartsOn DATE, CoversBookingsUntil DATE )

DECLARE @IsNewEpisode BIT = 1

DECLARE csr CURSOR FAST_FORWARD FOR 
SELECT Id, DDate
  FROM YourTable
 ORDER BY Id, DDate

OPEN csr 
FETCH NEXT FROM csr INTO @Id, @DDate
WHILE @@FETCH_STATUS = 0 BEGIN

	IF (@IsNewEpisode = 1) BEGIN
		SET @EpisodeStartId = @Id
		SET @EpisodeStartsOn = @DDate
		SET @EpisodeExpiresOn = DATEADD(DAY, 30, @EpisodeStartsOn)
		SET @IsNewEpisode = 0
	END
	
	SET @CoversBookingsUntil = @DDate
	
	FETCH NEXT FROM csr INTO @Id, @DDate
	
	IF (@@FETCH_STATUS <> 0 OR @Id <> @EpisodeStartId OR @DDate > @EpisodeExpiresOn) SET @IsNewEpisode = 1
	IF (@IsNewEpisode = 1) BEGIN
		SET @CoversBookingsUntil = DATEADD(DAY, 30, @CoversBookingsUntil)
		
		-- Don't allow this rows coverage to overlap the next episode
		IF (@@FETCH_STATUS = 0 AND @CoversBookingsUntil >= @DDate) SET @CoversBookingsUntil = DATEADD(DAY, -1, @DDate)
		
		INSERT INTO @EpisodeStartRows VALUES(@EpisodeStartId, @EpisodeStartsOn, @CoversBookingsUntil)
	END
	
END

CLOSE csr 
DEALLOCATE csr

SELECT yt.*, esr.CoversBookingsUntil, CASE WHEN esr.EpisodeStartId IS NULL THEN 0 ELSE 1 END As EpisodeStart
  FROM YourTable yt
  LEFT
  JOIN @EpisodeStartRows esr
    ON yt.Id = esr.EpisodeStartId AND yt.DDate = esr.EpisodeStartsOn

Then you should be able to use Tamar's technique to join your bookings.

...
AND Appts.BookingDate BETWEEN Main.DDate AND Main.CoversBookingsUntil

 

[Khanson2]

So your script did exactly what it should, which is amazing! but I found one more hurdle which is my fault. I've only been showing a subset of the data. If you notice the coversboookinguntil is incorrect on the last entry for episodestart of 1. It is probably using the ddate from the next patient in the full table.How can I modify this cursor to account for a change in PID. I would want the CoversBookingsUntil to default to 30 days out of the ddate if there is not another episodestart 1 record for that PID. So in this case since the ddate record of 2/6/2013 would be recognized as the last episodestart 1 for this patient it would set the CoversBookingsUntil to 30 days later and the last two records would still show null for the CoversBookingsUntil.


ID PID Ddate EpisodeStart CoversBookingsUntil
10780 97572 5/13/2012 1 6/18/2012
10781 97572 5/19/2012 0 NULL
10782 97572 6/19/2012 1 7/24/2012
10783 97572 6/24/2012 0 NULL
10784 97572 8/20/2012 1 10/1/2012
10785 97572 8/28/2012 0 NULL
10786 97572 9/7/2012 0 NULL
10787 97572 10/2/2012 1 11/11/2012
10788 97572 10/6/2012 0 NULL
10789 97572 10/12/2012 0 NULL
10790 97572 11/12/2012 1 12/23/2012
10791 97572 12/5/2012 0 NULL
10792 97572 12/12/2012 0 NULL
10793 97572 12/24/2012 1 2/5/2013
10794 97572 1/2/2013 0 NULL
10795 97572 1/12/2013 0 NULL
10796 97572 1/14/2013 0 NULL
[highlight #FCE94F]10797 97572 2/6/2013 1 11/5/2012[/highlight]
10798 97572 2/12/2013 0 NULL
10799 97572 2/19/2013 0 NULL

 

[DaveInIowa]

You would modify the condition where @CoversBookingsUntil is adjusted.

 

[Khanson2]

Dave I got it to work..First time with cursors so took some time but I really appreciate the help..

Here is the snippet I updated to get everything working good.First highlight is a bug I found in some of the @CoversBookingsUntil calculating.Other highlights are the fix for different patients.

IF (@@FETCH_STATUS <> 0 OR @Id <> @EpisodeStartId OR @DDate > @EpisodeExpiresOn) SET @IsNewEpisode = 1
IF (@IsNewEpisode = 1) BEGIN
[highlight #FCE94F]SET @CoversBookingsUntil = DATEADD(DAY, 365, @CoversBookingsUntil)[/highlight]
-- Don't allow this rows coverage to overlap the next episode
[highlight #FCE94F]IF (@@FETCH_STATUS = 0 AND @Id = @EpisodeStartId AND @CoversBookingsUntil >= @DDate) SET @CoversBookingsUntil = DATEADD(DAY, -1, @DDate)
IF (@@FETCH_STATUS = 0 AND @Id <> @EpisodeStartId) SET @CoversBookingsUntil = DATEADD(DAY, 30, @EpisodeStartsOn)[/highlight]

 

[Khanson2]

So I ran into a new issue with this script where when I have patients discharged multiple times a day it isn't taking it into consideration since it's keying off ID and DDate and those will match in these instances.
Here is latest edition of cursor:
==============================================================================================================================================================================
DECLARE @Id INT,@DDate DATE
DECLARE @EpisodeStartId INT, @EpisodeStartsOn DATE, @EpisodeExpiresOn DATE, @CoversBookingsUntil DATE
DECLARE @EpisodeStartRows TABLE ( EpisodeStartId INT, EpisodeStartsOn DATE, CoversBookingsUntil DATE )

DECLARE @IsNewEpisode BIT = 1

DECLARE csr CURSOR FAST_FORWARD FOR
SELECT distinct ID, DDate
FROM #discharges3
ORDER BY ID,DDate

OPEN csr
FETCH NEXT FROM csr INTO @Id,@DDate
WHILE @@FETCH_STATUS = 0 BEGIN

IF (@IsNewEpisode = 1) BEGIN
SET @EpisodeStartId = @Id
SET @EpisodeStartsOn = @DDate
SET @EpisodeExpiresOn = DATEADD(DAY, 30, @EpisodeStartsOn)
SET @IsNewEpisode = 0
END

SET @CoversBookingsUntil = @DDate

FETCH NEXT FROM csr INTO @Id,@DDate

IF (@@FETCH_STATUS <> 0 OR @Id <> @EpisodeStartId OR @DDate > @EpisodeExpiresOn) SET @IsNewEpisode = 1


IF (@IsNewEpisode = 1) BEGIN
SET @CoversBookingsUntil = DATEADD(DAY, 365, @CoversBookingsUntil)

-- Don't allow this rows coverage to overlap the next episode
IF (@@FETCH_STATUS = 0 AND @Id = @EpisodeStartId AND @CoversBookingsUntil >= @DDate) SET @CoversBookingsUntil = DATEADD(DAY, -1, @DDate)
IF (@@FETCH_STATUS = 0 AND @Id <> @EpisodeStartId) SET @CoversBookingsUntil = DATEADD(DAY, 30, @EpisodeStartsOn)
IF (@@FETCH_STATUS = 0 AND @Id = @EpisodeStartId) SET @CoversBookingsUntil = DATEADD(DAY, 30, @EpisodeStartsOn)

INSERT INTO @EpisodeStartRows VALUES(@EpisodeStartId, @EpisodeStartsOn, @CoversBookingsUntil)
END

END

CLOSE csr
DEALLOCATE csr

SELECT distinct yt.*, esr.CoversBookingsUntil, CASE WHEN esr.EpisodeStartId IS NULL THEN 0 ELSE 1 END As EpisodeStart
into #Patientsfinal2
FROM #discharges3 yt
LEFT JOIN @EpisodeStartRows esr
ON yt.ID= esr.EpisodeStartId AND yt.DDate = esr.EpisodeStartsOn

======================================================================================================================================================================

Here is my output for one example:
	ID	PDID	DDate	         calldate	  CoversBookingsUntil	 EpisodeStart
	2115928	16735	4/18/2013	  4/19/2013   	  5/18/2013	                 1
	2115928	16777	4/18/2013   	  4/19/2013  	  5/18/2013	                 1

You'll notice episodestart is 1 for both. It should only be 1 for the first record, since its the same patient and same dischargedate. I added in the PDID field so we could see if it helps in the cursor. It is a unique # for every discharge.


Any ideas how to update just the episodestart logic of the cursor to help with this issue?