How to Fix Medisoft Data Corruption SQL Samples Corruption - The Computer Support People, LLC


		Cheat Sheet

MAKE SURE YOU MAKE A BACKUP OF DATA FIRST....
MAKE SURE YOU MAKE A BACKUP OF DATA FIRST....
MAKE SURE YOU MAKE A BACKUP OF DATA FIRST....
MAKE SURE YOU MAKE A BACKUP OF DATA FIRST....

MAKE SURE YOU DELETE THE FILE C:\ADS_ERR.LOG SO YOU KNOW WHAT ERROR HAPPENED. THIS CONTAINS ANY ERRORS THAT HAPPEN WHILE IN MEDISOFT OR ADVANTAGE DATABASE

ADS = ADVANTAGE DATABASE WHERE USED

***** USE THESE SCRIPTS FOR DATA CORRUPTION OR DATA CLEAN UPS ***********

For assistance contact our Medisoft Data Recovery Department

Loosing Data Can be Devastating for your Business (203) 653-4643

MWZIP

delete from mwzip where LENGTH("ZIP CODE") < 5
SELECT * FROM "MWZIP" WHERE "cOUNTRY" <> 'USA'

delete from mwzip where "ZIP CODE" LIKE '%)%' OR "ZIP CODE" LIKE '%(%' OR "ZIP CODE" LIKE '%[%' OR "ZIP CODE" LIKE '%]%' OR "ZIP CODE" LIKE '%!%' OR "ZIP CODE" LIKE '%#%' OR "ZIP CODE" LIKE '%$%'

delete from mwzip where "COUNTRY" LIKE '%)%' OR "COUNTRY" LIKE '%(%' OR "COUNTRY" LIKE '%[%' OR "COUNTRY" LIKE '%]%' OR "COUNTRY" LIKE '%!%' OR "COUNTRY" LIKE '%#%' OR "COUNTRY" LIKE '%$%'

delete from mwzip where "CITY" LIKE '%)%' OR "CITY" LIKE '%(%' OR "CITY" LIKE '%[%' OR "CITY" LIKE '%]%' OR "CITY" LIKE '%!%' OR "CITY" LIKE '%#%' OR "CITY" LIKE '%$%'

UPDATE MWZIP SET "COUNTRY" = 'USA' WHERE "COUNTRY" IS NULL OR "COUNTRY" = ''

SELECT "ZIP CODE" FROM MWZIP GROUP BY "ZIP CODE" HAVING COUNT("ZIP CODE") > 1

OHAPP

CORRUPT INDEX

REMOVE PRIMARY INDEX. OTHERWISE WE'RE NOT ABLE TO RUN ANY QUERY OR USE INDEX TO SORT FILE. IT ACTUALLY CORRUPTS THE TABLE MAKING IT IMPOSSIBLE TO OPEN IT.

AFTER REMOVING PRIMARY INDEX (UNIQUE), RUN

SELECT * FROM OHAPP ORDER BY "ID"

SELECT * FROM OHAPP WHERE "ID" = 13751 (WHERE 13751 IS THE ID THAT ADS REPORTS AS NOT UNIQUE)

DELETE FROM OHAPP WHERE "ID" = 13751 (DELETE ONLY IF IT LOOKS LIKE AN OLD APPOINMENT)

SELECT * FROM OHAPP ORDER BY "ID" (GO TO END AND BEGINNING TO SEE IF RECORDS LOOK OK)

DELETE FROM OHAPP WHERE "ID" = 50355719 (DELETE ONLY IF IT LOOKS LIKE AN OLD APPOINMENT OR IS CORRUPTED)

DELETE FROM OHAPP WHERE "ID" = 87892 (DELETE ONLY IF IT LOOKS LIKE AN OLD APPOINMENT OR IS CORRUPTED)

SELECT * FROM OHAPP where "PROVIDER" LIKE '%)%' OR "PROVIDER" LIKE '%(%' OR "PROVIDER" LIKE '%[%' OR "PROVIDER" LIKE '%]%' OR "PROVIDER" LIKE '%!%' OR "PROVIDER" LIKE '%#%' OR "REASON CODE" LIKE '%$%'

DELETE FROM OHAPP where "PROVIDER" LIKE '%)%' OR "PROVIDER" LIKE '%(%' OR "PROVIDER" LIKE '%[%' OR "PROVIDER" LIKE '%]%' OR "PROVIDER" LIKE '%!%' OR "PROVIDER" LIKE '%#%' OR "REASON CODE" LIKE '%$%'

(COULD NOT RUN ALL AT ONCE...RUN A MANUALLY FIX THEM OR DELETE THEM USING QUERY)

SELECT * FROM OHAPP where "PROVIDER" LIKE '%)%' OR "PROVIDER" LIKE '%(%'
SELECT * FROM OHAPP where "PROVIDER" LIKE '%[%' OR "PROVIDER" LIKE '%]%'
SELECT * FROM OHAPP where "PROVIDER" LIKE '%!%' OR "PROVIDER" LIKE '%#%' (CANT USE AT ALL)

SELECT "DATE" FROM OHAPP ORDER BY "DATE" (DELETE ANY OUTRAGES DATE GOING BACK)

DELETE FROM OHAPP WHERE "DATE" = '12/30/1899'

SELECT * FROM OHAPP ORDER BY "PROVIDER" (DELETE ANY OUTRAGES RECORD GOING BACK)
SELECT * FROM OHAPP ORDER BY "START TIME" (DELETE ANY OUTRAGES RECORD GOING BACK)
SELECT * FROM OHAPP ORDER BY "PROVIDER" (DELETE ANY OUTRAGES RECORD GOING BACK)

FOUND MAJOR PROBLEMS WHEN SORTING BY CHART NUMBER. YOU MAY WANT TO CORRECT THEM MANUALLY DEPENDING ON

THE APPOINTMENT DATE. CLICK INSIDE THE VALUE AND CORRECT IT.

PAY SPECIAL ATTENTION TO DATES, LENGTH, PHONE, NAME, NOTES, RESOURCE, COLOR, USER CODE

SELECT * FROM OHAPP ORDER BY "CHART NUMBER" (MANUALL CORRECT OR DELETE ANY OUTRAGES RECORD USING ID NUMBER)

SELECT "DATE CREATED" FROM OHAPP ORDER BY "DATE CREATED" (MANUALL CORRECT OR DELETE ANY OUTRAGES RECORD USING ID NUMBER)

SELECT * FROM OHAPP ORDER BY "DATE MODIFIED" (MANUALL CORRECT OR DELETE ANY OUTRAGES RECORD USING ID NUMBER)

SELECT * FROM OHAPP ORDER BY "LENGTH" (MANUALL CORRECT OR DELETE ANY OUTRAGES RECORD USING ID NUMBER)

SELECT * FROM OHAPP ORDER BY "CHECK IN TIME" (MANUALL CORRECT OR DELETE ANY OUTRAGES RECORD USING ID NUMBER)

SELECT * FROM OHAPP ORDER BY "CHECK OUT TIME" (MANUALL CORRECT OR DELETE ANY OUTRAGES RECORD USING ID NUMBER)

SELECT * FROM OHAPP ORDER BY "USER CODE" (MANUALL CORRECT OR DELETE ANY OUTRAGES RECORD USING ID NUMBER)

DELETE OR CLEAN DUPLICATES BASED ON ID. FIND THEM FIRST BY ID

SELECT "ID" FROM OHAPP GROUP BY "ID" HAVING COUNT("ID") > 1

SELECT * FROM OHAPP WHERE "ID" = 22357 (DUPLICATE ID)

WRITE DOWN THE APPOINTMENT AND LET USER KNOW SO IT CAN BE RE-ENTERED OR JUST DELETE THEM IF THEY ARE OLD APPT

DELETE FROM OHAPP WHERE "ID" = 22357
PACK THE TABLE

RECREATE INDEX -UNIQUE BY ID - YOU MAY WANT TO TAKE A LOOK IN AN OLD OHAPP FILE

INDEX FILE (SORT) - MAY SURE IT IS GOOD

AFTER INDEX SORT IS GOOD COPY OHAPP.ADI (ADVANTAGE DATABASE FILE) FROM AN OLD COPY INTO THE MEDISOFT PRACTICE DATA FOLDER SO ALL INDEXES ARE RECREATED. EASY WAY.

INDEX FILE (SORT) - AGAIN

DONE!!!

UPDATE USER CODE FOR ALL MAJOR TABLES

UPDATE MWDIA SET "user code" = 'SANDRA' WHERE "USER CODE" IS NULL

UPDATE MWPRO SET "user code" = 'SANDRA' WHERE "USER CODE" IS NULL

UPDATE MWINS SET "user code" = 'SANDRA' WHERE "USER CODE" IS NULL

UPDATE MWTRN SET "user code" = 'SANDRA' WHERE "USER CODE" IS NULL

UPDATE MWCAS SET "user code" = 'SANDRA' WHERE "USER CODE" IS NULL

UPDATE MWDEP SET "user code" = 'SANDRA' WHERE "USER CODE" IS NULL

UPDATE MWPAT SET "user code" = 'SANDRA' WHERE "USER CODE" IS NULL

UPDATE OHAPP SET "user code" = 'SANDRA' WHERE "USER CODE" IS NULL

MWCAS

SELECT "BILLING CODE" FROM MWCAS ORDER BY "BILLING CODE"

UPDATE MWCAS SET "BILLING CODE" = 'A' WHERE NOT "BILLING CODE" LIKE '%A%'

FIND DUPLICATE CASE NUMBER

SELECT "CASE NUMBER" AS "MYCASE", Count (mwCAS."CASE NUMBER") AS NumberOfDups FROM mwCAS GROUP BY mwCAS."CASE NUMBER" HAVING (((Count(mwCAS."CASE NUMBER"))>1));

SELECT "PRINT PATIENT STATEMENTS" FROM MWCAS ORDER BY "PRINT PATIENT STATEMENTS"

UPDATE MWCAS SET "PRINT PATIENT STATEMENTS" = TRUE WHERE "PRINT PATIENT STATEMENTS" = FALSE
SELECT "date created" FROM MWCAS ORDER BY "date created"

SELECT "user code" FROM MWCAS ORDER BY "user code"

UPDATE MWCAS SET "user code" = 'SANDRA' WHERE "USER CODE" IS NULL

MWPRO

update mwpro set "Code 1" = trim("Code 1"), "Code 2" = trim("Code 2"), "Code 3" = trim("Code 3")

MWINS

UPDATE MWINS SET "INSURED SIGNATURE ON FILE" = 'Signature on file', "PHYSICIAN SIGNATURE ON FILE" = 'Signature on file',"SIGNATURE ON FILE" = 'Signature on file', "DEFAULT WITHHOLD CODE" = 'INSWITHHLD', "DEFAULT DEDUCTIBLE CODE" = 'DEDUCTIBLE',"PRINT PINS ON INS FORM" = 'N', "DEFAULT TAKE BACK CODE" = 'INSTAKEBAK', "PRACTICE ID NUMBER" = '061552327' WHERE "DEFAULT TAKE BACK CODE" IS NULL

UPDATE MWINS SET "PRACTICE ID NUMBER" = '061552327'

UPDATE MWINS SET "TYPE" = 'Other', "Procedure Code Set" = '1', "Diagnosis Code Set" = '1', "Signature On File" = 'Signature on file', "Default Billing Method" = 'Paper', "emc receiver" = '00', "Print PINs on ins form" = 'N', "Physician Signature on File" = 'Print Name', "Insured Signature on file" = 'Signature on file', "Inactive" = false

MWDEP

SELECT "deposit date" FROM mwdep ORDER BY "deposit date"

SELECT "ENTRY NUMBER" FROM mwdep ORDER BY "ENTRY NUMBER"

FIND DUPLICATE ENTRY NUMBER

SELECT "ENTRY NUMBER" AS "MYCASE", Count (MWDEP."ENTRY NUMBER") AS NumberOfDups FROM mwDEP GROUP BY mwDEP."ENTRY NUMBER" HAVING (((Count(MWDEP."ENTRY NUMBER"))>1));

SELECT "PAYOR TYPE" FROM mwdep ORDER BY "PAYOR TYPE"

SELECT "PAYMENT METHOD" FROM mwdep ORDER BY "PAYMENT METHOD"

SELECT "PAYMENT AMOUNT" FROM mwdep ORDER BY "PAYMENT AMOUNT"

SELECT "PAYMENT CODE" FROM mwdep ORDER BY "PAYMENT CODE"

SELECT "UNAPPLIED AMOUNT" FROM mwdep ORDER BY "UNAPPLIED AMOUNT"

MWPAT

DUPLICATE PATIENTS

SELECT "CHART NUMBER" AS "MYCASE", Count (MWPAT."CHART NUMBER") AS NumberOfDups FROM MWPAT GROUP BY MWPAT."CHART NUMBER" HAVING (((Count(MWPAT."CHART NUMBER"))>1));

UPDATE MWPAT SET "SIGNATURE ON FILE" = TRUE WHERE "SIGNATURE ON FILE" <> TRUE

UPDATE MWPAT SET "COUNTRY" = 'USA' WHERE "COUNTRY" <> 'USA'

UPDATE MWPAT SET "ASSIGNED PROVIDER" = 'SLM' WHERE NOT "ASSIGNED PROVIDER" LIKE '%SLM%'

UPDATE MWPAT SET "ASSIGNED PROVIDER" = 'SLM' WHERE "ASSIGNED PROVIDER" is null

UPDATE MWPAT SET "COUNTRY" = 'USA' WHERE "COUNTRY" <> 'USA'
UPDATE MWPAT SET "Billing code" = 'A' WHERE "Billing Code" is null

SELECT "LAST PATIENT PAYMENT AMOUNT" FROM mwpat ORDER BY "LAST PATIENT PAYMENT AMOUNT"

SELECT "PATIENT REFERENCE BALANCE" FROM mwpat ORDER BY "PATIENT REFERENCE BALANCE"

SELECT "SOCIAL SECURITY NUMBER" FROM mwpat ORDER BY "SOCIAL SECURITY NUMBER"

SELECT "LAST NAME", "FIRST NAME", "sex" FROM MWPAT WHERE "SEX" IS NULL order by "first name"

SELECT count("LAST NAME") as mycount FROM MWPAT WHERE "SEX" IS NULL

update mwpat set "sex" = 'Female' where "sex" is null

Modify first and last name case to title case

select ucase(left(rtrim("first name"),1)) + RIght(LCASE("first name"),14), ucase(left(rtrim("last name"),1)) + RIght(LCASE("last name"),19), "last name", "First Name" from mwpat order by "last name"

update mwpat set "first name" = ucase(left(rtrim("first name"),1)) + RIght(LCASE("first name"),14), "Last Name"=ucase(left(rtrim("last name"),1)) + RIght(LCASE("last name"),19)

MWPAX

select * from mwpax order by "Payment amount"

select * from mwpax order by "dEPOSIT ID"

select * from mwpax order by "PAYMENT REFERENCE"

MWTRN CLEAN UP

BACKUP FILE FIRST

PACK THE DATA

FIND INVALID DOCUMENT NUMBER IN TRANSACTION TABLE

select "Chart Number", "Document Number", "Case Number", "Claim Number", "Date From", "Description", "Attending Provider", "Procedure Code", "Procedure Description", "Diagnosis Code 1", "Diagnosis Code 2", "Diagnosis Code 3", "Diagnosis Code 4", "Transaction Type", "Units", "Amount", "Deposit ID", "Check Number", "Adjustment Amount", "Guarantor Amount Paid", "Insurance 1 Amount Paid", "Insurance 2 Amount Paid", "Insurance 3 Amount Paid" from mwtrn where length(rtrim("Document number")) < 10 order by "document number"

select count("Document Number"), "document number" from mwtrn where length(rtrim("Document number")) < 10 group by "Document Number" order by "document number"

UPDATE MWTRN SET "UNITS" = 1 WHERE "UNITS" = 0

SELECT "DATE FROM", "DATE TO", "DATE CREATED" FROM MWTRN ORDER BY "DATE FROM"

WRONG DATE - GREATER THAN TODAY

SELECT "DATE FROM", "DATE TO", "DATE CREATED", "CHART NUMBER", "CASE NUMBER", "DOCUMENT NUMBER", "PROCEDURE CODE", "AMOUNT", "UNITS", "DIAGNOSIS CODE 1", "DIAGNOSIS CODE 2", "DIAGNOSIS CODE 3", "DIAGNOSIS CODE 4", "MODIFIER 1", "MODIFIER 2" FROM MWTRN WHERE "DATE FROM" > '2/17/2007' ORDER BY "DATE FROM"

WRONG PLACE OF SERVICE

SELECT "PLACE OF SERVICE", "DATE FROM", "DATE TO", "DATE CREATED", "CHART NUMBER", "CASE NUMBER", "DOCUMENT NUMBER", "PROCEDURE CODE", "AMOUNT", "UNITS", "DIAGNOSIS CODE 1", "DIAGNOSIS CODE 2", "DIAGNOSIS CODE 3", "DIAGNOSIS CODE 4", "MODIFIER 1", "MODIFIER 2" FROM MWTRN WHERE "PLACE OF SERVICE" <> '21' AND "PLACE OF SERVICE" <> '22' AND "PLACE OF SERVICE" <> '23' AND "PLACE OF SERVICE" <> '24' AND "PLACE OF SERVICE" <> '11' ORDER BY "PLACE OF SERVICE"

FOR SOME REASON unapplied amount turns into a number that is $0.00 but computer reads it as $0.0000001 or something like that causing problems.

select "Unapplied Amount" from mwtrn where "Unapplied Amount" > 0.00 and "Unapplied Amount" < 0.01
update mwtrn set "Unapplied Amount" = 0 where "Unapplied Amount" > 0.00 and "Unapplied Amount" < 0.01

FOR SOME REASON there are deposits that show unapplied amount that is bigger than the payment amount in the deposit table (enter transaction screen)

select "Payment Amount", "Unapplied Amount", "Deposit Date", "Entry Number", "Payor Type", "Insurance Code", "Chart Number", "Payor Name" from mwdep where "Unapplied Amount" > "Payment Amount"

RUN FILE MAINTENANCE - RECALCULATE BALANCES - RECALCULATE UNAPPLIED AMOUNTS

SELECT "Units" FROM MWTRN ORDER BY "UNITS" (FIX IF NOT AN INTEGER)

UPDATE MWTRN SET "UNITS" = 1

SELECT "Units" FROM MWTRN (MAKE SURE IT GOT FIXED - IT TAKES A LONG TIME BECAUSE IT IS 1 NOW)

(CLEAN DATE FIELDS - HAS TO BE MANUAL UPDATE)

SELECT "DATE OF FIRST STATEMENT" FROM MWTRN WHERE NOT "DATE OF FIRST STATEMENT" IS NULL ORDER BY "DATE OF FIRST STATEMENT"

SELECT "DATE OF SECOND STATEMENT" FROM MWTRN WHERE NOT "DATE OF SECOND STATEMENT" IS NULL ORDER BY "DATE OF SECOND STATEMENT"

SELECT "DATE OF LAST STATEMENT" FROM MWTRN WHERE NOT "DATE OF LAST STATEMENT" IS NULL ORDER BY "DATE OF LAST STATEMENT"

(PAT ATTENTION TO DATE RANGE - SAMPLE IS FOR WACKY DATE)

UPDATE MWTRN SET "DATE CREATED" = '01/01/2001' WHERE NOT "DATE CREATED" IS NULL AND "DATE CREATED" BETWEEN '04/12/1700' AND '01/01/2000'

UPDATE MWTRN SET "DATE CREATED" = '01/01/2001' WHERE NOT "DATE CREATED" IS NULL AND "DATE CREATED" BETWEEN '04/12/2005'AND '01/01/3000'

SELECT * FROM MWTRN ORDER BY "DATE CREATED" (MANUALL CORRECT OR DELETE ANY OUTRAGES RECORD USING ID NUMBER)

SELECT * FROM MWTRN ORDER BY "DATE FROM"

DELETE FROM MWTRN WHERE "DATE FROM" IS NULL (NOT EVEN A DATE - DATE FROM IS REQUIRED)

(MANUAL CLEAN UP - CORRECT DATES - LOOK AT DATE TO VALUE)

SELECT "DATE FROM", "DATE TO", "DATE CREATED" FROM MWTRN ORDER BY "DATE FROM"

SELECT "DATE FROM", "DATE TO", "DATE CREATED" FROM MWTRN ORDER BY "DATE TO"

(DATE FROM AND DATE TO SHOULD BE THE SAME)

SELECT "DATE FROM", "DATE TO", "DATE CREATED" FROM MWTRN WHERE NOT "DATE FROM" = "DATE TO"

UPDATE MWTRN SET "DATE TO" = "DATE FROM" WHERE NOT "DATE FROM" = "DATE TO"

(FIX USER CODE)

SELECT "USER CODE" from MWTRN where "USER CODE" LIKE '%)%' OR "USER CODE" LIKE '%(%' OR "USER CODE" LIKE '%[%' OR "USER CODE" LIKE '%]%' OR "USER CODE" LIKE '%!%' OR "USER CODE" LIKE '%#%' OR "USER CODE" LIKE '%$%'

UPDATE MWTRN SET "USER CODE" = 'TCSP' where "USER CODE" LIKE '%)%' OR "USER CODE" LIKE '%(%' OR "USER CODE" LIKE '%U[%' OR "USER CODE" LIKE '%]%' OR "USER CODE" LIKE '%!%' OR "USER CODE" LIKE '%#%' OR "USER CODE" LIKE '%$%'

(SHOULD BE NO RECORDS)

(MANUAL CLEAN UP - JUST COPY AND PASTE A GOOD DATE - DON'T TRY TO CHANGE EACH VALUE)

SELECT "DATE MODIFIED" FROM MWTRN ORDER BY "DATE MODIFIED"

(MANUAL CLEAN UP)

SELECT "PLACE OF SERVICE" FROM MWTRN WHERE NOT "PLACE OF SERVICE" IS NULL ORDER BY "PLACE OF SERVICE"

(CLEAN UP EMTPY SPACES)

UPDATE MWTRN SET "PLACE OF SERVICE" = NULL WHERE "PLACE OF SERVICE" LIKE '% %'

(MANUAL CLEAN UP - SET TO 0 ANY CRAZY AMOUNTS)

SELECT "AMOUNT" FROM MWTRN ORDER BY "AMOUNT"

CREATE A FOLDER INTO MEDIDATA CALLED FIXED AND COPY THE FILES YOU WORKED ON ...MWTRN, MWZIP, OHAPP (INCLUDING INDEX FILE - ADI) SO IT SOMETHING HAPPENS YOU CAN GO BACK WITHOUT HAVING TO GO CRAZY ON DOING EVERYTHING OVER AGAIN

(MINUTES IS NORMALLY 0 - CHECK WITH PRACTICE - THE FOLLOWING CODE ASSUMES IT IS 0 -SETTING ALL REC TO 0)

SELECT "MINUTES" FROM MWTRN ORDER BY "MINUTES"

UPDATE MWTRN SET "MINUTES" = 0

FIND DUPLICATES BASED ON ENTRY NUMBER

SELECT "ENTRY NUMBER" FROM MWTRN GROUP BY "ENTRY NUMBER" HAVING COUNT("ENTRY NUMBER") > 1

SYSTEM WILL SHOW YOU DUPLICATE VALUES...COPY AND PASTE VALUES IN THE ENTRY NUMBER

DELETE FROM MWTRN WHERE "ENTRY NUMBER" = 17339 OR "ENTRY NUMBER" = 134314643 OR "ENTRY NUMBER" = 134267680 OR "ENTRY NUMBER" = 134243913 OR "ENTRY NUMBER" = 134240735 OR "ENTRY NUMBER" = 69269 OR "ENTRY NUMBER" = 61961 OR "ENTRY NUMBER" = 59862

FIND ANY DUPLICATES. SYSTEM WILL SHOW YOU DUPLICATE VALUES...COPY AND PASTE VALUES IN THE ENTRY NUMBER

SELECT "ENTRY NUMBER" FROM MWTRN GROUP BY "ENTRY NUMBER" HAVING COUNT("ENTRY NUMBER") > 1

DELETE DUPLICATES

DELETE FROM MWTRN WHERE "ENTRY NUMBER" = 134356262 OR "ENTRY NUMBER" = 134352878

REPEAT PROCESS UNTIL NO DUPLICATES

COPY MWTRN.ADI - INDEX FILE INTO DATA FOLDER...INDEX IS CORRUPTED AND CAN'T BE OPENED

PACK DATA

FIND DUPLICATES

SELECT "ENTRY NUMBER" FROM MWTRN GROUP BY "ENTRY NUMBER" HAVING COUNT("ENTRY NUMBER") > 1

RE-INDEX MWTRN

(AMOUNTS MAY BE SCREWY AS WELL)

SELECT "AMOUNT" FROM MWTRN WHERE ORDER BY "AMOUNT"

delete from mwtrn where "Procedure code" = 'COMMENT#'

SELECT "PROCEDURE CODE" from MWTRN where "PROCEDURE CODE" LIKE '%)%' OR "PROCEDURE CODE" LIKE '%(%' OR "PROCEDURE CODE" LIKE '%[%' OR "PROCEDURE CODE" LIKE '%]%' OR "PROCEDURE CODE" LIKE '%!%' OR "PROCEDURE CODE" LIKE '%#%' OR "PROCEDURE CODE" LIKE '%$%'

DELETE FROM MWTRN where "PROCEDURE CODE" LIKE '%)%' OR "PROCEDURE CODE" LIKE '%(%' OR "PROCEDURE CODE" LIKE '%[%' OR "PROCEDURE CODE" LIKE '%]%' OR "PROCEDURE CODE" LIKE '%!%' OR "PROCEDURE CODE" LIKE '%#%' OR "PROCEDURE CODE" LIKE '%$%'

SCREWY AMOUNTS

SELECT "Guarantor Amount Paid" FROM MWTRN WHERE "Guarantor Amount Paid" > 0 AND "Guarantor Amount Paid" < 1

DELETE FROM MWTRN WHERE "Guarantor Amount Paid" > 0 AND "Guarantor Amount Paid" < 1

SELECT "Guarantor Amount Paid" FROM MWTRN WHERE "Guarantor Amount Paid" > 350000 AND "Guarantor Amount Paid" < 1000000000000000

DELETE FROM MWTRN WHERE "Guarantor Amount Paid" > 350000 AND "Guarantor Amount Paid" < 1000000000000000

SELECT "VISIT NUMBER" FROM MWTRN WHERE "VISIT NUMBER" > 0 AND "VISIT NUMBER" < 1

COUNT HOW MANY RECORDS

SELECT COUNT("VISIT NUMBER") AS TOTAL FROM MWTRN WHERE "VISIT NUMBER" > 0 AND "VISIT NUMBER" < 1

DELETE UNWANTED RECORDS

DELETE FROM MWTRN WHERE "VISIT NUMBER" > 0 AND "VISIT NUMBER" < 1

SELECT "ATTORNEY AMOUNT PAID" FROM MWTRN WHERE "ATTORNEY AMOUNT PAID" > 0 AND "ATTORNEY AMOUNT PAID" < 1

SELECT "VISIT TOTAL IN SERIES" FROM MWTRN ORDER BY "VISIT TOTAL IN SERIES"

UPDATE MWTRN SET "vISIT TOTAL IN SERIES" = 100 FROM MWTRN WHERE "vISIT TOTAL IN SERIES" > 100

SELECT "CC ENTRY NUMBER" FROM MWTRN WHERE "CC ENTRY NUMBER" < -1 OR "CC ENTRY NUMBER" > 2

DELETE FROM MWTRN WHERE "CC ENTRY NUMBER" < -1 OR "CC ENTRY NUMBER" > 2

SELECT "ALLOWED AMOUNT" FROM MWTRN WHERE "ALLOWED AMOUNT" > 0 AND "ALLOWED AMOUNT" < 1

MANUALLY UPDATE THE AMOUNT TO 0 ANY SCREWY AMOUNTS

SELECT "ALLOWED AMOUNT" FROM MWTRN WHERE "ALLOWED AMOUNT" > 0 AND "ALLOWED AMOUNT" < 1 ORDER BY "ALLOWED AMOUNT"

SELECT "VISIT TOTAL IN SERIES" FROM MWTRN WHERE "VISIT TOTAL IN SERIES" > 0 AND "VISIT TOTAL IN SERIES" < 1

DELETE FROM MWTRN WHERE "VISIT TOTAL IN SERIES" > 0 AND "VISIT TOTAL IN SERIES" < 1

PACK DATA

FIND DUPLICATES

SELECT "ENTRY NUMBER" FROM MWTRN GROUP BY "ENTRY NUMBER" HAVING COUNT("ENTRY NUMBER") > 1

RE-INDEX MWTRN

SELECT "PROCEDURE CODE" FROM MWTRN where "PROCEDURE CODE" LIKE '%)%' OR "PROCEDURE CODE" LIKE '%(%' OR "PROCEDURE CODE" LIKE '%[%' OR "PROCEDURE CODE" LIKE '%]%' OR "PROCEDURE CODE" LIKE '%!%' OR "PROCEDURE CODE" LIKE '%#%' OR "PROCEDURE CODE" LIKE '%$%'

DELETE FROM MWTRN where "USER CODE" LIKE '%)%' OR "USER CODE" LIKE '%(%' OR "USER CODE" LIKE '%[%' OR "USER CODE" LIKE '%]%' OR "USER CODE" LIKE '%!%' OR "USER CODE" LIKE '%#%' OR "USER CODE" LIKE '%$%'

SELECT "USER CODE" FROM MWTRN where "USER CODE" IS NULL

UPDATE MWTRN SET "USER CODE" = 'TCSP' where "USER CODE" IS NULL

MANUAL UPDATE - TO ZERO

SELECT "INSURANCE 1 AMOUNT PAID" FROM MWTRN WHERE "INSURANCE 1 AMOUNT PAID" < 1 ORDER BY "INSURANCE 1 AMOUNT PAID"

SELECT "INSURANCE 2 AMOUNT PAID" FROM MWTRN WHERE "INSURANCE 2 AMOUNT PAID" < 1 ORDER BY "INSURANCE 2 AMOUNT PAID"

SELECT "INSURANCE 3 AMOUNT PAID" FROM MWTRN WHERE "INSURANCE 3 AMOUNT PAID" < 1 ORDER BY "INSURANCE 3 AMOUNT PAID"

SELECT "ATTENDING PROVIDER" FROM MWTRN where "ATTENDING PROVIDER" LIKE '%)%' OR "ATTENDING PROVIDER" LIKE '%(%' OR "ATTENDING PROVIDER" LIKE '%[%' OR "ATTENDING PROVIDER" LIKE '%]%' OR "ATTENDING PROVIDER" LIKE '%!%' OR "ATTENDING PROVIDER" LIKE '%#%' OR "ATTENDING PROVIDER" LIKE '%$%'

SELECT "VISIT NUMBER" FROM MWTRN WHERE "VISIT NUMBER" < 1 ORDER BY "VISIT NUMBER"

UPDATE MWTRN SET "VISIT NUMBER" = 0 WHERE "VISIT NUMBER" < 1

select "Date From","chart number", "amount", "Units" from mwtrn where "Units" > 1 order by "Units"

HOW TO FIX UNAPPLIED AMOUNTS THAT SHOW IN TRANSACTION SCREEN
IT NORMALLY HAPPENS IN VERSION 10. AMOUNT APPLIED IS CORRECT BUT AMOUNT TO BE APPLIED IS SHORT THE SAME AMOUNT THAT IS UNAPPLIED. IT IS NORMALLY WITHIN CENTS LIKE $0.01. ALL WE NEED TO DO IS TO INCREASE AMOUNT TO BE APPLIED (INS AMOUNT CHECK OR INS ADJUSTMENT AMOUNT) BY THE UNAPPLIED AMOUNT AS FOLLOWS:

--- TEST WITH ONLY PATIENT FIRST-------------------------------------------------------------------------------------------------------------

select "Chart Number", "Claim Number", "Case Number", "Date From", "Description", "Attending Provider", "Procedure Code", "Transaction Type", "Units", "Amount", "Unapplied Amount" from mwtrn where "Chart Number" = '00011295' and "Unapplied Amount" > 0 AND "Unapplied Amount" < 1

First Step:
UPDATE mwtrn set "AMOUNT" = "AMOUNT" + ("Unapplied Amount" * -1) where "Chart Number" = '00011295' and "Unapplied Amount" > 0 AND "Unapplied Amount" < 1

Second Step:
UPDATE mwtrn set "Unapplied Amount" = 0 where "Chart Number" = '00011295' and "Unapplied Amount" > 0 AND "Unapplied Amount" < 1

Third Step: (View if it worked)
select "Chart Number", "Claim Number", "Case Number", "Date From", "Description", "Attending Provider", "Procedure Code", "Transaction Type", "Units", "Amount", "Unapplied Amount" from mwtrn where "Chart Number" = '00011295' and "Unapplied Amount" > 0 AND "Unapplied Amount" < 1

--- END OF ONE PATIENT TEST ----------------------------------------------------------------------------------------------------------------------------

--- ALL PATIENTS NOW---------------------------------------------------------------------------------------------------------------------------------------

First Step:
UPDATE mwtrn set "AMOUNT" = "AMOUNT" + ("Unapplied Amount" * -1) where "Unapplied Amount" > 0 AND "Unapplied Amount" < 1

Second Step:
UPDATE mwtrn set "Unapplied Amount" = 0 where "Unapplied Amount" > 0 AND "Unapplied Amount" < 1

--- END OF ALL PATIENTS-----------------------------------------------------------------------------------------------------------------------------------

TO GET A FIELD NAME LIST USE QUERY CREATION IN MS ACCESS, ADD FIELDS TO GRID AND THEN VIEW SQL CODE

IMPORT INTO MWPAT USING A MWPAT SIMILAR TABLE
SQL SAMPLE INSERT INTO

insert into mwpat ("Chart number", "Last Name", "First Name", "Middle Initial", "Street 1", "Street 2", "City", "State", "Zip Code", "Phone 1", "Phone 2", "Phone 3", "Phone 4", "Phone 5", "Signature On File", "Work Phone", "Sex", "Social Security Number", "Date of Birth", "Contact Name", "Contact Phone", "Patient Type", "assigned Provider", "Flag", "Patient Indicator" ) select "Chart number", "Last Name", "First Name", "Middle Initial", "Street 1", "Street 2", "City", "State", "Zip Code", "Phone 1", "Phone 2", "Phone 3", "Phone 4", "Phone 5", "Signature On File", "Work Phone", "Sex", "Social Security Number", "Date of Birth", "Contact Name", "Contact Phone", "Patient Type", "assigned Provider", "Flag", "Patient Indicator" from "C:\Documents and Settings\medscan.OFFICE\Desktop\Joe\mwpat.adt"

MWRPH Find Duplicate Referring Providers Doctors

SELECT (TRIM(UPPER("LAST NAME")) + ', ' + TRIM(UPPER("FIRST NAME"))) AS "MYNAME", Count(TRIM(UPPER("LAST NAME")) + ', ' + TRIM(UPPER("FIRST NAME"))) AS "NumberOfDups" FROM MWRPH GROUP BY (TRIM(UPPER("LAST NAME")) + ', ' + TRIM(UPPER("FIRST NAME"))) HAVING count((TRIM(UPPER("LAST NAME")) + ', ' + TRIM(UPPER("FIRST NAME")))) > 1 ORDER BY "NumberOfDups";

IMPORT INTO MWRPH USING A MWRPH SIMILAR TABLE
SQL SAMPLE INSERT INTO

EXPORT DATA FROM DESIRED TABLE TO AN ADT TABLE ON C DRIVE
USE SQL TO IMPORT INTO THE TABLE
TO GET THE NAME OF FIELDS EXPORT TABLE TO HTML - VIEW SOURCE CODE SEARCH AND REPLACE WITH " AND ",

** MAKE SURE THAT YOU EXPORT DATA TO A NEW ADT TABLE PUT IT ON C DRIVE. TABLE WILL NOT BE OPENED IF PART OF DATA DICTIONARY

insert into mwrph ("Code", "Last Name", "First Name", "Middle Initial", "Credentials", "Street 1", "Street 2", "City", "State", "Zip Code", "Phone", "Fax", "SSN or Fed Tax ID", "Federal Tax ID Indicator", "License Number", "Signature on File", "Medicare PIN", "Medicaid PIN", "Champus PIN", "Blue Cross/Shield PIN", "Commercial PIN", "Group PIN", "HMO PIN", "PPO PIN", "Medicare Group ID", "Medicaid Group ID", "BC/BS Group ID", "Other Group ID", "EMC ID", "Medicare
Particp Provider", "UPIN", "Extra 1", "Extra 2", "Specialty", "Security Level", "SB Number", "SB Report File", "SOF Date", "User Code", "Date Created", "TAT Number", "CLIA Number", "National Provider Identifier", "DEA Registration", "EMail", "Office", "Cell", "Date Modified", "Inactive", "EntityType" ) select "Code", "Last Name", "First Name", "Middle
Initial", "Credentials", "Street 1", "Street 2", "City", "State", "Zip Code", "Phone", "Fax", "SSN or Fed Tax ID", "Federal Tax ID Indicator", "License Number", "Signature on File", "Medicare PIN", "Medicaid PIN", "Champus PIN", "Blue Cross/Shield PIN", "Commercial PIN", "Group PIN", "HMO PIN", "PPO PIN", "Medicare Group ID", "Medicaid Group ID", "BC/BS Group ID", "Other Group ID", "EMC ID", "Medicare Particp Provider", "UPIN", "Extra 1", "Extra 2", "Specialty", "Security Level", "SB Number", "SB Report File", "SOF Date", "User Code", "Date Created", "TAT Number", "CLIA Number", "National Provider Identifier", "DEA Registration", "EMail", "Office", "Cell", "Date Modified", "Inactive", "EntityType" from "C:\Diegorph.adt"

JOIN BETWEEN MWCAS AND MWINS. FIND CODES THAT DO NOT MATCH BETWEEN THE CASE AND INSURANCE FILE. CONVERSION CLEANED UP CODES THAT WERE NOT GOOD.

SELECT "INSURANCE CARRIER #1" FROM MWCAS WHERE "INSURANCE CARRIER #1" NOT IN (SELECT "CODE" FROM MWINS WHERE "INSURANCE CARRIER #1" = "CODE") order by "INSURANCE CARRIER #1"

SELECT "INSURANCE CARRIER #2" FROM MWCAS WHERE "INSURANCE CARRIER #2" NOT IN (SELECT "CODE" FROM MWINS WHERE "INSURANCE CARRIER #2" = "CODE") order by "INSURANCE CARRIER #2"

SELECT "INSURANCE CARRIER #3" FROM MWCAS WHERE "INSURANCE CARRIER #3" NOT IN (SELECT "CODE" FROM MWINS WHERE "INSURANCE CARRIER #3" = "CODE") order by "INSURANCE CARRIER #3"

BTO FIX THE CASE WITH A VALID MWINS CODE USE THE FOLLOWING EXAMPLE WHERE 'TEAM' VALUE IS REPLACED WITH 'TEAMU' VALUE

UPDATE MWCAS SET "INSURANCE CARRIER #2" = 'TEAMU' WHERE "INSURANCE CARRIER #2" = 'TEAM'

IMPORT INTO MWCAS USING A MWCAS SIMILAR TABLE
SQL SAMPLE INSERT INTO

insert into mwcas("Chart Number", "Description", "Guarantor", "Print Patient Statements", "Work Phone", "Policy Number #1", "Group Number #1", "Diagnosis 1", "Diagnosis 2", "Diagnosis 3", "Diagnosis 4", "Copayment Amount", "Assigned Provider") select "Chart Number", "Description", "Guarantor", "Print Patient Statements", "Work Phone", "Policy Number #1", "Group Number #1", "Diagnosis 1", "Diagnosis 2", "Diagnosis 3", "Diagnosis 4", "Copayment Amount", "Assigned Provider" from "C:\Documents and Settings\medscan.OFFICE\Desktop\Joe\mwcas.adt"

FIX PHONE NUMBER FORMATS - PHONE FORMAT (203)555-555

Select "USER CODE", "pHONE 1", '(' + LEFT("pHONE 1", 3) + ')' + LEFT(RIGHT(RTRIM("pHONE 1"), 8), 3) + '-' + RIGHT(RTRIM("PHONE 1"), 4), "DATE MODIFIED" from mwpat WHERE (length("phone 1") > 9 and "Phone 1" not like '%(%' )

Setup a join SQL statement between two tables and update one of the tables based on data from the second table

update mwcas set mwcas."Billing Code" = 'NF' FROM mwcas m9, mwccd m11 WHERE m9."case number"=m11."case number" AND m11."ClaimType" = 'NO-FAULT'

update mwpro set mwpro."Amount B" = m11."lc" FROM mwpro m9, "c:\fees2.adt" m11 WHERE m9."Code 1"=m11."procedure code"

FIND DUPLICATE VALUES

SELECT "Code 1" AS "Code 1 Field", Count (mwpro."Code 1") AS NumberOfDups FROM mwpro GROUP BY mwpro."Code 1" HAVING (((Count(mwpro."Code 1"))>1));

CLEAN DELETE DUPLICATE PATIENTS BASED ON SSN, FIRST AND LAST NAME

' ************** BACKUP BACKUP BACKUP DATA BEFORE DOING ANYTHING ******

' *** START DELETE DUPLICATE PATIENTS BASED ON FIRST, LAST NAME, SOCIAL SECURITY****************

' To find the number of duplicates for each record
SELECT "social Security Number" AS "MyDup", Count ("social Security Number") AS NumberOfDups FROM mwPAT GROUP BY "social Security Number" HAVING (((Count("social Security Number"))>1));

' Find duplicates

SELECT * FROM mwpat WHERE ("Last Name" + "First Name" + "Social Security Number") In (SELECT ("Last Name" + "First Name" + "Social Security Number") as MyDupValue FROM mwpat As Tmp GROUP BY "Last Name" + "First Name" + "Social Security Number" HAVING Count(*)>1 )
ORDER BY ("Last Name" + "First Name" + "Social Security Number");

' RIGHT CLICK ON RESULTS AND EXPORT RESULTS TO A TABLE C:\mwPatDup2.ADT

' ************** BACKUP BACKUP BACKUP DATA BEFORE DOING ANYTHING ******

' TAG DUPLICATE RECORDS SO WE CAN DELETE DUPLICATES FROM ORIGINAL TABLE

UPDATE mwpat SET "Street 1" = 'DUPLICATE DUPLICATE'
WHERE (("Last Name" + "First Name" + "Social Security Number") In (SELECT ("Last Name" + "First Name" + "Social Security Number") as MyDupValue FROM "mwpat" As Tmp GROUP BY "Last Name" + "First Name" + "Social Security Number" HAVING Count(*)>1 ));

' ************** BACKUP BACKUP BACKUP DATA BEFORE DOING ANYTHING ******

' DELETE DUPLICATES FROM ORIGINAL TABLE

DELETE FROM MWPAT WHERE "Street 1" = 'DUPLICATE DUPLICATE'

' VIEW the Chart Number AS an Integer
select convert("Chart Number", SQL_INTEGER) as MaxChartNumber, from "c:\mwPatDup2.adt"

' FIND THE HIGHTEST CHART WE WANT TO KEEP
SELECT Max(convert("Chart Number", SQL_INTEGER)) as MaxChartNumber, "social Security Number", max(Trim("Last Name") + ', ' + trim("First Name")) AS "MyDup", Count ("social Security Number") AS NumberOfDups FROM "c:\mwPatDup2.adt" GROUP BY "social Security Number" HAVING (((Count("social Security Number"))>1));

' RIGHT CLICK ON RESULTS AND EXPORT RESULTS TO A TABLE C:\mwPatToKeep.ADT

' VIEW MATCHING RECORDS BETWEEN THE DUPLICATES AND THE ONE WE WANT TO KEEP
Select * from "c:\mwPatDup2.adt" PatDupTable, "c:\mwPatToKeep.adt" PatToKeep WHERE Convert("PatDupTable"."Chart Number", SQL_INTEGER) = Convert("PatToKeep"."MaxChartNumber", SQL_INTEGER)

' NOW TAG RECORDS ON C:\mwPatDup2.adt that WE WANT TO KEEP - USING "USER CODE" FIELD TO TAG WANTED RECORDS
UPDATE "C:\mwPatDup2.adt" set "USER CODE" = 'MYKEEP' from "c:\mwPatDup2.adt" PatDupTable, "c:\mwPatToKeep.adt" PatToKeep WHERE Convert("PatDupTable"."Chart Number", SQL_INTEGER) = Convert("PatToKeep"."MaxChartNumber", SQL_INTEGER)

' DELETE RECORDS WE DO NOT WANT TO KEEP
DELETE FROM "C:\mwPatDup2.adt" WHERE "USER CODE" <> 'MYKEEP'

' VIEW RECORDS THAT I WANT TO KEEP
SELECT * FROM "C:\MWPATDUP2.ADT"

' SET USER CODE BACK TO SOMETHING MORE LIKE A USER
UPDATE "C:\MWPATDUP2.ADT" SET "USER CODE" = 'TCSP'

' INSERT UNIQUE RECORDS INTO MWPAT
insert into mwpat select * from "C:\mwPatDup2.adt"

' If running above query gives you an error because of duplicate records. Run the following
' Check to see if both tables contain records that match.
SELECT * FROM "c:\mwPatDup2.adt" PatDupTable, mwpat WHERE "PatDupTable"."Chart Number" ="mwpat"."Chart Number"

**** END DELETE DUPLICATE PATIENTS BASED ON FIRST, LAST NAME, SOCIAL SECURITY ************************

CLEAN DUPLICATE PATIENTS BASED ON SSN

' ************** BACKUP BACKUP BACKUP DATA BEFORE DOING ANYTHING ******

' ********************************* START DELETE DUPLICATE PATIENTS BASED ON SOCIAL SECURITY ONLY **********************************

' SET SSN EQUAL TO BLANK IF VALUE IN SSN IS = 000-00-0000 OTHERWISE YOU WILL BE DELETING VALID PATIENTS
UPDATE MWPAT SET "SOCIAL SECURITY NUMBER" = '' WHERE "SOCIAL SECURITY NUMBER" = '000-00-0000' OR "SOCIAL SECURITY NUMBER" = '000000000'

' To find the number of duplicates for each record
SELECT "social Security Number" AS "MyDup", Count ("social Security Number") AS NumberOfDups FROM mwPAT WHERE "Social Security Number" <> '' GROUP BY "social Security Number" HAVING (((Count("social Security Number"))>1));

' Find duplicates

SELECT * FROM mwpat WHERE ("Social Security Number") in (SELECT ("Social Security Number") as MyDupValue FROM mwpat As Tmp WHERE "Social Security Number" <> '' GROUP BY "Social Security Number" HAVING Count(*)>1 ) ORDER BY ("Social Security Number");

' RIGHT CLICK ON RESULTS AND EXPORT RESULTS TO A TABLE C:\mwPatDupSSN.ADT

' ************** BACKUP BACKUP BACKUP DATA BEFORE DOING ANYTHING ******

' TAG DUPLICATE RECORDS SO WE CAN DELETE DUPLICATES FROM ORIGINAL TABLE

UPDATE mwpat SET "Street 1" = 'DUPLICATE DUPLICATE'
WHERE (("Social Security Number") In (SELECT ("Social Security Number") as MyDupValue FROM "mwpat" As Tmp GROUP BY "Social Security Number" HAVING Count(*)>1 ));

' ************** BACKUP BACKUP BACKUP DATA BEFORE DOING ANYTHING ******

' DELETE DUPLICATES FROM ORIGINAL TABLE

DELETE FROM MWPAT WHERE "Street 1" = 'DUPLICATE DUPLICATE'

' VIEW the Chart Number AS an Integer
select convert("Chart Number", SQL_INTEGER) as MaxChartNumber, from "c:\mwPatDupSSN.adt"

' FIND THE HIGHTEST CHART WE WANT TO KEEP
SELECT Max(convert("Chart Number", SQL_INTEGER)) as MaxChartNumber, "social Security Number", max(Trim("Last Name") + ', ' + trim("First Name")) AS "MyDup", Count ("social Security Number") AS NumberOfDups FROM "c:\mwPatDupSSN.adt" GROUP BY "social Security Number" HAVING (((Count("social Security Number"))>1));

' RIGHT CLICK ON RESULTS AND EXPORT RESULTS TO A TABLE c:\mwPatSSNToKeep.adt

' VIEW MATCHING RECORDS BETWEEN THE DUPLICATES AND THE ONE WE WANT TO KEEP
Select * from "c:\mwPatDupSSN.adt" PatDupTable, "c:\mwPatSSNToKeep.adt" PatToKeep WHERE Convert("PatDupTable"."Chart Number", SQL_INTEGER) = Convert("PatToKeep"."MaxChartNumber", SQL_INTEGER)

' NOW TAG RECORDS ON C:\mwPatDupSSN.adt that WE WANT TO KEEP - USING "USER CODE" FIELD TO TAG WANTED RECORDS
UPDATE "C:\mwPatDupSSN.adt" set "USER CODE" = 'MYKEEP' from "c:\mwPatDupSSN.adt" PatDupTable, "c:\mwPatSSNToKeep.adt" PatToKeep WHERE Convert("PatDupTable"."Chart Number", SQL_INTEGER) = Convert("PatToKeep"."MaxChartNumber", SQL_INTEGER)

' DELETE RECORDS WE DO NOT WANT TO KEEP
DELETE FROM "C:\mwPatDupSSN.adt" WHERE "USER CODE" <> 'MYKEEP'

' VIEW RECORDS THAT I WANT TO KEEP
SELECT * FROM "C:\mwPatDupSSN.ADT"

' SET USER CODE BACK TO SOMETHING MORE LIKE A USER
UPDATE "C:\mwPatDupSSN.ADT" SET "USER CODE" = 'TCSP'

' INSERT UNIQUE RECORDS INTO MWPAT
insert into mwpat select * from "C:\mwPatDupSSN.adt"

' If running above query gives you an error because of duplicate records. Run the following
' Check to see if both tables contain records that match.
SELECT * FROM "c:\mwPatDupSSN.adt" PatDupTable, mwpat WHERE "PatDupTable"."Chart Number" ="mwpat"."Chart Number"

'******** END DELETE DUPLICATE PATIENTS BASED ON SOCIAL SECURITY ONLY **********************************

' TO UPDATE RECORDS BASED ON THE VALUES IN ANOTHER TABLE (JOIN)
UPDATE "C:\mwPatDupSSN.adt" set "Sex" = "mwpat"."Sex" from "c:\mwPatDupSSN.adt" PatDupTable, mwpat WHERE "PatDupTable"."Chart Number" ="mwpat"."Chart Number"

DELETE FROM customer WHERE customer.id NOT IN (SELECT custid FROM orders WHERE orders.custid = customer.id )

DELETE FROM "c:\MWPAT.ADT" WHERE "CHART NUMBER" NOT IN (SELECT "FIELD0" FROM "C:\MWPATKEEP.ADT" WHERE "CHART NUMBER" = "FIELD0")

HOW TO DELETE PATIENTS THAT HAVE NOT BEEN IN THE OFFICE IN THE PAST 3 YEARS. THIS WAS A CONVERSION FROM PMS - CREATED REPORTS USING REPORT / SCAN / OPTION 47 / RUN SCAN / OPTION 5 ANY CODE / TO TEXT FILE / CLEANED UP FILE TO GET CHART NUMBER FOR PATIENTS THAT WHERE IN THE OFFICE FOR A TIME PERIOD / NEEDED TO RUN THE PROCEDURE FOR TWO PROVIDERS PNL AND CPNL THEN DID THE FOLLOWING DATA TAGGING AND CLEAN UP

' KEEP NEW RECORDS
UPDATE MWPAT SET "PATIENT ID #2" = 'NEEDIT' WHERE CONVERT("DATE CREATED", SQL_DATE) >= CONVERT('01/01/2006', SQL_DATE)

' TAG OTHER PATIENTS WE WANT TO KEEP BUT KEEP IN MIND THAT THE DATE PATIENTS WERE MODIFIED IS REFLECTED
' FROM THE DATE THE FILE MAINTENANCE WAS RAN - VIEW THEM FIRST TO MAKE SURE DATE IS ACCURATE

SELECT "DATE MODIFIED" FROM MWPAT where CONVERT("DATE MODIFIED", SQL_DATE) >= CONVERT('08/19/2006', SQL_DATE) ORDER BY "DATE MODIFIED"
UPDATE MWPAT SET "PATIENT ID #2" = 'NEEDIT' WHERE CONVERT("DATE MODIFIED", SQL_DATE) >= CONVERT('08/19/2006', SQL_DATE)

' NEED TO TAKE A LOOK AT OTHER RECORDS YOU MAY WANT TO KEEP SUCH PATIENTS THAT HAVE MADE PAYMENTS
SELECT "PATIENT ID #2", "LAST PATIENT PAYMENT DATE", "LAST PATIENT PAYMENT AMOUNT", "PATIENT_REMAINDER_BALANCE",