Compare date fields in SQL server

Jason Yousef
Jason Yousef used Ask the Experts™
on
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
readmin.jpg
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jason YousefSr. BI  Developer

Author

Commented:
I do apologize that the question was long...
Rajkumar GsSoftware Engineer

Commented:
Hi huslayer,

In the sample data you provided, we need to remove only one record, right ?
Medical Rec No = 141954    

Do you want to keep the latest record from duplicate record ?

Raj
Rajkumar GsSoftware Engineer

Commented:
>> Eliminate any rows that is NOT duplicate (not everybody in this file has been admitted again)

Ok. So you want to remove non-duplicating records ?
Then check these scripts

Raj
Eliminate any rows that is NOT duplicate (not everybody in this file has been admitted again)


-- This will give you duplicated records
SELECT [MEDICAL REC NO], COUNT(*) FROM YourTable
GROUP BY [MEDICAL REC NO]
HAVING COUNT(*) > 1

-- Here is the non-dupliating records
SELECT [MEDICAL REC NO], COUNT(*) FROM YourTable
GROUP BY [MEDICAL REC NO]
HAVING COUNT(*)  = 1

-- To remove non-duplicated records
DELETE FROM YourTable
	WHERE [MEDICAL REC NO] IN
		(
			SELECT [MEDICAL REC NO] FROM 
			(
				SELECT [MEDICAL REC NO], COUNT(*) FROM YourTable
				GROUP BY [MEDICAL REC NO]
				HAVING COUNT(*)  = 1
			)
		)

Open in new window

Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Rajkumar GsSoftware Engineer

Commented:
Replace 'YourTable' with your actual tablename

Raj
Jason YousefSr. BI  Developer

Author

Commented:
Hi Raj,

Thank you for your fast reply.

But now how can we filter and calculate the "ReAdmitdate" and "ReDischargedate" from the duplicate rows and add it to the report?

Please advise
Thanks
Rajkumar GsSoftware Engineer

Commented:
From this query for duplicate records, use the fields ("ReAdmitdate" and "ReDischargedate") for any calculation

Raj


SELECT * FROM YourTable WHERE [MEDICAL REC NO] IN
(
	SELECT [MEDICAL REC NO] FROM  
	( 
			SELECT [MEDICAL REC NO], COUNT(*) FROM YourTable 
			GROUP BY [MEDICAL REC NO] 
			HAVING COUNT(*)  > 1 
	) 
)

Open in new window

Jason YousefSr. BI  Developer

Author

Commented:
Raj,
I'm getting

Msg 102, Level 15, State 1, Line 11
Incorrect syntax near ')'.

when I run the 3rd script.

attached is my code
DELETE FROM Report
        WHERE MedRecNo IN
                (
                        SELECT [MEDRECNO] FROM 
                        (
                                SELECT [MEDRECNO], COUNT(*) FROM Report
                                GROUP BY [MEDRECNO]
                                HAVING COUNT(*)  = 1
                       
                        )
                        )

Open in new window

Jason YousefSr. BI  Developer

Author

Commented:
and this one too, i'm getting the same error

"Msg 102, Level 15, State 1, Line 9
Incorrect syntax near ')'."
SELECT * FROM report WHERE [MEDRECNO] IN
(
        SELECT [MEDRECNO] FROM  
        ( 
                        SELECT [MEDRECNO], COUNT(*) FROM report
                        GROUP BY [MEDECNO] 
                        HAVING COUNT(*)  > 1 
        ) 
)

Open in new window

Jason YousefSr. BI  Developer

Author

Commented:
this query can show me the duplicates , 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?

Please Help Raj
Thanks
SELECT *
 FROM  dbo.Report
 WHERE [MedRecNo]

IN (
SELECT [MedRecNo] 
FROM dbo.Report AS Tmp 
GROUP BY [MedRecNo] 
HAVING Count( * ) >1
) 

ORDER BY [MedRecNo]

Open in new window

Rajkumar GsSoftware Engineer

Commented:
>> 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? <<


Can you explain with an example?

Raj
Jason YousefSr. BI  Developer

Author

Commented:
duplicate records means the person has been admitted more than once to the hospital.

in the excel report we need to put in one line admit date and discharge date, and re-admitdate and re-discharge date

i've attached the final excel report
hffinc-20090415.xls
Rajkumar GsSoftware Engineer

Commented:
I understood like this

You want to display ONLY those medical records having duplicate values.
It should be presented in a way that latest record & previous record in a single row.

These are the columns needed in the output
MedRecNo
      
ReAdmitWithin      
AdmitDate      
ReAdmitDate      

DateOfDischarge      
ReAdmitDateOfDischarge      

PatientNo      
PatientName      

DXCode      
ReAdmitDXCode      

LOS      
DIAGName      

DRGNo      
ReAdmitDRGNo      

FinCode      
FinDescr            

Raj
Jason YousefSr. BI  Developer

Author

Commented:
Yesss :)
Rajkumar GsSoftware Engineer

Commented:
In the example, that you mentioned in your question

About MedicalRecNo = 141954, which duplicates... This patient  ( SHUMATE ,VALERIA ) have different PatientNo. Is it so ? Means  PatientNo will change each time ?

Raj
Jason YousefSr. BI  Developer

Author

Commented:
yes it changes every time .
Rajkumar GsSoftware Engineer

Commented:
Hi,

This is not the final query. I have created a temporary table and included the main fields and tested my query on the sample data.

You please check the script and test it.

Let me know whether this is the result you are looking for. If yes, verify that it is correct for your various records

Note that the column names I used may slight vary. So when you test it with your actual table, rename those columns to your actual column names

Will be back tomorrow. Have a nice day...
Raj
-- FIRST CREATE TEMPORARY TABLE
CREATE TABLE #tblMedicalRecords
(
	MedicalRecNo	INT,
	AdmitDate		DATETIME,
	PatientNumber	VARCHAR(20),
	PatientName		VARCHAR(50),
	DateOfDischarge	DATETIME,
	DXCode			VARCHAR(20),
	DrgNo			INT
)

-- INSERT TWO SETS OF DUPLICATE RECORDS AND ONE NON-DUPLICATE RECORD
INSERT INTO #tblMedicalRecords
SELECT 141954,    '02/25/09',       '72779499',  'SHUMATE ,VALERIA',       '02/25/09',   '112.84',     370  
UNION 
SELECT 141954,    '03/07/09',       '36271732',  'SHUMATE ,VALERIA',       '03/10/09',   '493.92',     203  
UNION
SELECT 145299,    '01/21/09',       '12406294',  'BAUGH ,MARIA',               '01/21/09',   '366.17',     117  
union
SELECT 141955,    '02/26/09',       '72779400',  'RAJ ,KUMAR',       '02/26/09',   '55.84',     323  
UNION 
SELECT 141955,    '03/08/09',       '36271743',  'RAJ ,KUMAR',       '03/11/09',   '43.92',     52 


-- ACTUAL TEST DATA
SELECT * FROM #tblMedicalRecords

-- HERE IS THE FINAL QUERY. CHECK ITS OUTPUT
SELECT A.MedicalRecNo, 
		A.AdmitDate, B.AdmitDate AS ReAdmitDate,
		A.DateOfDischarge, B.DateOfDischarge AS ReDateOfDischarge,
		A.PatientNumber, B.PatientNumber AS RePatientNumber,
		A.PatientName,
		A.DXCode, B.DXCode AS ReAdmitDXCode,
		A.DrgNo, B.DrgNo AS ReAdmitDrgNo
FROM #tblMedicalRecords A
	INNER JOIN #tblMedicalRecords B ON A.MedicalRecNo = B.MedicalRecNo AND
				A.AdmitDate <> B.AdmitDate and A.AdmitDate < B.AdmitDate

-- DROP THE TEMPORARY TABLE
DROP TABLE #tblMedicalRecords

Open in new window

Jason YousefSr. BI  Developer

Author

Commented:
Cheeeeers for reply !
Works...........

Now i'm trying to figure out how to integrate it in SSIS in the same package as the data extraction :")
Rajkumar GsSoftware Engineer

Commented:
Nice to hear :)

Hope it will easier for you to integrate.

Raj
Jason YousefSr. BI  Developer

Author

Commented:
Raj,
Good morning..
how to SELECT that query INTO new Table ?
Rajkumar GsSoftware Engineer

Commented:
If you meant to insert the result of that query into already existing table

