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 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%'

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")

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

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"

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"

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"

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"

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

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)

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 '%$%'

(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

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"

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

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

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

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

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 '%$%'

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 ----------------------------------------------------------------------------------------------------------------------------
 

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

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

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 "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"

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

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"

' ************** 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 ************************




 

' ************** 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")

' 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", "SOF DATE" FROM MWPAT WHERE NOT "LAST PATIENT PAYMENT DATE" IS NULL ORDER BY "LAST PATIENT PAYMENT DATE" DESC
UPDATE MWPAT SET "PATIENT ID #2" = 'NEEDIT' WHERE NOT "LAST PATIENT PAYMENT DATE" IS NULL
UPDATE MWPAT SET "PATIENT ID #2" = 'NEEDIT' WHERE NOT "LAST PATIENT PAYMENT AMOUNT" IS NULL

SELECT "PATIENT ID #2", "USER CODE", "LAST PATIENT PAYMENT DATE", "LAST PATIENT PAYMENT AMOUNT", "PATIENT_REMAINDER_BALANCE", "SOF DATE" FROM MWPAT WHERE NOT "PATIENT ID #2" LIKE '%NEEDIT%' ORDER BY "USER CODE"
UPDATE MWPAT SET "PATIENT ID #2" = 'NEEDIT' WHERE NOT "PATIENT ID #2" LIKE '%NEEDIT%' AND ("USER CODE" = 'BJD' OR "USER CODE" = 'DIEGORA' OR "USER CODE" = 'TAP' OR "USER CODE" = 'JH')

SELECT "PATIENT ID #2", "PAYMENT PLAN", "EMPLOYMENT STATUS", "USER CODE", "COUNTRY", "LAST PATIENT PAYMENT DATE", "LAST PATIENT PAYMENT AMOUNT", "PATIENT_REMAINDER_BALANCE", "SOF DATE" FROM MWPAT WHERE NOT "PATIENT ID #2" LIKE '%NEEDIT%' ORDER BY "PAYMENT PLAN"


' uSE TABLE THAT CONTAINS PATIENTS THAT HAVE BEEN IN THE OFFICE SINCE 2002
Use import into a C:\ system will automatically create a table with ADT if none specified
Use export to existing table as long as not in a dictionary it shall be ok.

update mwpat set "PATIENT ID #2" = 'NEEDIT' from mwpat, "c:\mwpatTokeep" mymwpatTokeep WHERE mwpat."Chart Number" = "mymwpatTokeep"."Chart Number"

' VIEW RECORDS TO DELETE - MAKE SURE THE RECORDS DO LOOK LIKE PATIENTS THAT HAVE
' NOT BEEN IN THE OFFICE - GO INTO OLD SYSTEM AND CHECK A COUPLE OF PATIENTS
' BEFORE DELETING THE PATIENTS
SELECT * FROM MWPAT WHERE NOT "PATIENT ID #2" LIKE '%NEED%'
DELETE FROM MWPAT WHERE NOT "PATIENT ID #2" LIKE '%NEED%'

mwcas - Set Case Default Values

UPDATE MWCAS SET Description = 'My Desription', [Assigned Provider] = 'MYPRO', [Print Patient Statements] = True, [Insured Relationship #1] = 'Self', [Accept Assignment #1] = True, [Percent Covered A #1] = 100, [Percent Covered B #1] = 100, [Percent Covered C #1] = 100, [Percent Covered D #1] = 100, [Percent Covered E #1] = 100, [Percent Covered F #1] = 100, [Percent Covered G #1] = 100, [Percent Covered H #1] = 100, [Policy is Capitated] = False, [Copayment Amount] = 0, [Insured Relationship #2] = 'Self', [Accept Assignment #2] = True, [Percent Covered A #2] = 100, [Percent Covered B #2] = 100, [Percent Covered C #2] = 100, [Percent Covered D #2] = 100, [Percent Covered E #2] = 100, [Percent Covered F #2] = 100, [Percent Covered G #2] = 100, [Percent Covered H #2] = 100, [Policy #2 Crossover Claim] = False, [Insured Relationship #3] = 'Self', [Accept Assignment #3] = 100, [Percent Covered A #3] = 100, [Percent Covered B #3] = 100, [Percent Covered C #3] = 100, [Percent Covered D #3] = 100, [Percent Covered E #3] = 100, [Percent Covered F #3] = 100, [Percent Covered G #3] = 100, [Percent Covered H #3] = 100, [Related to Employment] = False, [Same or Similar Symptoms] = False, Emergency = False, EPSDT = False, [Family Planning] = False, [Outside Lab Work] = 0, [Lab Charges] = 0, [Visit Series ID] = 'A', [Visit Series Counter] = 0, [Authorized No of Visits] = 100, [Billing Code] = 'A', [Price Code] = 'A', [Cash Case] = False, [Case Closed] = False, [Annual Deductible] = 0, [Radiographs enclosed] = 0, Prosthesis = 0, Orthodontics = 0, [Length of Treatment] = 0, [Medical Plan Coverage] = 0, [Pregnancy Indicator] = 0, [Homebound indicator] = 0, [Deductible Met] = 0, Certification_Code_Applies = 0, [User Code] = 'TCSP', [Date Created] = '2/10/1969', [Demonstration_Code] = 0, [Insurance_Type_Code] = 0 where "[User Code]" is null and "[Assigned Provider]" is null and "[Description]" = 'BALANCE FORWARD ONLY';

OHAPP - Office Hours

Compare Appoinments chart number to mwpat chart number
to see if any chart in the appointment book is missing from mwpat table.  Name has a comma

SELECT ("OHAPP"."Chart Number") AS "FirstOfChart Number", ("OHAPP"."Phone") AS "FirstOfPhone", "OHAPP"."Name", Left(trim("Name"),POSITION( ',' IN "Name")-1) AS LName, RIGHT(trim("Name"),(Length("Name") - (POSITION( ',' IN "Name")))-1) AS FName
FROM OHAPP LEFT JOIN MWPAT ON "OHAPP"."Chart Number" = "MWPAT"."Chart Number"
WHERE ((("MWPAT"."Chart Number") Is Null))
GROUP BY "OHapp"."name", "Ohapp"."Chart Number", "Ohapp"."Phone" HAVING ((("OHAPP"."Name") Not In ('OFFICE CLOSED','No Patients','MRI','No One (Suppressed)','LUNCH','CT SCAN','X-RAYS','SONOGRAM','Work-in','NO TECH','Undefined','On Vacation','Suppress Slot')));

COMPARE THE MWPAT CHART NUMBERS TO C:\TOIMPORT.ADT TO SEE IF CHART ALREADY EXISTS BEFORE IMPORTING INTO MWPAT.  CLEAN UP DATA MANUALLY

select "Chart Number", "firstofchart number" from mwpat mm, "c:\toimport.adt" mp where trim(ucase("chart number")) = trim(ucase("firstofchart number"))

IMPORT MISSING CHART NUMBERS INTO MWPAT

insert into mwpat("last Name", "first Name", "Chart Number", "EntityType") select "LName", "FName", "FirstOfChart Number", 1 from "c:\toimport.adt"

JOIN - OHAPP AND MWPAT
UPDATE APPOITNMENTS THAT HAVE NO PHONE NUMBER BASED ON CHART NUMBER FROM MWPAT

UPDATE OHAPP SET "OHAPP"."PHONE" = M11."pHONE 1" FROM OHAPP m9, MWPAT m11 WHERE m9."cHART NUMBER"=m11."cHART NUMBER" AND M9."USER CODE" = 'TCSP' AND M9."PHONE" IS NULL
 

JOIN - MWCAS AND OHAPP
Find all appointments that have no case

SELECT * FROM OHAPP m9, MWCAS m11 WHERE m9."cHART NUMBER"=m11."cHART NUMBER" AND M9."USER CODE" = 'TCSP' AND M9."CASE NUMBER" IS NULL

Update appointments without a case number

update ohapp set "Case Number" = m11."case number" FROM OHAPP m9, MWCAS m11 WHERE m9."cHART NUMBER"=m11."cHART NUMBER" AND M9."CASE NUMBER" IS NULL

HOW TO MODIFY CUSTOM PATIENT AND CASE SCREENS – ADD FIELDS TO IT

CANNOT JUST COPY FIELDS – IT DOES NOT WORK

TO ADD THE CUSTOM SCREENS - (NY WORKER'S COMP) TO MEDISOFT - YOU NEED TO ADD THE FOLLOWING FIELDS BY USING SQL QUERY ON MWCCD (Custom Case Data)

MWCCD comes originally only with the following fields

      [Case Number] Integer,
      [Date Modified] TimeStamp,
      [Required Logical] Logical,
      [Required Date] Date,
      [Required Alphanumeric] Char( 15 )

 REMOVE UNNEEDED FIELDS THAT CAN CAUSE A PROBLEM ( I do not use thes fields)

Alter Table mwccd
    DROP column [Trial]
    DROP column [*]
    DROP column [*1]
    DROP column [History?]
    DROP column [3]
    DROP column [31]
    DROP column [3b]
    DROP column [Maximum]
    DROP column [Date]
    DROP column [5]
    DROP column [IF YES]
    DROP column [6]
    DROP column [7 Yes]
    DROP column [No]
    DROP column [8 Yes]
    DROP column [No1]
    DROP column [Total]
    DROP column [Partial]
    DROP column [%]
    DROP column [10]
    DROP column [If YES1]
    DROP column [If YES2]
    DROP column [11 Yes]
    DROP column [No2]

ADD NEEDED FIELDS  (some fields may already exist - remove them from this list)

ALTER TABLE MWCCD
    Add Column [Case Number] Integer
    Add Column [Date Modified] TimeStamp
    Add Column [Required Logical] Logical
    Add Column [Required Date] Date
    Add Column [Required Alphanumeric] Char( 15 )
    Add Column physician Char( 1 )
    Add Column Podiatrist Char( 1 )
    Add Column Chiropractor Char( 1 )
    Add Column FROMEXAM Date
    Add Column TOEXAM Date
    Add Column DATETREAT Date
    Add Column NOBOX Char( 1 )
    Add Column VFB Char( 1 )
    Add Column VAWB Char( 1 )
    Add Column BoxNine Char( 80 )
    Add Column BoxTen Char( 80 )
    Add Column BoxTwelve Char( 80 )
    Add Column BoxFive Char( 80 )
    Add Column BoxSeventeenNo Logical
    Add Column BoxSeventeen Logical
    Add Column BoxSeventeenD Char( 80 )
    Add Column BoxSixteen Logical
    Add Column BoxSixteenNo Logical
    Add Column WCB Char( 35 )
    Add Column BoxTwentyone Char( 50 )
    Add Column BoxTwenty Char( 50 )
    Add Column BoxElevenNOT Logical
    Add Column [Key] Char( 15 )
    Add Column FORTYEIGHT Char( 15 )
    Add Column FIFTEENDAY Char( 15 )
    Add Column FORTYFIVE Char( 15 )
    Add Column WCBCASE Char( 15 )
    Add Column Testify Char( 15 )
    Add Column Testify_one Char( 15 )
    Add Column Testify_two Char( 15 )
    Add Column VFBL Char( 15 )
    Add Column VAWBL Char( 15 )
    Add Column WORKYES Logical
    Add Column WORKNO Logical
    Add Column ELEVENYES Logical
    Add Column BOXFOUR Logical
    Add Column WCBNUMBER Char( 15 )
    Add Column HISTORY Date
    Add Column FIVE Char( 50 )
    Add Column ONE Char( 100 )
    Add Column ONEA Char( 100 )
    Add Column FOUR Char( 100 )
    Add Column FOURA Char( 100 )
    Add Column TWO Char( 100 )
    Add Column FOURB Char( 100 )
    Add Column TEN Char( 50 )
    Add Column TENA Char( 50 )
    Add Column ONEB Char( 100 )
    Add Column PILOT Char( 3 )
    Add Column [Chart Number] Char( 8 )
    Add Column [Carrier Case #] Char( 19 )
    Add Column time Char( 8 )
    Add Column INITIAL Logical
    Add Column PROGRESS Logical
    Add Column FINAL Logical
    Add Column OT Logical
    Add Column PT Logical
    Add Column PTCASE Char( 20 )
    Add Column PTIME Char( 10 )
    Add Column PTVFBL Char( 6 )
    Add Column PTREPORT Date
    Add Column PTONEA Char( 100 )
    Add Column PTTWO Char( 100 )
    Add Column PTTHREEA Char( 100 )
    Add Column PTTHREEB Char( 100 )
    Add Column PTTHREE_TWO Char( 100 )
    Add Column PTFREQ Char( 50 )
    Add Column PTPERIOD Char( 50 )
    Add Column PTPERIOD_TWO Char( 50 )
    Add Column THREE_THREE Logical
    Add Column VISIT_FROM Date
    Add Column VISIT_TO Date
    Add Column FIRST_VISIT Date
    Add Column SEEN_AGAIN Logical
    Add Column SEEN_WHEN Date
    Add Column REFERRED Logical
    Add Column WORKING Logical
    Add Column