January 21, 2009 at 11:55 am
I am working with a medical practice management database, trying to correct a problem with a single table. The table contains patient insurance plan data. It has guid columns for person_id, insurance_payer_id, and person_payer_id. Each record for a given person_id has a sequential number(column name:cob) to sort patient insurance payers on the front end. The sequential number is reset to 1 for the next person_id. So in a perfect world, I would have:
person_id insurance_payer_id cob
6E791... DFAF1..... 1
6E791... 1C145..... 2
11A57..... E1FDB..... 1
However, I have 30,000+ patients with gaps in the sequential ordering. This creates problems on the front end, because the program (which I have no control over) will not allow re-sorting of the insurance plans unless the cob column has no gaps.
My question is, is there a way to resequence the cob column for each person_id to remove the gaps?
Thanks,
Kate
January 21, 2009 at 12:35 pm
Hi Kate,
Does the query below return the needed results?
select person_id,
insurance_payer_id,
RANK() OVER(PARTITION BY person_id ORDER BY insurance_payer_id asc) as 'cob'
from dbo.resequence
If so, you can use the results to update the table.
January 21, 2009 at 1:39 pm
That's perfect! Thanks!
Kate
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply