Hi Folks,
The following problem that I am working on for a non-profit government agency has unfortunetly surpassing my coding skills. Any help would be GREATLY appreciated, as I am frantically trying to find out how to do this ... I know it is possible, just not sure how to go about it. Please help :|
I have a huge list of individuals who receive service a certain type of service. The individuals can receive one day of service, several days of service in a row, one day of service then in the future either one day of service and/or several days of service in a row. I have grouped and normalized the data uniquely by PatientIdentifier and DateOfService for this data set.
Because some of the individuals have a string of service that occur on a day to day basis for a few days ...
Example:
John Smith 10/01/01
John Smith 10/02/01
John Smith 10/03/01
John Smith 10/04/01
John Smith 10/18/01
I would like to grab the last day of service in that string. For example, the code would return:
John Smith 10/04/01 because John Smith did not receive a service on 10/05/01 but received a service on 10/03/01. And I would return John Smith 10/18/01 because John Smith only received one service on that day and did not receive a service the very next day.
I was able to create some queries to help me with this. So now, I have sorted the data set by PatientIdentifier then by DateOfService. The query simply computes a Day_Lapse by taking the current PatientIdentifier and DateOfService and comparing it to the previous PatientIdentifier and DateOfService. If the previous PatientIdentifier matches the current PatientIdentifier, then the difference between the current DateOfService and Previous DateOfService is returned. If the previous PatientIdentifier does not match the current PatientIdentifier, then the Day_Lapse is of course null.
I used a query like this to get the above results:
SELECT [PatientIdentifier], [DateOfService], DateDiff("d",(Select Max([DateOfService]) from [CBTEMP] where [PatientIdentifier]=a.[PatientIdentifier] and [DateOfService] < a.[DateOfService]), [DateOfService]) AS Day_Lapse
FROM [CBTEMP] AS a
ORDER BY [PatientIdentifier], [DateOfService];
The query works wonderfully and returns some results such as the ones pasted below:
Patient ID DateOfService Day_Lapse
10114 12/24/02
10270 02/06/03
10270 02/07/03 1
10270 02/08/03 1
10270 02/09/03 1
10270 02/10/03 1
10270 02/11/03 1
10270 02/17/03 6
10270 02/18/03 1
10270 03/04/03 14
10270 03/05/03 1
10270 03/19/03 14
10270 03/20/03 1
10270 03/21/03 1
10270 03/22/03 1
10270 04/02/03 11
10270 04/03/03 1
10270 04/04/03 1
10322 05/24/03
10322 05/25/03 1
10322 06/03/03 9
10322 06/04/03 1
10322 06/05/03 1
10406 01/07/03
10406 01/08/03 1
10406 01/09/03 1
10406 01/10/03 1
10406 01/11/03 1
10406 01/12/03 1
10406 02/18/03 37
10406 02/19/03 1
10406 02/20/03 1
10406 02/21/03 1
10406 03/07/03 14
10406 03/08/03 1
10406 03/09/03 1
10406 03/10/03 1
10406 03/21/03 11
10406 03/22/03 1
10406 03/30/03 8
10406 03/31/03 1
10406 04/01/03 1
10406 04/02/03 1
10479 06/09/03
10479 06/10/03 1
10479 06/11/03 1
10479 06/15/03 4
10479 06/16/03 1
10791 02/15/03
10791 02/16/03 1
However, I am not sure how to extract only the last sequence in the set. I would somehow need to create an 'If' statement that would do the following given the data set above:
If the PatientID for the current record is the same as the PatientID for the previous record AND if the Day_Lapse for the current record is greater than 1, then return the previous record as that would be the last sequence in the set. And if the Day_Lapse is null, only return that record if the next record does not have the same PatientID OR does have the same PatientID but it's Day_Lapse is not = 1. So in effect, I the end result would be a list of PatientID and DateOfServices that are the last ones in a back-to-back daily string of services and also return instances where the individual only has one DateOfService in that time with no DateOfService the day after that day.
I know there is a way to do this, and unfortunetly I do not think that a regular query could handle these compounded 'If' statements that I need to get my result. If anyone can PLEASE help me, I would GREATLY appreciate it so, so much. Thank you very much.
Joe
The following problem that I am working on for a non-profit government agency has unfortunetly surpassing my coding skills. Any help would be GREATLY appreciated, as I am frantically trying to find out how to do this ... I know it is possible, just not sure how to go about it. Please help :|
I have a huge list of individuals who receive service a certain type of service. The individuals can receive one day of service, several days of service in a row, one day of service then in the future either one day of service and/or several days of service in a row. I have grouped and normalized the data uniquely by PatientIdentifier and DateOfService for this data set.
Because some of the individuals have a string of service that occur on a day to day basis for a few days ...
Example:
John Smith 10/01/01
John Smith 10/02/01
John Smith 10/03/01
John Smith 10/04/01
John Smith 10/18/01
I would like to grab the last day of service in that string. For example, the code would return:
John Smith 10/04/01 because John Smith did not receive a service on 10/05/01 but received a service on 10/03/01. And I would return John Smith 10/18/01 because John Smith only received one service on that day and did not receive a service the very next day.
I was able to create some queries to help me with this. So now, I have sorted the data set by PatientIdentifier then by DateOfService. The query simply computes a Day_Lapse by taking the current PatientIdentifier and DateOfService and comparing it to the previous PatientIdentifier and DateOfService. If the previous PatientIdentifier matches the current PatientIdentifier, then the difference between the current DateOfService and Previous DateOfService is returned. If the previous PatientIdentifier does not match the current PatientIdentifier, then the Day_Lapse is of course null.
I used a query like this to get the above results:
SELECT [PatientIdentifier], [DateOfService], DateDiff("d",(Select Max([DateOfService]) from [CBTEMP] where [PatientIdentifier]=a.[PatientIdentifier] and [DateOfService] < a.[DateOfService]), [DateOfService]) AS Day_Lapse
FROM [CBTEMP] AS a
ORDER BY [PatientIdentifier], [DateOfService];
The query works wonderfully and returns some results such as the ones pasted below:
Patient ID DateOfService Day_Lapse
10114 12/24/02
10270 02/06/03
10270 02/07/03 1
10270 02/08/03 1
10270 02/09/03 1
10270 02/10/03 1
10270 02/11/03 1
10270 02/17/03 6
10270 02/18/03 1
10270 03/04/03 14
10270 03/05/03 1
10270 03/19/03 14
10270 03/20/03 1
10270 03/21/03 1
10270 03/22/03 1
10270 04/02/03 11
10270 04/03/03 1
10270 04/04/03 1
10322 05/24/03
10322 05/25/03 1
10322 06/03/03 9
10322 06/04/03 1
10322 06/05/03 1
10406 01/07/03
10406 01/08/03 1
10406 01/09/03 1
10406 01/10/03 1
10406 01/11/03 1
10406 01/12/03 1
10406 02/18/03 37
10406 02/19/03 1
10406 02/20/03 1
10406 02/21/03 1
10406 03/07/03 14
10406 03/08/03 1
10406 03/09/03 1
10406 03/10/03 1
10406 03/21/03 11
10406 03/22/03 1
10406 03/30/03 8
10406 03/31/03 1
10406 04/01/03 1
10406 04/02/03 1
10479 06/09/03
10479 06/10/03 1
10479 06/11/03 1
10479 06/15/03 4
10479 06/16/03 1
10791 02/15/03
10791 02/16/03 1
However, I am not sure how to extract only the last sequence in the set. I would somehow need to create an 'If' statement that would do the following given the data set above:
If the PatientID for the current record is the same as the PatientID for the previous record AND if the Day_Lapse for the current record is greater than 1, then return the previous record as that would be the last sequence in the set. And if the Day_Lapse is null, only return that record if the next record does not have the same PatientID OR does have the same PatientID but it's Day_Lapse is not = 1. So in effect, I the end result would be a list of PatientID and DateOfServices that are the last ones in a back-to-back daily string of services and also return instances where the individual only has one DateOfService in that time with no DateOfService the day after that day.
I know there is a way to do this, and unfortunetly I do not think that a regular query could handle these compounded 'If' statements that I need to get my result. If anyone can PLEASE help me, I would GREATLY appreciate it so, so much. Thank you very much.
Joe
