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.

 

[xenou]

Hi,
how do you define "a program that does not have a break of more than 30 days."?

 

[TyeReece]

In the example there are two different people. 001 and 002. It is hard to view from the original post. Person 001 is in program 9999 from 1/1/2017 to 6/1/2017, then program 5656 from 6/1/2017 to 6/25/2017 then returns to program 9999 on 6/25/17. The difference between the date of admit of record 3 and the date of discharge from record 1 is how I would define it.

 

[xenou]

Sorry that's not clear enough to me. I can't proceed. Which program has the break and why is it more than 30 days here?

 

[TyeReece]

No apology needed. I am not very good at explaining. The real life situation is that I have a table "Placements" that stores every out of home placement for a child who is removed from home. There are thousands of children with multiple placements with multiple programs. So one child could be in program A, then move to program B, then move back to program A (and just about any combination you can imagine). For example, child 001 could have the following placements:

Program A ADMIT 1/1/2018 DISCHARGE 2/2/2018
Program B ADMIT 2/2/2018 DISCHARGE 2/15/2018
Program A ADMIT 2/15/2018 DISCHARGE "Null" (child is still there)

What I want to be able to do is return the first admission date to program A (1/1/2018 in this case) for the child that is currently in program A. Where the time out of PROGRAM A (2/2/2018 to 2/15/2018) is less than 31 days. One probably doesn't even need the information about programs other than program A. I don't know if this helps or not. I am a novice with VBA so I feel like I'm not explaining it well. What do you need to know from me to better explain?

 

[xenou]

I would hesitate to write any SQL for this given the very little data provided and the real life probability that there are many problems with the data that are not represented by this very small sample. You might want to get an expert involved who can work with your actual data.

 

[Micron]

I don't think you can do this with only sql. At least I could not. If I understand the goal (the determination is based on a person in a specific program who gets re-admitted into that program within 30 days of the discharge date of that program), you'd have to first build a sorting query. At first glance, one might surmise that the sort would be on person then program. However, that would induce rows where the discharge date and re-admiting date for a person are non contiguous, which would make comparisons more difficult. So if the sort was first on program, then on person, and sorted by admit date, I think it would be doable. It would require an inner loop on a recordset to calculate the difference between the discharge date of the first record of the record pair and the admit date of the second record of the pair, where the person and program were the same for that pair of records. If any of the comparisons fail (i.e. the person or program between a pair of records are not both equal) then the outer loop has to move one record and repeat the comparison with a new pair.

I have done this sort of thing before and can tell you it's not a ten minute exercise by any means. In my case, I needed a calculation to ensure that any contracted worker was not showing up on any company job site within x hours from their last clock out time. If they were, it was a potential violation of government regulation or a billing of hours that may not have been valid.

 

[TyeReece]

Micron, I appreciate your input. I was afraid it would be too difficult. Thanks.

 

[xenou]

I would also think this would be somewhat easier if it could be guaranteed that every discharge date has a matching admit date (i.e., as Micron put it, the dates are contiguous).

 

[TyeReece]

They generally are contiguous but I can't guarantee it in all cases. Thanks anyway.