Hi Folks,
I am trying to run this in access and if need be, can try to run it in SQL query analyzer. I am talking about 2 million records right now, and I got that down from twice that many.
I have a normalized listing of unique identifier and unique date of service. I am trying to write a query or program that would somehow sort the data by unique identifier and unique date of service, then on each current record compare the unique identifier from the current record to the record prior to it, if a match exists then I would like to know the difference in days between the two services, if there is no match I can leave null or put a flag indicating such.
This is all in an attempt to determine re-entry into the system via a 180 or greater lapse of service; so although the above logic is not set in stone for arriving at a base table that would render then end result, I will entertain any easier idea that might work.
In the past, I would simply use excel to perform calculations like this by simply sorting all the data by unique identifier and date of service and then just using IF statements to compare current line with previous line ... if unique identifier would match then I would get the difference in days between the current line and previous line and if no match then either put a flag or leave null ... this worked well and served its purpose. But now I have a 2 mill normalized record dataset, and excel will not work for this amount of data and I would hate to have to cut it into manageable pieces.
Any help would be greatly appreciated ... I know this can be done with queries or programing but simply is better. Is there an easy way to do this?
Thank you,
Joe
I am trying to run this in access and if need be, can try to run it in SQL query analyzer. I am talking about 2 million records right now, and I got that down from twice that many.
I have a normalized listing of unique identifier and unique date of service. I am trying to write a query or program that would somehow sort the data by unique identifier and unique date of service, then on each current record compare the unique identifier from the current record to the record prior to it, if a match exists then I would like to know the difference in days between the two services, if there is no match I can leave null or put a flag indicating such.
This is all in an attempt to determine re-entry into the system via a 180 or greater lapse of service; so although the above logic is not set in stone for arriving at a base table that would render then end result, I will entertain any easier idea that might work.
In the past, I would simply use excel to perform calculations like this by simply sorting all the data by unique identifier and date of service and then just using IF statements to compare current line with previous line ... if unique identifier would match then I would get the difference in days between the current line and previous line and if no match then either put a flag or leave null ... this worked well and served its purpose. But now I have a 2 mill normalized record dataset, and excel will not work for this amount of data and I would hate to have to cut it into manageable pieces.
Any help would be greatly appreciated ... I know this can be done with queries or programing but simply is better. Is there an easy way to do this?
Thank you,
Joe
