Systems Engineering and RDBMS

Another Self Join scenario

Posted by decipherinfosys on September 22, 2008

In one of our earlier posts, we had covered self joins. Yesterday, while helping a client with their data processing logic a similar situation arose which was resolved by making use of a self join. The scenario is not an uncommon one in the healthcare industry. This private practice had prescriptions for their patients stored in the database (SQL Server lingo) and had the start and end dates for those prescriptions. They are required to track this and also furnish reports to the insurance companies as well as to the auditors and one of the things that they look for is whether the prescriptions overlapped or was their a gap in the prescription for the same medication etc.. One of the reasons why they want to see such reports also is to track malpractice – an example: We were told that some doctors were caught under the malpractice insurance law when they had un-necessarily prescribed pain killers to the patient and the patient had infact taken those and sold them in order to make a profit…

So, how does all this fit into a self join? 🙂 Well, the information is stored in the database tables and data is power, isn’t it? Especially when used correctly. So, let’s create some dummy data first:

create table patient_info (pat_nbr int identity not null primary key, pat_first_name nvarchar(10) not null, pat_ssn nvarchar(9) not null)
create table patient_presc (pat_presc_nbr int identity not null, pat_nbr int not null, presc_id int not null, start_date_time datetime not null, end_date_time datetime not null,
constraint pk_pat_presc primary key (pat_presc_nbr),
constraint fk_to_pat_info foreign key (pat_nbr) references patient_info (pat_nbr))
create unique index patient_presc_ind_1 on patient_presc (pat_nbr, presc_id, start_date_time);

go
set nocount on
go
insert into patient_info (pat_first_name, pat_ssn) values (‘tom’, ‘123456789’);
insert into patient_info (pat_first_name, pat_ssn) values (‘harry’, ‘012345678’);
insert into patient_presc (pat_nbr, presc_id, start_date_time, end_date_time) values (1, 1, ’03/01/2008′, ’03/31/2008′);
insert into patient_presc (pat_nbr, presc_id, start_date_time, end_date_time) values (1, 1, ’03/20/2008′, ’04/15/2008′);
insert into patient_presc (pat_nbr, presc_id, start_date_time, end_date_time) values (1, 2, ’03/22/2008′, ’04/20/2008′);
insert into patient_presc (pat_nbr, presc_id, start_date_time, end_date_time) values (1, 1, ’05/01/2008′, ’05/30/2008′);
insert into patient_presc (pat_nbr, presc_id, start_date_time, end_date_time) values (1, 1, ’07/01/2008′, ’07/31/2008′);
insert into patient_presc (pat_nbr, presc_id, start_date_time, end_date_time) values (1, 1, ’07/25/2008′, ’08/25/2008′);

If you look into the data, you will see that there are two scenarios where there is an overlap of the prescriptions – for the same prescription (same medication), before the previous prescription’s date ended, a new prescription was issued. This is a cause for an audit which will then be followed up by the auditor with the doctors and nurses in the practice – there always is a reason code that is also stored in the tables as well as the doctor/nurse information but for the sake of this example, we have not shown that piece of information. We can get this information by doing a simple self join – here is the SQL:

select ‘Overlapped Prescriptions’,
pi.pat_first_name, pp1.end_date_time, min(pp2.start_date_time) as next_presc_start_date
from dbo.patient_info as pi
inner join dbo.patient_presc as pp1
on pi.pat_nbr = pp1.pat_nbr
inner join dbo.patient_presc as pp2
on pi.pat_nbr = pp2.pat_nbr
where pi.pat_ssn = ‘123456789’
and pp1.presc_id = pp2.presc_id
and pp1.start_date_time < pp2.start_date_time
and pp1.end_date_time < pp2.end_date_time
group by pi.pat_first_name, pp1.end_date_time
having datediff (dd, pp1.end_date_time, min(pp2.start_date_time)) < 1

                         pat_first_name end_date_time           next_presc_start_date
------------------------ -------------- ----------------------- -----------------------
Overlapped Prescriptions tom            2008-03-31 00:00:00.000 2008-03-20 00:00:00.000
Overlapped Prescriptions tom            2008-07-31 00:00:00.000 2008-07-25 00:00:00.000

Another thing that you would note is that there is 1 scenario where there is a gap of more than 30 days between the same type of prescription to the same patient. This could be patient negligence or could even be a valid scenario where the same disease (maybe a viral infection) occurred at different times leading to the same prescription being given again within 45 days but after a gap of 30 days. We can get this information by using the same SQL as above but changing the having criteria a bit:

select ‘Prescription Gap of more than 30 days and less than 45 days’,
pi.pat_first_name, pp1.end_date_time, min(pp2.start_date_time) as next_presc_start_date
from dbo.patient_info as pi
inner join dbo.patient_presc as pp1
on pi.pat_nbr = pp1.pat_nbr
inner join dbo.patient_presc as pp2
on pi.pat_nbr = pp2.pat_nbr
where pi.pat_ssn = ‘123456789’
and pp1.presc_id = pp2.presc_id
and pp1.start_date_time < pp2.start_date_time
and pp1.end_date_time < pp2.end_date_time
group by pi.pat_first_name, pp1.end_date_time
having datediff (dd, pp1.end_date_time, min(pp2.start_date_time)) > 30
and datediff (dd, pp1.end_date_time, min(pp2.start_date_time)) < 45

                                                            pat_first_name end_date_time           next_presc_start_date
----------------------------------------------------------- -------------- ----------------------- -----------------------
Prescription Gap of more than 30 days and less than 45 days tom            2008-05-30 00:00:00.000 2008-07-01 00:00:00.000

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: