Skip to content

Instantly share code, notes, and snippets.

@ebot

ebot/count_echo_recs.sql

Created February 22, 2011 20:51
Show Gist options
  • Save ebot/839360 to your computer and use it in GitHub Desktop.
Save ebot/839360 to your computer and use it in GitHub Desktop.
Download ZIP
Patch to Delete Duplicate records from echo and then verify whats left by counting the unique patients.
Raw
count_echo_recs.sql
-- DELETE THE BabyInformation DUPLICATES
BEGIN
SELECT [PA-PT-NO-WOSCD], [PA-ACCT-TYPE], [PA-CTL-PAA-XFER-DATE],
COUNT([PA-PT-NO-WOSCD]) AS dupes, MAX([PA-REC-CREATE-DATE]) AS last_occurance,
MIN([PA-REC-CREATE-DATE]) AS first_occurance
INTO #BabyInformation_dupes
FROM BabyInformation
GROUP BY [PA-PT-NO-WOSCD], [PA-ACCT-TYPE], [PA-CTL-PAA-XFER-DATE]
DELETE FROM BabyInformation
WHERE [PA-REC-CREATE-DATE] != (SELECT last_occurance
FROM #BabyInformation_dupes
WHERE #BabyInformation_dupes.[PA-PT-NO-WOSCD] = BabyInformation.[PA-PT-NO-WOSCD]
AND #BabyInformation_dupes.[PA-ACCT-TYPE] = BabyInformation.[PA-ACCT-TYPE]
AND #BabyInformation_dupes.[PA-CTL-PAA-XFER-DATE] = BabyInformation.[PA-CTL-PAA-XFER-DATE])
DROP TABLE #BabyInformation_dupes
DELETE BabyInformation
FROM BabyInformation
LEFT OUTER JOIN PatientDemographics ON
BabyInformation.[PA-PT-NO-WOSCD] = PatientDemographics.[PA-PT-NO-WOSCD]
AND BabyInformation.[PA-ACCT-TYPE] = PatientDemographics.[PA-ACCT-TYPE]
WHERE PatientDemographics.[PA-ACCT-TYPE] IS NULL
END
-- DELETE THE DiagnosisInformation DUPLICATES
BEGIN
SELECT [PA-PT-NO-WOSCD], [PA-ACCT-TYPE], [PA-CTL-PAA-XFER-DATE],
COUNT([PA-PT-NO-WOSCD]) AS dupes, MAX([PA-REC-CREATE-DATE]) AS last_occurance,
MIN([PA-REC-CREATE-DATE]) AS first_occurance
INTO #DiagnosisInformation_dupes
FROM DiagnosisInformation
GROUP BY [PA-PT-NO-WOSCD], [PA-ACCT-TYPE], [PA-CTL-PAA-XFER-DATE]
DELETE FROM DiagnosisInformation
WHERE [PA-REC-CREATE-DATE] != (SELECT last_occurance
FROM #DiagnosisInformation_dupes
WHERE #DiagnosisInformation_dupes.[PA-PT-NO-WOSCD] = DiagnosisInformation.[PA-PT-NO-WOSCD]
AND #DiagnosisInformation_dupes.[PA-ACCT-TYPE] = DiagnosisInformation.[PA-ACCT-TYPE]
AND #DiagnosisInformation_dupes.[PA-CTL-PAA-XFER-DATE] = DiagnosisInformation.[PA-CTL-PAA-XFER-DATE])
DROP TABLE #DiagnosisInformation_dupes
DELETE DiagnosisInformation
FROM DiagnosisInformation
LEFT OUTER JOIN PatientDemographics ON
DiagnosisInformation.[PA-PT-NO-WOSCD] = PatientDemographics.[PA-PT-NO-WOSCD]
AND DiagnosisInformation.[PA-ACCT-TYPE] = PatientDemographics.[PA-ACCT-TYPE]
WHERE PatientDemographics.[PA-ACCT-TYPE] IS NULL
END
-- DELETE THE DRGInformation DUPLICATES
BEGIN
SELECT [PA-PT-NO-WOSCD], [PA-ACCT-TYPE], [PA-CTL-PAA-XFER-DATE],
COUNT([PA-PT-NO-WOSCD]) AS dupes, MAX([PA-REC-CREATE-DATE]) AS last_occurance,
MIN([PA-REC-CREATE-DATE]) AS first_occurance
INTO #DRGInformation_dupes
FROM DRGInformation
GROUP BY [PA-PT-NO-WOSCD], [PA-ACCT-TYPE], [PA-CTL-PAA-XFER-DATE]
DELETE FROM DRGInformation
WHERE [PA-REC-CREATE-DATE] != (SELECT last_occurance
FROM #DRGInformation_dupes
WHERE #DRGInformation_dupes.[PA-PT-NO-WOSCD] = DRGInformation.[PA-PT-NO-WOSCD]
AND #DRGInformation_dupes.[PA-ACCT-TYPE] = DRGInformation.[PA-ACCT-TYPE]
AND #DRGInformation_dupes.[PA-CTL-PAA-XFER-DATE] = DRGInformation.[PA-CTL-PAA-XFER-DATE])
DROP TABLE #DRGInformation_dupes
DELETE DRGInformation
FROM DRGInformation
LEFT OUTER JOIN PatientDemographics ON
DRGInformation.[PA-PT-NO-WOSCD] = PatientDemographics.[PA-PT-NO-WOSCD]
AND DRGInformation.[PA-ACCT-TYPE] = PatientDemographics.[PA-ACCT-TYPE]
WHERE PatientDemographics.[PA-ACCT-TYPE] IS NULL
END
-- DELETE THE PBDInformation DUPLICATES
BEGIN
SELECT [PA-PT-NO-WOSCD], [PA-ACCT-TYPE], [PA-CTL-PAA-XFER-DATE],
COUNT([PA-PT-NO-WOSCD]) AS dupes, MAX([PA-REC-CREATE-DATE]) AS last_occurance,
MIN([PA-REC-CREATE-DATE]) AS first_occurance
INTO #PBDInformation_dupes
FROM PBDInformation
GROUP BY [PA-PT-NO-WOSCD], [PA-ACCT-TYPE], [PA-CTL-PAA-XFER-DATE]
DELETE FROM PBDInformation
WHERE [PA-REC-CREATE-DATE] != (SELECT last_occurance
FROM #PBDInformation_dupes
WHERE #PBDInformation_dupes.[PA-PT-NO-WOSCD] = PBDInformation.[PA-PT-NO-WOSCD]
AND #PBDInformation_dupes.[PA-ACCT-TYPE] = PBDInformation.[PA-ACCT-TYPE]
AND #PBDInformation_dupes.[PA-CTL-PAA-XFER-DATE] = PBDInformation.[PA-CTL-PAA-XFER-DATE])
DROP TABLE #PBDInformation_dupes
DELETE PBDInformation
FROM PBDInformation
LEFT OUTER JOIN PatientDemographics ON
PBDInformation.[PA-PT-NO-WOSCD] = PatientDemographics.[PA-PT-NO-WOSCD]
AND PBDInformation.[PA-ACCT-TYPE] = PatientDemographics.[PA-ACCT-TYPE]
WHERE PatientDemographics.[PA-ACCT-TYPE] IS NULL
END
-- DELETE THE PBHInformation DUPLICATES
BEGIN
SELECT [PA-PT-NO-WOSCD], [PA-ACCT-TYPE], [PA-CTL-PAA-XFER-DATE],
COUNT([PA-PT-NO-WOSCD]) AS dupes, MAX([PA-REC-CREATE-DATE]) AS last_occurance,
MIN([PA-REC-CREATE-DATE]) AS first_occurance
INTO #PBHInformation_dupes
FROM PBHInformation
GROUP BY [PA-PT-NO-WOSCD], [PA-ACCT-TYPE], [PA-CTL-PAA-XFER-DATE]
DELETE FROM PBHInformation
WHERE [PA-REC-CREATE-DATE] != (SELECT last_occurance
FROM #PBHInformation_dupes
WHERE #PBHInformation_dupes.[PA-PT-NO-WOSCD] = PBHInformation.[PA-PT-NO-WOSCD]
AND #PBHInformation_dupes.[PA-ACCT-TYPE] = PBHInformation.[PA-ACCT-TYPE]
AND #PBHInformation_dupes.[PA-CTL-PAA-XFER-DATE] = PBHInformation.[PA-CTL-PAA-XFER-DATE])
DROP TABLE #PBHInformation_dupes
DELETE PBHInformation
FROM PBHInformation
LEFT OUTER JOIN PatientDemographics ON
PBHInformation.[PA-PT-NO-WOSCD] = PatientDemographics.[PA-PT-NO-WOSCD]
AND PBHInformation.[PA-ACCT-TYPE] = PatientDemographics.[PA-ACCT-TYPE]
WHERE PatientDemographics.[PA-ACCT-TYPE] IS NULL
END
-- DELETE THE PPHInformation DUPLICATES
BEGIN
SELECT [PA-PT-NO-WOSCD], [PA-ACCT-TYPE], [PA-CTL-PAA-XFER-DATE],
COUNT([PA-PT-NO-WOSCD]) AS dupes, MAX([PA-REC-CREATE-DATE]) AS last_occurance,
MIN([PA-REC-CREATE-DATE]) AS first_occurance
INTO #PPHInformation_dupes
FROM PPHInformation
GROUP BY [PA-PT-NO-WOSCD], [PA-ACCT-TYPE], [PA-CTL-PAA-XFER-DATE]
DELETE FROM PPHInformation
WHERE [PA-REC-CREATE-DATE] != (SELECT last_occurance
FROM #PPHInformation_dupes
WHERE #PPHInformation_dupes.[PA-PT-NO-WOSCD] = PPHInformation.[PA-PT-NO-WOSCD]
AND #PPHInformation_dupes.[PA-ACCT-TYPE] = PPHInformation.[PA-ACCT-TYPE]
AND #PPHInformation_dupes.[PA-CTL-PAA-XFER-DATE] = PPHInformation.[PA-CTL-PAA-XFER-DATE])
DROP TABLE #PPHInformation_dupes
DELETE PPHInformation
FROM PPHInformation
LEFT OUTER JOIN PatientDemographics ON
PPHInformation.[PA-PT-NO-WOSCD] = PatientDemographics.[PA-PT-NO-WOSCD]
AND PPHInformation.[PA-ACCT-TYPE] = PatientDemographics.[PA-ACCT-TYPE]
WHERE PatientDemographics.[PA-ACCT-TYPE] IS NULL
END
-- DELETE THE PPSInformation DUPLICATES
BEGIN
SELECT [PA-PT-NO-WOSCD], [PA-ACCT-TYPE], [PA-CTL-PAA-XFER-DATE],
COUNT([PA-PT-NO-WOSCD]) AS dupes, MAX([PA-REC-CREATE-DATE]) AS last_occurance,
MIN([PA-REC-CREATE-DATE]) AS first_occurance
INTO #PPSInformation_dupes
FROM PPSInformation
GROUP BY [PA-PT-NO-WOSCD], [PA-ACCT-TYPE], [PA-CTL-PAA-XFER-DATE]
DELETE FROM PPSInformation
WHERE [PA-REC-CREATE-DATE] != (SELECT last_occurance
FROM #PPSInformation_dupes
WHERE #PPSInformation_dupes.[PA-PT-NO-WOSCD] = PPSInformation.[PA-PT-NO-WOSCD]
AND #PPSInformation_dupes.[PA-ACCT-TYPE] = PPSInformation.[PA-ACCT-TYPE]
AND #PPSInformation_dupes.[PA-CTL-PAA-XFER-DATE] = PPSInformation.[PA-CTL-PAA-XFER-DATE])
DROP TABLE #PPSInformation_dupes
DELETE PPSInformation
FROM PPSInformation
LEFT OUTER JOIN PatientDemographics ON
PPSInformation.[PA-PT-NO-WOSCD] = PatientDemographics.[PA-PT-NO-WOSCD]
AND PPSInformation.[PA-ACCT-TYPE] = PatientDemographics.[PA-ACCT-TYPE]
WHERE PatientDemographics.[PA-ACCT-TYPE] IS NULL
END
-- DELETE THE ProcedureInformation DUPLICATES
BEGIN
SELECT [PA-PT-NO-WOSCD], [PA-ACCT-TYPE], [PA-CTL-PAA-XFER-DATE],
COUNT([PA-PT-NO-WOSCD]) AS dupes, MAX([PA-REC-CREATE-DATE]) AS last_occurance,
MIN([PA-REC-CREATE-DATE]) AS first_occurance
INTO #ProcedureInformation_dupes
FROM ProcedureInformation
GROUP BY [PA-PT-NO-WOSCD], [PA-ACCT-TYPE], [PA-CTL-PAA-XFER-DATE]
DELETE FROM ProcedureInformation
WHERE [PA-REC-CREATE-DATE] != (SELECT last_occurance
FROM #ProcedureInformation_dupes
WHERE #ProcedureInformation_dupes.[PA-PT-NO-WOSCD] = ProcedureInformation.[PA-PT-NO-WOSCD]
AND #ProcedureInformation_dupes.[PA-ACCT-TYPE] = ProcedureInformation.[PA-ACCT-TYPE]
AND #ProcedureInformation_dupes.[PA-CTL-PAA-XFER-DATE] = ProcedureInformation.[PA-CTL-PAA-XFER-DATE])
DROP TABLE #ProcedureInformation_dupes
DELETE ProcedureInformation
FROM ProcedureInformation
LEFT OUTER JOIN PatientDemographics ON
ProcedureInformation.[PA-PT-NO-WOSCD] = PatientDemographics.[PA-PT-NO-WOSCD]
AND ProcedureInformation.[PA-ACCT-TYPE] = PatientDemographics.[PA-ACCT-TYPE]
WHERE PatientDemographics.[PA-ACCT-TYPE] IS NULL
END
Raw
echo_dup_fix.sql
SELECT 'BabyInformation' AS table_name, COUNT(DISTINCT [PA-PT-NO-WOSCD]) AS patients FROM BabyInformation
UNION SELECT 'DiagnosisInformation' AS table_name, COUNT(DISTINCT [PA-PT-NO-WOSCD]) AS patients FROM DiagnosisInformation
UNION SELECT 'DRGInformation' AS table_name, COUNT(DISTINCT [PA-PT-NO-WOSCD]) AS patients FROM DRGInformation
UNION SELECT 'PBDInformation' AS table_name, COUNT(DISTINCT [PA-PT-NO-WOSCD]) AS patients FROM PBDInformation
UNION SELECT 'PBHInformation' AS table_name, COUNT(DISTINCT [PA-PT-NO-WOSCD]) AS patients FROM PBHInformation
UNION SELECT 'PPHInformation' AS table_name, COUNT(DISTINCT [PA-PT-NO-WOSCD]) AS patients FROM PPHInformation
UNION SELECT 'PPSInformation' AS table_name, COUNT(DISTINCT [PA-PT-NO-WOSCD]) AS patients FROM PPSInformation
UNION SELECT 'ProcedureInformation' AS table_name, COUNT(DISTINCT [PA-PT-NO-WOSCD]) AS patients FROM ProcedureInformation
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment