Compare date fields in SQL server

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
LVL 21
Jason Yousef, MSSr. BI DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jason Yousef, MSSr. BI  DeveloperAuthor Commented:
I do apologize that the question was long...
0
Rajkumar GsSoftware EngineerCommented:
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
0
Rajkumar GsSoftware EngineerCommented:
>> 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

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Rajkumar GsSoftware EngineerCommented:
Replace 'YourTable' with your actual tablename

Raj
0
Jason Yousef, MSSr. BI  DeveloperAuthor 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
0
Rajkumar GsSoftware EngineerCommented:
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

0
Jason Yousef, MSSr. BI  DeveloperAuthor 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

0
Jason Yousef, MSSr. BI  DeveloperAuthor 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

0
Jason Yousef, MSSr. BI  DeveloperAuthor 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

0
Rajkumar GsSoftware EngineerCommented:
>> 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
0
Jason Yousef, MSSr. BI  DeveloperAuthor 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
0
Rajkumar GsSoftware EngineerCommented:
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
0
Jason Yousef, MSSr. BI  DeveloperAuthor Commented:
Yesss :)
0
Rajkumar GsSoftware EngineerCommented:
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
0
Jason Yousef, MSSr. BI  DeveloperAuthor Commented:
yes it changes every time .
0
Rajkumar GsSoftware EngineerCommented:
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

0
Jason Yousef, MSSr. BI  DeveloperAuthor 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 :")
0
Rajkumar GsSoftware EngineerCommented:
Nice to hear :)

Hope it will easier for you to integrate.

Raj
0
Jason Yousef, MSSr. BI  DeveloperAuthor Commented:
Raj,
Good morning..
how to SELECT that query INTO new Table ?
0
Rajkumar GsSoftware EngineerCommented:
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
0
Rajkumar GsSoftware EngineerCommented:
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
0
Jason Yousef, MSSr. BI  DeveloperAuthor 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

0
Jason Yousef, MSSr. BI  DeveloperAuthor 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.

0
Rajkumar GsSoftware EngineerCommented:

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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rajkumar GsSoftware EngineerCommented:
Hi huslayer,

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

Raj
0
Jason Yousef, MSSr. BI  DeveloperAuthor Commented:
Raj,
Still waiting for the feedback, will keep you updated :)
0
Jason Yousef, MSSr. BI  DeveloperAuthor Commented:
Thanks for your help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.