Compare date fields in SQL server 1

[Huslayer]

Hi all,

I've a flat file that I cleaned the data out using SSIS, the output looks like that :


MEDICAL ADMIT PATIENT PATIENT DATE OF DX
REC NO DATE NUMBER NAME DISCHARGE Code DRG #

123613 02/16/09 12413209 MORIBALDI ,GEMMA 02/19/09 428.20 988
130897 01/23/09 12407193 TINLEY ,PATRICIA 01/23/09 535.10 392
139367 02/27/09 36262509 THARPE ,GLORIA 03/05/09 562.10 392

141954 02/25/09 72779499 SHUMATE ,VALERIA 02/25/09 112.84 370
141954 03/07/09 36271732 SHUMATE ,VALERIA 03/10/09 493.92 203

145299 01/21/09 12406294 BAUGH ,MARIA 01/21/09 366.17 117


and the report (final results) attached in the screen shot from the final excel report.

so what's happening is IF the same name or same account number is duplicate, that means the patient has entered the hospital again and needs to be included in the report.

what I need to do is...

Eliminate any rows that is NOT duplicate (not everybody in this file has been admitted again)
and compare the dates to get the ReAdmitdate and ReDischargedate

I dumped the data into a SQL table and trying to compare the dates to figure out "ReAdmitdate" and "ReDischargedate"

any help is appreciated.
Thanks

 

[MDXer]

A sql query using row_number() and OVER() should get you what you need. something like

WITH t AS(
Select
Name
, Rec_No
, Admit_Date
,Discharge_Date
, row_number() OVER(PARTITION BY NAME Order by NAME) as rec
From Table
)
Select
, Rec_No
, Admit_Date
, Discharge_Date
FROM t
Where rec > 1

Not tested but it should get you started.

 

[Huslayer]

Thank you MDXer for the fast reply...
I've edited the query to look like this
----------------------------
WITH t AS(
Select
PatientName,
medRecNo,
AdmitDate
,DateOfDischarge
, row_number() OVER(PARTITION BY PatientName Order by PatientName) as rec
From report
)
Select
PatientName
medRecNo
, AdmitDate
, DateOfDischarge
FROM t
Where rec > 1
-----------------------------
but I don't understand what's the result of this query?

but still how to tell which date is before the other and what's the "ReAdmitdate" and "ReDischargedate" from the duplicate rows and add it to the report?

it's so confusing, there's patients that admitted and discharged the same day, and admitted again after 4 or 5 days and discharged again,,,, and there's even patients that admitted 4 times !!!!!

Please help.
Thanks

 

[MDXer]

ok I'll explain the query in pieces. The first piece of the query is a CTE which actually helps you determine how many times someone has been admitted and discharged.

WITH t AS(
Select 
PatientName, 
medRecNo, 
AdmitDate
,DateOfDischarge
, row_number() OVER(PARTITION BY PatientName Order by PatientName) as rec
From report
)

if you run the query between the () you will see that the rec column will be 1,2,3 etc. This shows how many times the person will have been admitted. the row_number() assigns a simple row number based on the OVER. Think of the Partition by as a group by, you want the row number sequence to start at 1 for each Patient the Order By in the OVER performs a standard order by but is required for a row_number() you can add your Admit date to this and it will then assign the row number in chronological order.

The second part of the query is

Select
PatientName
medRecNo
, AdmitDate
, DateOfDischarge
FROM t
Where rec > 1

This selects from the CTE only the records that are greater than 1 so if you want all records for an individual you can build on this as follows,

WITH t AS(
Select 
PatientName, 
medRecNo, 
, row_number() OVER(PARTITION BY PatientName Order by PatientName) as rec
From report
), patients AS( 
Select Distinct
PatientName
medRecNo
FROM t
Where rec > 1
)

Select
r.*
From Report r
  JOIN patiens p ON r.PatientName = p.PatientName
                AND r.medRecNo = p.medRecno
Order by AdmitDate

In this you can see I am referencing the first CTE in the second CTE and returing a distinct list of individuals that had more than one admitance. I then join the second CTE to the original table to return the rows for those individual regardless of what row_number() it was. finally the order by admit will obviously return your records in chronological order.

 

[Huslayer]

Cheers for reply, you're too technical for me, I just don't know what to do with the results of the query...

i'm not really a programmer, I know some basics and I work with SSIS.

but really i'm stuck and need to get this report done, and I don't know what to do !! I've been search all over for any similar post but can't find !!

I there's a way we can use the row numbers ! or compare the dates where the medicalrecno is the same and the older will be the first admitdate ?

 

[Huslayer]

MDXer,
I figured it out using this query......

SELECT A.MedRecNo,
A.AdmitDate, B.AdmitDate AS ReAdmitDate,
A.DateOfDischarge, B.DateOfDischarge AS ReDateOfDischarge,
A.PatientNo, B.PatientNo AS RePatientNumber,
A.PatientName,
A.DXCode, B.DXCode AS ReAdmitDXCode,
A.DrgNo, B.DrgNo AS ReAdmitDrgNo
FROM report A
INNER JOIN report B ON A.MedRecNo = B.MedRecNo AND
A.AdmitDate <> B.AdmitDate and A.AdmitDate < B.AdmitDate


I need to ask you, do you see anything wrong with it? the results are OK, but I need an expert like you to look at it..maybe it needs modifications