INSERT INTO YourTable
SELECT A.MedicalRecNo,  
                A.AdmitDate, B.AdmitDate AS ReAdmitDate,
                A.DateOfDischarge, B.DateOfDischarge AS ReDateOfDischarge,
                A.PatientNumber, B.PatientNumber AS RePatientNumber,
                A.PatientName,
                A.DXCode, B.DXCode AS ReAdmitDXCode,
                A.DrgNo, B.DrgNo AS ReAdmitDrgNo
FROM #tblMedicalRecords A
        INNER JOIN #tblMedicalRecords B ON A.MedicalRecNo = B.MedicalRecNo AND
                                A.AdmitDate <> B.AdmitDate and A.AdmitDate < B.AdmitDate

But the destination table should have the same columns as in the result of the query. Otherwise you have specify the columns like

INSERT INTO YourTable (Col1, Col2,...)
SELECT ....

Hope this helps
Raj
Rajkumar GsSoftware Engineer

Commented:
SELECT *
INTO <NewTable?
FROM (<< Our Query >>) A

will create new table. We no need to do it.

If you are not clear, check this link
http://www.w3schools.com/sql/sql_select_into.asp

Raj
Jason YousefSr. BI  Developer

Author

Commented:
I Thought i'm doing something wrong, i'm using the exact query and the columns are the same but still gets this error

Msg 213, Level 16, State 1, Line 1
Column name or number of supplied values does not match table definition.



and here's my table CREATE statement

USE HFFINC
GO
/****** Object:  Table [dbo].[ReportFinal]    Script Date: 07/27/2009 11:05:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ReportFinal](
      [MedRecNo] [nvarchar](50) NULL,
      [ReAdmitWithin] [Int] NULL,
      [AdmitDate] [Date],
      [ReAdmitDate] [Date] ,
      [DateOfDischarge] [Date],
      [ReDateOfDischarge] [Date],
      [PatientNo] [nvarchar](50) NULL,
      [PatientName] [nvarchar](50) NULL,
      [DXCode] [nvarchar](50) NULL,
      [ReAdmitDxCode] [nvarchar](50) NULL,
      [LOS] [nvarchar](50) NULL,
      [DIAGName] [nvarchar](50) NULL,
      [DRGNo] [nvarchar](50) NULL,
      [ReAdmitDRGNo] [nvarchar](50) NULL,
      [FinCode] [nvarchar](5) NULL,
      [FinDescr] [nvarchar](100) NULL
) ON [PRIMARY]



so what i'm doing wrong this time?
insert into dbo.ReportFinal

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

Open in new window

Jason YousefSr. BI  Developer

Author

Commented:
SELECT *
INTO >) A


That works

Thanks again :)

Now i'm checking the integraty of data in the report and will update you

what's url E-mail? I wanna send you something.

Software Engineer
Commented:

There is an unnecessary condition in the query that I just noticed. :)
...AND A.AdmitDate <> B.AdmitDate and ...

Already there is a condition like ... A.AdmitDate < B.AdmitDate ... which will do job alone. So remove the condition this from WHERE conditon and try (... AND  A.AdmitDate <> B.AdmitDate... )

So the query I posted finally will become...

Raj
SELECT A.MedicalRecNo,  
                A.AdmitDate, B.AdmitDate AS ReAdmitDate, 
                A.DateOfDischarge, B.DateOfDischarge AS ReDateOfDischarge, 
                A.PatientNumber, B.PatientNumber AS RePatientNumber, 
                A.PatientName, 
                A.DXCode, B.DXCode AS ReAdmitDXCode, 
                A.DrgNo, B.DrgNo AS ReAdmitDrgNo 
FROM #tblMedicalRecords A 
        INNER JOIN #tblMedicalRecords B 
			ON A.MedicalRecNo = B.MedicalRecNo 
				AND A.AdmitDate < B.AdmitDate

Open in new window

Rajkumar GsSoftware Engineer

Commented:
Hi huslayer,

>> what's url E-mail?
raj.experts.exchange@gmail.com

Raj
Jason YousefSr. BI  Developer

Author

Commented:
Raj,
Still waiting for the feedback, will keep you updated :)
Jason YousefSr. BI  Developer

Author

Commented:
Thanks for your help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial