Help with Looping query

[TyeReece]

I have a table of data that holds admission and discharge dates to multiple residences for multiple individuals. Looks like this:

Unique ID Person ID Program ID Admit Date Discharge date
1 001 9999 1/1/2017 6/1/2017
2 001 5656 6/1/2017 6/25/2017
3 001 9999 6/25/2017 (NULL)
4 002 1212 1/1/2017 6/1/2017
5 002 4444 6/1/2017 7/25/2017
6 002 1212 7/25/2017 (NULL)

Of course there can be multiple records per person per program. What I need to return is the earliest admit date for a person that is currently in a program that does not have a break of more than 30 days. So, in the simple example above I would return the admit date of "1/1/2017" for person 001 in program 9999 and admit date "7/25/2017" for person 002 in program 1212. Any help is appreciated.

 

[Micron]

The idea is for the query to provide that

 

[TyeReece]

Replying to more than one post here. I just queried the number of records that don't have a contiguous date. 4 out of 40,000 ish had non contiguous dates which have been corrected. Of course, that doesn't mean there won't be errors in the future. I will contact our programmer to see if he has any ideas how to do this. It also occurs to me that I may not need to look at a child's entire history if I just grab the previous placements that are the same as the current program the child is in. This would result in non contiguous dates but couldn't a loop check the current admit date to the previous dc date and if it is less than 30 continue to look backwards in time to that admit and the next dc? Stopping when you get to one longer than 30 days? I can do this with several steps in excel but it is time consuming and cumbersome. Thanks to all the feedback.

 

[Micron]

Perhaps I am overlooking something due to the limited data you provided but I don't understand what the issue is. If you review what I posted regarding the grouping and sorting of the records, the re-admission of a client can only come after the prior discharge thereby producing contiguous records grouped by program then client. This should result in a useable progression of records. If a discharge date is missing the reason ought to be 1 of 2 possibilities - they are still in a program or data integrity is a bit lacking.

 

[xenou]

One try as an example is below.

You will need to create two tables:
X1000 (ID Autonumber, PersonID Number, ProgramID number, AdmitDate DateTime, DischargeDate Datetime, CountOfDays Number)
TableZ (ID Autonumber, PersonID Number, ProgramID number, AdmitDate DateTime, DischargeDate Datetime)

The first table will be used to store results (you could change that later if you don't like it)
The second table is your data.

Then run these three queries:

Delete X1000.* From X1000

Insert Into X1000 (PersonID, ProgramID, AdmitDate, DischargeDate, CountOfDays)
select 
	t2.PersonID, 
	t2.ProgramID, 
	t2.AdmitDate, 
	t2.DischargeDate, 
	(t2.DischargeDate - t2.AdmitDate) as CountOfDays
from TableZ t1
inner join TableZ t2
on t1.PersonID = t2.PersonID
and t1.AdmitDate = t2.DischargeDate

Insert Into X1000 (PersonID, ProgramID, AdmitDate, DischargeDate, CountOfDays)
select
	t1.PersonID, 
	t1.ProgramID, 
	t1.AdmitDate, 
	t1.DischargeDate, 
	(Date() - t1.AdmitDate) as CountOfDays
from 
	TableZ t1
Where
	t1.DischargeDate is null

Initial Data (table TableZ):
---------------------------------------------------------
| ID | PersonID | ProgramID | AdmitDate | DischargeDate |
---------------------------------------------------------
|  1 |        1 |      9999 |  1/1/2017 |      6/1/2017 |
|  2 |        1 |      5656 |  6/1/2017 |     6/25/2017 |
|  3 |        1 |      9999 | 6/25/2017 |               |
|  4 |        2 |      1212 |  1/1/2017 |      6/1/2017 |
|  5 |        2 |      4444 |  6/1/2017 |     7/25/2017 |
|  6 |        2 |      1212 | 7/25/2017 |               |
---------------------------------------------------------

Results (table X1000):
------------------------------------------------------------------
| PersonID | ProgramID | AdmitDate | DischargeDate | CountOfDays |
------------------------------------------------------------------
|        1 |      9999 | 25-Jun-17 |               |         270 |
|        1 |      5656 | 01-Jun-17 | 25-Jun-17     |          24 |
|        1 |      9999 | 01-Jan-17 | 01-Jun-17     |         151 |
|        2 |      1212 | 25-Jul-17 |               |         240 |
|        2 |      4444 | 01-Jun-17 | 25-Jul-17     |          54 |
|        2 |      1212 | 01-Jan-17 | 01-Jun-17     |         151 |
------------------------------------------------------------------

The third query counts the days in the current program using today's date as the end point.

I haven't tried to do a lot here. It simply builds the chain of admits, discharges. For instance, if you wanted to get the sum of days you can simply sum from table X1000 now. You can exclude the cases with less than 30 days by simply adding a where clause filter (where CountOfDays >= 30). You would also exclude the current program by filtering out records where dischargeDate is null.