Medisoft SQL commands
WARNING: Proceed at your own risk
and ALWAYS backup before doing any of these ARC commands!
Browse to your Medidata folder and select the practice folder you want to connect to:
Select the mwddf.add and and enter your username:
A client is switching from Medisoft, and one of the things they asked to be exported is: In transaction table, if Procedure code is equal to G0105, G0121, or 45378 thru 45385 then they want:
Chart Number, Patient Last Name, Patient First Name, Patient Middle Initial, Patient Date Of Birth, Procedure code, Date From
SELECT mwtrn."Chart Number",
mwpat."First Name",
mwpat."Last Name",
mwpat."Middle Initial",
mwpat."Date of Birth",
mwtrn."Date From",
mwtrn."Procedure Code"
FROM mwtrn
INNER JOIN mwpat ON mwpat."Chart Number" = mwtrn."Chart Number"
WHERE "Procedure Code" = 'G0121' OR "Procedure Code" = 'G0105'
OR ("Procedure Code" > '45378' AND "Procedure Code" < '45385')http://www.emedct.com/Tech_Support/OurFiles/Medisoft_Data_Corruption_SQL_Samples_FileCorruption.htm
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"
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 ----------------------------------------------------------------------------------------------------------------------------
--- ALL PATIENTS NOW---------------------------------------------------------------------------------------------------------------------------------------
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"
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", "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')));
RIGHT CLICK ON EXPORT RESULTS TO C:\TOIMPORT.ADT
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
SELECT m9."Chart Number", m9."Case Number", m11."Case Number" FROM OHAPP m9, MWCAS m11 WHERE m9."cHART NUMBER"=m11."cHART NUMBER" 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 LIMITED Date
Add Column REGULAR Date
Add Column ADDITIONAL Char( 100 )
Add Column ADDITIONAL_TWO Char( 100 )
Add Column PTINITIAL Logical
Add Column PTPROGRESS Logical
Add Column PTFINAL Logical
Add Column OTR Logical
Add Column PTR Logical
Add Column PRIOR Logical
Add Column ANOTHER Logical
Add Column PTEVAL Logical
Add Column PTTRE Logical
Add Column DOI Date
Add Column ONEA_TWO Char( 100 )
Add Column TWO_TWO Char( 100 )
Add Column PTCARRIER Char( 20 )
Add Column PTTHREEA_TWO Char( 100 )
Add Column PTTHREEB_B Char( 100 )
Add Column PTTHREETWO_TWO Char( 100 )
Add Column INJADDR Char( 50 )
Add Column PVFBL Logical
Add Column WORKDISABLE Logical
Add Column BoxFourY Char( 1 )
Add Column BoxElevenTRUE Char( 2 )
Add Column BoxElevenFALSE Char( 2 )
Add Column ResultOfAccidentY Char( 2 )
Add Column ResultOfAccidentN Char( 2 )
Add Column ResultOfAccidentNoDesc Char( 50 )
Add Column PermDisabilityDesc Char( 75 )
Add Column PermDisabilityY Char( 1 )
Add Column PermDisabilityN Char( 1 )
Add Column PermDisabilityUndetermine Char( 2 )
Add Column DisabilityPARTIAL Char( 2 )
Add Column DisabilityTOTAL Char( 2 )
Add Column PPOProvider Logical
Add Column Hist_WhereHowDesc Char( 255 )
Add Column Hist_LearnedByPatient Char( 1 )
Add Column Hist_LearnedByMedicalRecords Char( 1 )
Add Column Hist_LearnedByOther Char( 1 )
Add Column Hist_LearnedByMedicalRecords_1 Char( 30 )
Add Column Hist_AnotherHlthProvTreatY Char( 1 )
Add Column Hist_AnotherHlthProvTreatYDesc Char( 75 )
Add Column Hist_AnotherHlthProvTreatN Char( 1 )
Add Column Hist_PrevTreatedY Char( 1 )
Add Column Hist_PrevTreatedYDesc Char( 25 )
Add Column Hist_PrevTreatedN Char( 1 )
Add Column ExmInj_Abrasion Char( 1 )
Add Column ExmInj_Amputation Char( 1 )
Add Column ExmInj_Avulsion Char( 1 )
Add Column ExmInj_Bite Char( 1 )
Add Column ExmInj_Bum Char( 1 )
Add Column ExmInj_Constusions_Hematoma Char( 1 )
Add Column ExmInj_Crush_Injury Char( 1 )
Add Column ExmInj_Dermatitis Char( 1 )
Add Column ExmInj_Dislocation Char( 1 )
Add Column ExmInj_Fracture Char( 1 )
Add Column ExmInj_Hearing_Loss Char( 1 )
Add Column ExmInj_Hemia Char( 1 )
Add Column ExmInj_Other Char( 1 )
Add Column ExmInj_Infectious_Disease Char( 1 )
Add Column ExmInj_Inhalation_Exposure Char( 1 )
Add Column ExmInj_Laceration Char( 1 )
Add Column ExmInj_Needle_Stick Char( 1 )
Add Column ExmInj_Poisoning_Toxic_Effects Char( 1 )
Add Column ExmInj_Psychological Char( 1 )
Add Column ExmInj_Puncture_Wound Char( 1 )
Add Column ExmInj_Repetitive_Strain_Inj Char( 1 )
Add Column ExmInj_Spinal_Cord_Injury Char( 1 )
Add Column ExmInj_Sprain_Strain Char( 1 )
Add Column ExmInj_Tom_Ligament_Tendon_Muscle Char( 1 )
Add Column ExmInj_Vision_Loss Char( 1 )
Add Column ExmInj_Abrasion_Desc Char( 50 )
Add Column ExmInj_Amputation_Desc Char( 50 )
Add Column ExmInj_Avulsion_Desc Char( 50 )
Add Column ExmInj_Bite_Desc Char( 50 )
Add Column ExmInj_Bum_Desc Char( 50 )
Add Column ExmInj_Constusions_Hematoma_Desc Char( 50 )
Add Column ExmInj_Crush_Injury_Desc Char( 50 )
Add Column ExmInj_Dermatitis_Desc Char( 50 )
Add Column ExmInj_Dislocation_Desc Char( 50 )
Add Column ExmInj_Fracture_Desc Char( 50 )
Add Column ExmInj_Hearing_Loss_Desc Char( 50 )
Add Column ExmInj_Hemia_Desc Char( 50 )
Add Column ExmInj_Other_Desc Char( 150 )
Add Column ExmInj_Infectious_Disease_Desc Char( 50 )
Add Column ExmInj_Inhalation_Exposure_Desc Char( 50 )
Add Column ExmInj_Laceration_Desc Char( 50 )
Add Column ExmInj_Needle_Stick_Desc Char( 50 )
Add Column ExmInj_Poisoning_Toxic_Effects_Desc Char( 50 )
Add Column ExmInj_Psychological_Desc Char( 50 )
Add Column ExmInj_Puncture_Wound_Desc Char( 50 )
Add Column ExmInj_Repetitive_Strain_Inj_Desc Char( 50 )
Add Column ExmInj_Spinal_Cord_Injury_Desc Char( 50 )
Add Column ExmInj_Sprain_Strain_Desc Char( 50 )
Add Column ExmInj_Tom_Ligament_Tendon_Muscle_Desc Char( 50 )
Add Column ExmInj_Vision_Loss_Desc Char( 50 )
Add Column Exm_Dates Char( 100 )
Add Column ExmSub_Numb_Tingling Char( 1 )
Add Column ExmSub_Pain Char( 1 )
Add Column ExmSub_Stiff Char( 1 )
Add Column ExmSub_Swelling Char( 1 )
Add Column ExmSub_Weakness Char( 1 )
Add Column ExmSub_Other Char( 1 )
Add Column ExmSub_Numb_Tingling_Desc Char( 50 )
Add Column ExmSub_Pain_Desc Char( 50 )
Add Column ExmSub_Stiff_Desc Char( 50 )
Add Column ExmSub_Swelling_Desc Char( 50 )
Add Column ExmSub_Weakness_Desc Char( 50 )
Add Column ExmSub_Other_Desc Char( 150 )
Add Column PE_None_At_Present Char( 1 )
Add Column PE_Bruising Char( 1 )
Add Column PE_Bums Char( 1 )
Add Column PE_Crepitation Char( 1 )
Add Column PE_Deformity Char( 1 )
Add Column PE_Edema Char( 1 )
Add Column PE_Hema_Lump_Swelling Char( 1 )
Add Column PE_Joint_Effusion Char( 1 )
Add Column PE_Laceration_Sutures Char( 1 )
Add Column PE_Pain_Tenderness Char( 1 )
Add Column PE_Scar Char( 1 )
Add Column PE_Other Char( 1 )
Add Column PE_Neuro Char( 1 )
Add Column PE_Abn_Rest_ROM Char( 1 )
Add Column PE_Abn_Rest_Active_ROM Char( 1 )
Add Column PE_Abn_Rest_Passive_ROM Char( 1 )
Add Column PE_Gait Char( 1 )
Add Column PE_Palpable_Muscle_Spasm Char( 1 )
Add Column PE_Reflexes Char( 1 )
Add Column PE_Sensation Char( 1 )
Add Column PE_Strength_Weakness Char( 1 )
Add Column PE_Wasting_Muscle_Atrophy Char( 1 )
Add Column PE_None_At_Present_Desc Char( 50 )
Add Column PE_Bruising_Desc Char( 50 )
Add Column PE_Bums_Desc Char( 50 )
Add Column PE_Crepitation_Desc Char( 50 )
Add Column PE_Deformity_Desc Char( 50 )
Add Column PE_Edema_Desc Char( 50 )
Add Column PE_Hema_Lump_Swelling_Desc Char( 50 )
Add Column PE_Joint_Effusion_Desc Char( 50 )
Add Column PE_Laceration_Sutures_Desc Char( 50 )
Add Column PE_Pain_Tenderness_Desc Char( 50 )
Add Column PE_Scar_Desc Char( 50 )
Add Column PE_Other_Desc Char( 100 )
Add Column PE_Neuro_Desc Char( 50 )
Add Column PE_Abn_Rest_ROM_Desc Char( 50 )
Add Column PE_Abn_Rest_Active_ROM_Desc Char( 50 )
Add Column PE_Abn_Rest_Passive_ROM_Desc Char( 50 )
Add Column PE_Gait_Desc Char( 50 )
Add Column PE_Palpable_Muscle_Spasm_Desc Char( 50 )
Add Column PE_Reflexes_Desc Char( 50 )
Add Column PE_Sensation_Desc Char( 50 )
Add Column PE_Strength_Weakness_Desc Char( 50 )
Add Column PE_Wasting_Muscle_Atrophy_Desc Char( 50 )
Add Column PE_Dx_Tests Char( 255 )
Add Column PE_Treatments Char( 255 )
Add Column PE_Prognosis_Recover Char( 255 )
Add Column PE_Preexist_May_Affect_TreatY Char( 1 )
Add Column PE_Preexist_May_Affect_TreatN Char( 1 )
Add Column PE_Preexist_May_Affect_Treat_Desc Char( 255 )
Add Column DOC_Med_Cause_IngY Char( 1 )
Add Column DOC_Med_Cause_IngN Char( 1 )
Add Column DOC_Pat_Consistent_HistY Char( 1 )
Add Column DOC_Pat_Consistent_HistN Char( 1 )
Add Column DOC_Consistent_FindingsY Char( 1 )
Add Column DOC_Consistent_FindingsN Char( 1 )
Add Column DOC_Consistent_FindingsNA Char( 1 )
Add Column DOC_Perct_Temp_Impair Char( 3 )
Add Column DOC_Findings_Tests_Results Char( 255 )
Add Column Pln_Treatment Char( 255 )
Add Column Pln_Meds_Prescribed Char( 255 )
Add Column Pln_Meds_Over_Counter Char( 255 )
Add Column Pln_Meds_Restrict_None Char( 1 )
Add Column Pln_Meds_Restrict_May_Affect Char( 1 )
Add Column Pln_Meds_Restrict_May_Affect_Desc Char( 255 )
Add Column Test_CTScan Char( 1 )
Add Column Test_EMG_NCS Char( 1 )
Add Column Test_MRI Char( 1 )
Add Column Test_Labs Char( 1 )
Add Column Test_XRay Char( 1 )
Add Column Test_Other Char( 1 )
Add Column Test_CTScan_Desc Char( 50 )
Add Column Test_EMG_NCS_Desc Char( 50 )
Add Column Test_MRI_Desc Char( 50 )
Add Column Test_Labs_Desc Char( 50 )
Add Column Test_XRay_Desc Char( 50 )
Add Column Test_Other_Desc Char( 150 )
Add Column Ref_Chiro Char( 1 )
Add Column Ref_Int_FamPhysician Char( 1 )
Add Column Ref_Occp_Therapist Char( 1 )
Add Column Ref_Phy_Therapist Char( 1 )
Add Column Ref_Specialist Char( 1 )
Add Column Ref_Other Char( 1 )
Add Column Ref_Specialist_Desc Char( 100 )
Add Column Ref_Other_Desc Char( 100 )
Add Column FU_1WKS Char( 1 )
Add Column FU_1_2WKS Char( 1 )
Add Column FU_3_4WKS Char( 1 )
Add Column FU_5_6WKS Char( 1 )
Add Column FU_7_8WKS Char( 1 )
Add Column FU_MONTHS Char( 1 )
Add Column FU_AS_NEEDED Char( 1 )
Add Column WRK_MissedY Char( 1 )
Add Column WRK_MissedN Char( 1 )
Add Column WRK_Missed Date
Add Column WRK_ing_NowY Char( 1 )
Add Column WRK_ing_NowN Char( 1 )
Add Column WRK_ing_NowY_Usual Char( 1 )
Add Column WRK_ing_NowY_Limited Char( 1 )
Add Column WRK_Cannot_Rtrn Char( 1 )
Add Column WRK_Cannot_Rtrn_Desc Char( 100 )
Add Column WRK_Can_Rtrn_No_Lim Char( 1 )
Add Column WRK_Can_Rtrn_No_Lim_Date Date
Add Column WRK_Can_Rtrn_W_Lim Char( 1 )
Add Column WRK_Can_Rtrn_W_Lim_Date Date
Add Column WRK_Lim_Bend_Twist Char( 1 )
Add Column WRK_Lim_Climb Char( 1 )
Add Column WRK_Lim_Envrn_Conditions Char( 1 )
Add Column WRK_Lim_Kneeling Char( 1 )
Add Column WRK_Lim_Other Char( 1 )
Add Column WRK_Lim_Other_Desc Char( 100 )
Add Column WRK_Lim_Lift Char( 1 )
Add Column WRK_Lim_Oper_Heavy_Equip Char( 1 )
Add Column WRK_Lim_Oper_Motor_Veh Char( 1 )
Add Column WRK_Lim_Pers_Proct_Equip Char( 1 )
Add Column WRK_Lim_Sit Char( 1 )
Add Column WRK_Lim_Stand Char( 1 )
Add Column WRK_Lim_Pub_Transp Char( 1 )
Add Column WRK_Lim_Use_Upper_Ext Char( 1 )
Add Column WRK_Lim_Desc Char( 255 )
Add Column WRK_Lim_For_1_2Days Char( 1 )
Add Column WRK_Lim_For_3_7Days Char( 1 )
Add Column WRK_Lim_For_8_14Days Char( 1 )
Add Column WRK_Lim_For_15Days Char( 1 )
Add Column WRK_Lim_For_Unknown Char( 1 )
Add Column WRK_Lim_For_NA Char( 1 )
Add Column WRK_Rtrn_Discs_w_Pat Char( 1 )
Add Column WRK_Rtrn_Discs_w_Emp Char( 1 )
Add Column WRK_Rtrn_Discs_w_NA Char( 1 )
Add Column Sign_Provided_Svcs Char( 1 )
Add Column Sign_Supervised_Svcs Char( 1 )
Add Column DP_Cane Char( 1 )
Add Column DP_Crutches Char( 1 )
Add Column DP_Orthotics Char( 1 )
Add Column DP_Walker Char( 1 )
Add Column DP_Wheelchair Char( 1 )
Add Column DP_Other Char( 1 )
Add Column DP_Other_Dec Char( 50 )
Add Column WRK_MissedY_Date Date
Add Column Ph_Meds_Restric_May_Affect_Des Char( 160 )
Add Column Board_Authorized_Provided Char( 15 )
Add Column Board_Authorized_Supervised Char( 15 )
Add Column AR_XRays Char( 1 )
Add Column AR_XRays_Dec Char( 150 )
Add Column AR_CTscan Char( 1 )
Add Column AR_CTscan_Dec Char( 150 )
Add Column AR_MRI Char( 1 )
Add Column AR_MRI_Dec Char( 150 )
Add Column AR_EMG_NCV Char( 1 )
Add Column AR_UE_Dec Char( 150 )
Add Column AR_LE_Dec Char( 150 )
Add Column AR_Nerve_Blocks Char( 1 )
Add Column AR_Nerve_Blocks_Dec Char( 150 )
Add Column AR_Orthopedic_Evaluation Char( 1 )
Add Column AR_Orthopedic_Evaluation_Dec Char( 150 )
Add Column AR_Neurological_Evaluation Char( 1 )
Add Column AR_Neurological_Evaluation_Dec Char( 150 )
Add Column AR_Other Char( 1 )
Add Column AR_Other_Dec Char( 150 )
Add Column ART_Physical_Therapy Char( 1 )
Add Column ART_Occupational_Therapy Char( 1 )
Add Column ART_Other Char( 1 )
Add Column ART_Times_P Char( 15 )
Add Column ART_Times_Oc Char( 15 )
Add Column ART_Weeks_P Char( 15 )
Add Column ART_Weeks_Oc Char( 15 )
Add Column ART_Other_Dec Char( 15 )
Add Column ART_Times_Ot Char( 15 )
Add Column ART_Weeks_Ot Char( 15 )
Add Column ARS_Type_of_Surgery_Dec Char( 150 )
Add Column ARS_Surgical_Implants Char( 1 )
Add Column ARS_Surgical_Implants_Dec Char( 150 )
Add Column ARS_Post_OP_Physical_Therapy Char( 1 )
Add Column ARS_Times_Pt Char( 15 )
Add Column ARS_Weeks_Pt Char( 15 )
Add Column ARS_Type_of_Surgery Char( 1 )
Add Column PA_Job_Tile_Dec Char( 150 )
Add Column PA_Work_Dec Char( 150 )
Add Column [DP_Additional body parts_Dec] Char( 150 )
Add Column [DP_List any changes_Dec] Char( 150 ));
THE ACTUAL SCREEN IS NEXT
Tell the system the location of each field, size, tab order and everything
Click here to download C4_MWSCD.ADT.zip and import this settings into MWSCD.ADT:
Insert into mwscd select * from "C:\mwtest.adt"
NEXT DEFINE THE NAME FOR EACH SCREEN
***** YOU NEED TO REMOVE ANY UNNEEDED SCREEN ID SINCE THE SYSTEM USES
***** AUTOINCREMENT NUMBER AND IT CANNOT BE CHANGED IN MWSCR – THIS
***** NUMBER HAS TO MATCH THE SCREEN ID IN BOTH TABLES IN MWSCD AND MWSCR.
***** ONE CAN BE CHANGED AND THE OTHER CAN’T.
***** YOU CAN CHANGE THE TABLE FIELD [SCREEN ID] FROM AUTOINC TO INTEGER
***** WHEN DONE UPDATING THE SCREEN ID THEN SET FIELD BACK TO AUTOINC
Screen above will fit the custom fields and the table setting imported from C4_MWSCR.ADT.zip specified at the beginning with the Alter SQL statement. Size is critical and can cause problems if fields do not fit on the screen.
FOR TABLE mwscr TYPE THE VALUES DIRECTLY INTO THE TABLE. IT IS ONLY ONE LINE – PAY ATTENTION TO SCREEN ID (AUTONUMBER –CANT BE CHANGED)
Need to set defaults and triggers for each table mwscd and mwcpd
If user wants to set a default value for a field so they do not have to enter it everytime, you can go th the table / prpoerties and set the default value for whatever the user wants.
THE INSURANCE CLAIMS REPORTS is next
Click here to download the C4 forms for Medisoft UReports file
Run the following code.
The script will set the initial procedure codes with a flag so we can filter the claims for C4 Initial Reporr. Add/remove procedure codes that apply/do not apply. Otherwise claims will not print for C4 Initial Report form:
update mwpro set [Revenue Code] = 'INITIAL' WHERE [Revenue Code] is null and ([Code 1] = '99201' or [Code 1] ='99203' or [Code 1] ='99203N' or [Code 1] ='99204' or [Code 1] ='99205' or [Code 1] = '99244' or [Code 1] = '97001')
Update the procedure codes in the transaction
Update the procedure codes in the transaction table that represent initial codes for a visit. This is used as a filter so multiple pages are not printed when multiple transactions are on the same claim number. Run the following code add/remove procedure codes that apply/do not apply. Otherwise old claims will not print with the new C4 form:
update mwtrn set [Revenue_Code] = 'INITIAL' where [revenue_code] is null and ([procedure code] = '99201' or [procedure code] ='99203' or [procedure code] ='99203N' or [procedure code] ='99204' or [procedure code] ='99205' or [procedure code] = '99244' or [procedure code] = '97001')
Modify the Report New C4 Initial Rpt P2
In the New C4 Initial Rpt P2 you need to add the procedure codes that represent initial procedure codes. The report shows an IF statements for 99201, 99203, 99203N, 99204, 99205, 99244, 97001. See Date From, Date To, Modifiers … the Transaction line. Add any other procedure codes using IF statements that apply to the practice.
********************************************************************************************************************************************
HOW TO CREATE MWPINS FOR EACH PROVIDER
********************************************************************************************************************************************
In ARC32 run the following SQL - change Provider Code and PIN to match provider you want
select code AS "Insurance Code", 'DHAG' AS "Provider Code", '202890038' as "Pin Number", '202890038' as "Group ID", '11/06/2006 9:33:30 AM' as "Date Modified", 'G2' as "Pin_Qualifier", 'G2' as "Group_Qualifier", 'P' as "Type" from mwins
select code AS "Insurance Code", 'JACOB' AS "Provider Code", '202890038' as "Pin Number", '202890038' as "Group ID", '11/06/2006 9:33:30 AM' as "Date Modified", 'G2' as "Pin_Qualifier", 'G2' as "Group_Qualifier", 'P' as "Type" from mwin
select code AS "Insurance Code", 'GERRE' AS "Provider Code", '2DDDDDDDD' as "Pin Number", '2DDDDDDDD' as "Group ID", '11/06/2006 9:33:30 AM' as "Date Modified", 'G2' as "Pin_Qualifier", 'G2' as "Group_Qualifier", 'P' as "Type" from mwins
export results to new file : c:\mwpins1.adt --> remember this is for provider 1 ---> GARRE
export results to new file : c:\mwpins2.adt --> remember this is for provider 2 ---> JACOB
export results to new file : c:\mwpins2.adt --> remember this is for provider 3 ---> ROSEN
and so on...
In ARC32 open database, export mwpins to a new table so we can get a blank (image file) of the table mwpins to C:\mwpinsBLANK.adt emtpy C:\mwpinsBLANK.adt (no records)
open c:\mwpins1.adt - right click and EXPORT to existing table --> C:\mwpinsBLANK.adt
open c:\mwpins2.adt - right click and EXPORT to existing table --> C:\mwpinsBLANK.adt
open c:\mwpins3.adt - right click and EXPORT to existing table --> C:\mwpinsBLANK.adt
and so on....
Bring all records from C:\mwpinsBLANK.adt into actual Medisoft table mwpins use the following script:
insert into mwpins select * from "c:\mwpinsBLANK.ADT"
Done
********************************************************************************************************************************************
HOW TO INSERT MWZIP CODES FROM ANOTHER TABLE THAT DO NOT EXIST IN THE CURRENT MWZIP TABLE
********************************************************************************************************************************************
insert into "c:\mwzipCT.adt" select * FROM "c:\mwzipNY.adt" myFrom WHERE trim(upper(myFrom."zip code")) <> trim(upper("c:\mwzipCT.adt"."zip code"))
mwcas set some defaults
UPDATE MWCAS SET
[Print Patient Statements] = True,
[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,
[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,
[Accept Assignment #3] = True,
[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
UPDATE MWCAS SET "FACILITY" = 'OFFIC'
SET DEFAULTS FOR MWINS (ALL OF THEM)
UPDATE MWINS SET "Delay Secondary Billing" = TRUE, "INSURED SIGNATURE ON FILE" = 'Signature on file', "PHYSICIAN SIGNATURE ON FILE" = 'Signature on file',"SIGNATURE ON FILE" = 'Signature on file', "Default Write Off Code" = 'INSADJUST', "DEFAULT PAYMENT CODE" = 'INSPAYMENT', "DEFAULT WITHHOLD CODE" = 'INSWITHHLD', "DEFAULT DEDUCTIBLE CODE" = 'DEDUCTIBLE',"PRINT PINS ON INS FORM" = 'N', "DEFAULT TAKE BACK CODE" = 'INSTAKEBAK', "PRACTICE ID NUMBER" = '202890038', "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
********************************************************************************************************************************************
SET JOIN TABLE TO VIEW NO MATCHING RECORDS
********************************************************************************************************************************************
In the following example, I want to view records in c:\mwzipIMP.adt that do not exist in mwzip.
The following query is design to include all records (left join) from C:\mwzipIMP.adt where Zip Code does not exist (is null) in mwzip:
select * from "c:\mwzipIMP.adt" left join mwzip on mwzip.[Zip code] = "c:\mwzipIMP.adt".[Zip code] WHERE mwzip.[Zip Code] is null
To be able to insert the fields we must have the same number of fields. Above query shows fields from both tables. Below query shows only fields from C:\mwzipIMP.adt which is what we want to export into Medisoft
select "c:\mwzipIMP.adt".* from "c:\mwzipIMP.adt" left join mwzip on mwzip.[Zip code] = "c:\mwzipIMP.adt".[Zip code] WHERE mwzip.[Zip Code] is null
Insert records that do not exist in mwzip from c:\mwzipIMP.adt
insert into mwzip select "c:\mwzipIMP.adt".* from "c:\mwzipIMP.adt" left join mwzip on mwzip.[Zip code] = "c:\mwzipIMP.adt".[Zip code] WHERE mwzip.[Zip Code] is null
To see records that match in both tables. Set up the following JOIN. The query provides fields from both tables.
select * from "c:\mwzipIMP.adt" INNER JOIN mwzip on mwzip.[Zip code] = "c:\mwzipIMP.adt".[Zip code]
To see records that match in both tables. Set up the following JOIN but only to display fields in one table not both fields from both tables
select "c:\mwzipIMP.adt".* from "c:\mwzipIMP.adt" INNER JOIN mwzip on mwzip.[Zip code] = "c:\mwzipIMP.adt".[Zip code]
To update the records that match in both tables run the following query. Join query is updating City in c:\mwzipIMP.adt
update "c:\mwzipIMP.adt" set "c:\mwzipIMP.adt".[City] = mwzip.[City] from "c:\mwzipIMP.adt" INNER JOIN mwzip on mwzip.[Zip code] = "c:\mwzipIMP.adt".[Zip code]
To update the records that match in both tables run the following query. Join query below is for MWPRO
update mwpro set mwpro.[Amount C]= "c:\mwpro_DHAG.adt".[Amount C] , mwpro.[Amount D]= "c:\mwpro_DHAG.adt".[Amount D] from mwpro INNER JOIN "c:\mwpro_DHAG.adt" on mwpro."Code 1" = "c:\mwpro_DHAG.adt"."code 1"
********************************************************************************************************************************************
Access Violation when printing reports, claims and other
Below are the tables that need to be searched for non-alpha characters and/or all numeric characters (except for Phone & Zip) which could be causing the Access Violations to occur:
Patient table - MWPAT
First Name, Last Name, Middle Initial, Phone 1
Provider table - MWPHY
First Name, Last Name, Middle Initials, Credentials, Office, City, State, Zip
Insurance table - MWINS
City, State, Zip
Address table - MWADD
City, State, Zip
Referring Provider table – MWRPH
First Name, Last Name, Middle Initial, Credentials, City, State, Zip
MWPAT (use UPDATE MWPAT SET "First Name" = replace(mwpat.[First Name], '#','') to fix invalid characters)
SELECT * FROM mwpat WHERE "Last Name" LIKE '%0%' or "Last Name" LIKE '%2%' or "Last Name" LIKE '%3%' or "Last Name" LIKE '%4%' or "Last Name" LIKE '%5%' or "Last Name" LIKE '%6%' or "Last Name" LIKE '%7%' or "Last Name" LIKE '%8%' or "Last Name" LIKE '%9%'
SELECT * FROM MWPAT WHERE "Last Name" LIKE '%&%' or "Last Name" LIKE '%)%' OR "Last Name" LIKE '%(%' OR "Last Name" LIKE '%[%' OR "Last Name" LIKE '%]%' OR "Last Name" LIKE '%!%' OR "Last Name" LIKE '%#%' OR "Last Name" LIKE '%$%'
SELECT * FROM mwpat WHERE "First Name" LIKE '%0%' or "First Name" LIKE '%2%' or "First Name" LIKE '%3%' or "First Name" LIKE '%4%' or "First Name" LIKE '%5%' or "First Name" LIKE '%6%' or "First Name" LIKE '%7%' or "First Name" LIKE '%8%' or "First Name" LIKE '%9%'
SELECT * FROM MWPAT WHERE "First Name" LIKE '%&%' or "First Name" LIKE '%)%' OR "First Name" LIKE '%(%' OR "First Name" LIKE '%[%' OR "First Name" LIKE '%]%' OR "First Name" LIKE '%!%' OR "First Name" LIKE '%#%' OR "First Name" LIKE '%$%'
SELECT * FROM mwpat WHERE "Middle Initial" LIKE '%0%' or "Middle Initial" LIKE '%2%' or "Middle Initial" LIKE '%3%' or "Middle Initial" LIKE '%4%' or "Middle Initial" LIKE '%5%' or "Middle Initial" LIKE '%6%' or "Middle Initial" LIKE '%7%' or "Middle Initial" LIKE '%8%' or "Middle Initial" LIKE '%9%'
SELECT * FROM mwpat WHERE Ucase("Phone 1") LIKE '%A%' or Ucase("Phone 1") LIKE '%B%' or Ucase("Phone 1") LIKE '%C%' or Ucase("Phone 1") LIKE '%D%' or Ucase("Phone 1") LIKE '%E%' or Ucase("Phone 1") LIKE '%F%' or Ucase("Phone 1") LIKE '%G%' or Ucase("Phone 1") LIKE '%H%' or Ucase("Phone 1") LIKE '%I%' OR Ucase("Phone 1") LIKE '%J%' or Ucase("Phone 1") LIKE '%K%' or Ucase("Phone 1") LIKE '%L%' or Ucase("Phone 1") LIKE '%M%' or Ucase("Phone 1") LIKE '%N%' or Ucase("Phone 1") LIKE '%O%' or Ucase("Phone 1") LIKE '%P%' or Ucase("Phone 1") LIKE '%Q%' or Ucase("Phone 1") LIKE '%R%' OR Ucase("Phone 1") LIKE '%S%' or Ucase("Phone 1") LIKE '%T%' or Ucase("Phone 1") LIKE '%U%' or Ucase("Phone 1") LIKE '%V%' or Ucase("Phone 1") LIKE '%X%' or Ucase("Phone 1") LIKE '%Y%' or Ucase("Phone 1") LIKE '%Z%'
SELECT * FROM MWPAT WHERE "Phone 1" LIKE '%&%' OR "Phone 1" LIKE '%[%' OR "Phone 1" LIKE '%]%' OR "Phone 1" LIKE '%!%' OR "Phone 1" LIKE '%#%' OR "Phone 1" LIKE '%$%'
' INVALID CHART NUMBER IN PATIENT TABLE
SELECT * FROM MWPAT WHERE "CHART NUMBER" LIKE '%&%' OR "CHART NUMBER" LIKE '%:%' OR "CHART NUMBER" LIKE '%;%'
OR "CHART NUMBER" LIKE '%-%' or "CHART NUMBER" LIKE '%)%' OR "CHART NUMBER" LIKE '%(%' OR "CHART NUMBER" LIKE
'%[%' OR "CHART NUMBER" LIKE '% %' OR "CHART NUMBER" LIKE '%!%' OR "CHART NUMBER" LIKE '%#%' OR "CHART NUMBER"
LIKE '%$%'
MWPHY (use UPDATE MWPHY SET "First Name" = replace(mwpat.[First Name], '#','') to fix invalid characters)
SELECT * FROM mwphy WHERE "Last Name" LIKE '%0%' or "Last Name" LIKE '%2%' or "Last Name" LIKE '%3%' or "Last Name" LIKE '%4%' or "Last Name" LIKE '%5%' or "Last Name" LIKE '%6%' or "Last Name" LIKE '%7%' or "Last Name" LIKE '%8%' or "Last Name" LIKE '%9%'
SELECT * FROM mwphy WHERE "First Name" LIKE '%0%' or "First Name" LIKE '%2%' or "First Name" LIKE '%3%' or "First Name" LIKE '%4%' or "First Name" LIKE '%5%' or "First Name" LIKE '%6%' or "First Name" LIKE '%7%' or "First Name" LIKE '%8%' or "First Name" LIKE '%9%'
SELECT * FROM mwphy WHERE "Middle Initial" LIKE '%0%' or "Middle Initial" LIKE '%2%' or "Middle Initial" LIKE '%3%' or "Middle Initial" LIKE '%4%' or "Middle Initial" LIKE '%5%' or "Middle Initial" LIKE '%6%' or "Middle Initial" LIKE '%7%' or "Middle Initial" LIKE '%8%' or "Middle Initial" LIKE '%9%'
SELECT * FROM mwphy WHERE Ucase("Phone") LIKE '%A%' or Ucase("Phone") LIKE '%B%' or Ucase("Phone") LIKE '%C%' or Ucase("Phone") LIKE '%D%' or Ucase("Phone") LIKE '%E%' or Ucase("Phone") LIKE '%F%' or Ucase("Phone") LIKE '%G%' or Ucase("Phone") LIKE '%H%' or Ucase("Phone") LIKE '%I%' OR Ucase("Phone") LIKE '%J%' or Ucase("Phone") LIKE '%K%' or Ucase("Phone") LIKE '%L%' or Ucase("Phone") LIKE '%M%' or Ucase("Phone") LIKE '%N%' or Ucase("Phone") LIKE '%O%' or Ucase("Phone") LIKE '%P%' or Ucase("Phone") LIKE '%Q%' or Ucase("Phone") LIKE '%R%' OR Ucase("Phone") LIKE '%S%' or Ucase("Phone") LIKE '%T%' or Ucase("Phone") LIKE '%U%' or Ucase("Phone") LIKE '%V%' or Ucase("Phone") LIKE '%X%' or Ucase("Phone") LIKE '%Y%' or Ucase("Phone") LIKE '%Z%'
SELECT * FROM mwphy WHERE "Credentials" LIKE '%0%' or "Credentials" LIKE '%2%' or "Credentials" LIKE '%3%' or "Credentials" LIKE '%4%' or "Credentials" LIKE '%5%' or "Credentials" LIKE '%6%' or "Credentials" LIKE '%7%' or "Credentials" LIKE '%8%' or "Credentials" LIKE '%9%'
SELECT * FROM mwphy WHERE "City" LIKE '%0%' or "City" LIKE '%2%' or "City" LIKE '%3%' or "City" LIKE '%4%' or "City" LIKE '%5%' or "City" LIKE '%6%' or "City" LIKE '%7%' or "City" LIKE '%8%' or "City" LIKE '%9%'
SELECT * FROM mwphy WHERE "State" LIKE '%0%' or "State" LIKE '%2%' or "State" LIKE '%3%' or "State" LIKE '%4%' or "State" LIKE '%5%' or "State" LIKE '%6%' or "State" LIKE '%7%' or "State" LIKE '%8%' or "State" LIKE '%9%'
SELECT * FROM mwphy WHERE Ucase("Zip Code") LIKE '%A%' or Ucase("Zip Code") LIKE '%B%' or Ucase("Zip Code") LIKE '%C%' or Ucase("Zip Code") LIKE '%D%' or Ucase("Zip Code") LIKE '%E%' or Ucase("Zip Code") LIKE '%F%' or Ucase("Zip Code") LIKE '%G%' or Ucase("Zip Code") LIKE '%H%' or Ucase("Zip Code") LIKE '%I%' OR Ucase("Zip Code") LIKE '%J%' or Ucase("Zip Code") LIKE '%K%' or Ucase("Zip Code") LIKE '%L%' or Ucase("Zip Code") LIKE '%M%' or Ucase("Zip Code") LIKE '%N%' or Ucase("Zip Code") LIKE '%O%' or Ucase("Zip Code") LIKE '%P%' or Ucase("Zip Code") LIKE '%Q%' or Ucase("Zip Code") LIKE '%R%' OR Ucase("Zip Code") LIKE '%S%' or Ucase("Zip Code") LIKE '%T%' or Ucase("Zip Code") LIKE '%U%' or Ucase("Zip Code") LIKE '%V%' or Ucase("Zip Code") LIKE '%X%' or Ucase("Zip Code") LIKE '%Y%' or Ucase("Zip Code") LIKE '%Z%'
SELECT * FROM mwphy WHERE Ucase("Office") LIKE '%A%' or Ucase("Office") LIKE '%B%' or Ucase("Office") LIKE '%C%' or Ucase("Office") LIKE '%D%' or Ucase("Office") LIKE '%E%' or Ucase("Office") LIKE '%F%' or Ucase("Office") LIKE '%G%' or Ucase("Office") LIKE '%H%' or Ucase("Office") LIKE '%I%' OR Ucase("Office") LIKE '%J%' or Ucase("Office") LIKE '%K%' or Ucase("Office") LIKE '%L%' or Ucase("Office") LIKE '%M%' or Ucase("Office") LIKE '%N%' or Ucase("Office") LIKE '%O%' or Ucase("Office") LIKE '%P%' or Ucase("Office") LIKE '%Q%' or Ucase("Office") LIKE '%R%' OR Ucase("Office") LIKE '%S%' or Ucase("Office") LIKE '%T%' or Ucase("Office") LIKE '%U%' or Ucase("Office") LIKE '%V%' or Ucase("Office") LIKE '%X%' or Ucase("Office") LIKE '%Y%' or Ucase("Office") LIKE '%Z%'
MWINS (use UPDATE MWPAT SET "First Name" = replace(mwpat.[First Name], '#','') to fix invalid characters)
SELECT * FROM MWINS WHERE "City" LIKE '%0%' or "City" LIKE '%2%' or "City" LIKE '%3%' or "City" LIKE '%4%' or "City" LIKE '%5%' or "City" LIKE '%6%' or "City" LIKE '%7%' or "City" LIKE '%8%' or "City" LIKE '%9%'
SELECT * FROM MWINS WHERE "State" LIKE '%0%' or "State" LIKE '%2%' or "State" LIKE '%3%' or "State" LIKE '%4%' or "State" LIKE '%5%' or "State" LIKE '%6%' or "State" LIKE '%7%' or "State" LIKE '%8%' or "State" LIKE '%9%'
SELECT * FROM MWINS WHERE Ucase("Zip Code") LIKE '%A%' or Ucase("Zip Code") LIKE '%B%' or Ucase("Zip Code") LIKE '%C%' or Ucase("Zip Code") LIKE '%D%' or Ucase("Zip Code") LIKE '%E%' or Ucase("Zip Code") LIKE '%F%' or Ucase("Zip Code") LIKE '%G%' or Ucase("Zip Code") LIKE '%H%' or Ucase("Zip Code") LIKE '%I%' OR Ucase("Zip Code") LIKE '%J%' or Ucase("Zip Code") LIKE '%K%' or Ucase("Zip Code") LIKE '%L%' or Ucase("Zip Code") LIKE '%M%' or Ucase("Zip Code") LIKE '%N%' or Ucase("Zip Code") LIKE '%O%' or Ucase("Zip Code") LIKE '%P%' or Ucase("Zip Code") LIKE '%Q%' or Ucase("Zip Code") LIKE '%R%' OR Ucase("Zip Code") LIKE '%S%' or Ucase("Zip Code") LIKE '%T%' or Ucase("Zip Code") LIKE '%U%' or Ucase("Zip Code") LIKE '%V%' or Ucase("Zip Code") LIKE '%X%' or Ucase("Zip Code") LIKE '%Y%' or Ucase("Zip Code") LIKE '%Z%'
MWADD (use UPDATE MWADD SET "First Name" = replace(mwpat.[First Name], '#','') to fix invalid characters)
SELECT * FROM MWADD WHERE "City" LIKE '%0%' or "City" LIKE '%2%' or "City" LIKE '%3%' or "City" LIKE '%4%' or "City" LIKE '%5%' or "City" LIKE '%6%' or "City" LIKE '%7%' or "City" LIKE '%8%' or "City" LIKE '%9%'
SELECT * FROM MWADD WHERE "State" LIKE '%0%' or "State" LIKE '%2%' or "State" LIKE '%3%' or "State" LIKE '%4%' or "State" LIKE '%5%' or "State" LIKE '%6%' or "State" LIKE '%7%' or "State" LIKE '%8%' or "State" LIKE '%9%'
SELECT * FROM MWADD WHERE Ucase("Zip Code") LIKE '%A%' or Ucase("Zip Code") LIKE '%B%' or Ucase("Zip Code") LIKE '%C%' or Ucase("Zip Code") LIKE '%D%' or Ucase("Zip Code") LIKE '%E%' or Ucase("Zip Code") LIKE '%F%' or Ucase("Zip Code") LIKE '%G%' or Ucase("Zip Code") LIKE '%H%' or Ucase("Zip Code") LIKE '%I%' OR Ucase("Zip Code") LIKE '%J%' or Ucase("Zip Code") LIKE '%K%' or Ucase("Zip Code") LIKE '%L%' or Ucase("Zip Code") LIKE '%M%' or Ucase("Zip Code") LIKE '%N%' or Ucase("Zip Code") LIKE '%O%' or Ucase("Zip Code") LIKE '%P%' or Ucase("Zip Code") LIKE '%Q%' or Ucase("Zip Code") LIKE '%R%' OR Ucase("Zip Code") LIKE '%S%' or Ucase("Zip Code") LIKE '%T%' or Ucase("Zip Code") LIKE '%U%' or Ucase("Zip Code") LIKE '%V%' or Ucase("Zip Code") LIKE '%X%' or Ucase("Zip Code") LIKE '%Y%' or Ucase("Zip Code") LIKE '%Z%'
SELECT * FROM MWADD WHERE "Name" LIKE '%&%' or "Name" LIKE '%)%' OR "Name" LIKE '%(%' OR "Name" LIKE '%[%' OR "Name" LIKE '%]%' OR "Name" LIKE '%!%' OR "Name" LIKE '%#%' OR "Name" LIKE '%$%'
UPDATE MWADD SET "Name" = replace(mwADD.[Name], '#','') WHERE "Name" LIKE '%&%' or "Name" LIKE '%)%' OR "Name" LIKE '%(%' OR "Name" LIKE '%[%' OR "Name" LIKE '%]%' OR "Name" LIKE '%!%' OR "Name" LIKE '%#%' OR "Name" LIKE '%$%'
MWRPH (use UPDATE MWRPH SET "First Name" = replace(mwpat.[First Name], '#','') to fix invalid characters)
SELECT * FROM MWRPH WHERE "Last Name" LIKE '%0%' or "Last Name" LIKE '%2%' or "Last Name" LIKE '%3%' or "Last Name" LIKE '%4%' or "Last Name" LIKE '%5%' or "Last Name" LIKE '%6%' or "Last Name" LIKE '%7%' or "Last Name" LIKE '%8%' or "Last Name" LIKE '%9%'
SELECT * FROM MWRPH WHERE "Last Name" LIKE '%&%' or "Last Name" LIKE '%)%' OR "Last Name" LIKE '%(%' OR "Last Name" LIKE '%[%' OR "Last Name" LIKE '%]%' OR "Last Name" LIKE '%!%' OR "Last Name" LIKE '%#%' OR "Last Name" LIKE '%$%'
SELECT * FROM MWRPH WHERE "First Name" LIKE '%0%' or "First Name" LIKE '%2%' or "First Name" LIKE '%3%' or "First Name" LIKE '%4%' or "First Name" LIKE '%5%' or "First Name" LIKE '%6%' or "First Name" LIKE '%7%' or "First Name" LIKE '%8%' or "First Name" LIKE '%9%'
SELECT * FROM MWRPH WHERE "First Name" LIKE '%&%' or "First Name" LIKE '%)%' OR "First Name" LIKE '%(%' OR "First Name" LIKE '%[%' OR "First Name" LIKE '%]%' OR "First Name" LIKE '%!%' OR "First Name" LIKE '%#%' OR "First Name" LIKE '%$%'
SELECT * FROM MWRPH WHERE "Middle Initial" LIKE '%0%' or "Middle Initial" LIKE '%2%' or "Middle Initial" LIKE '%3%' or "Middle Initial" LIKE '%4%' or "Middle Initial" LIKE '%5%' or "Middle Initial" LIKE '%6%' or "Middle Initial" LIKE '%7%' or "Middle Initial" LIKE '%8%' or "Middle Initial" LIKE '%9%'
SELECT * FROM MWRPH WHERE "Middle Initial" LIKE '%&%' or "Middle Initial" LIKE '%)%' OR "Middle Initial" LIKE '%(%' OR "Middle Initial" LIKE '%[%' OR "Middle Initial" LIKE '%]%' OR "Middle Initial" LIKE '%!%' OR "Middle Initial" LIKE '%#%' OR "Middle Initial" LIKE '%$%'
SELECT * FROM MWRPH WHERE Ucase("Phone") LIKE '%A%' or Ucase("Phone") LIKE '%B%' or Ucase("Phone") LIKE '%C%' or Ucase("Phone") LIKE '%D%' or Ucase("Phone") LIKE '%E%' or Ucase("Phone") LIKE '%F%' or Ucase("Phone") LIKE '%G%' or Ucase("Phone") LIKE '%H%' or Ucase("Phone") LIKE '%I%' OR Ucase("Phone") LIKE '%J%' or Ucase("Phone") LIKE '%K%' or Ucase("Phone") LIKE '%L%' or Ucase("Phone") LIKE '%M%' or Ucase("Phone") LIKE '%N%' or Ucase("Phone") LIKE '%O%' or Ucase("Phone") LIKE '%P%' or Ucase("Phone") LIKE '%Q%' or Ucase("Phone") LIKE '%R%' OR Ucase("Phone") LIKE '%S%' or Ucase("Phone") LIKE '%T%' or Ucase("Phone") LIKE '%U%' or Ucase("Phone") LIKE '%V%' or Ucase("Phone") LIKE '%X%' or Ucase("Phone") LIKE '%Y%' or Ucase("Phone") LIKE '%Z%'
SELECT * FROM MWRPH WHERE "Credentials" LIKE '%0%' or "Credentials" LIKE '%2%' or "Credentials" LIKE '%3%' or "Credentials" LIKE '%4%' or "Credentials" LIKE '%5%' or "Credentials" LIKE '%6%' or "Credentials" LIKE '%7%' or "Credentials" LIKE '%8%' or "Credentials" LIKE '%9%'
SELECT * FROM MWRPH WHERE "City" LIKE '%0%' or "City" LIKE '%2%' or "City" LIKE '%3%' or "City" LIKE '%4%' or "City" LIKE '%5%' or "City" LIKE '%6%' or "City" LIKE '%7%' or "City" LIKE '%8%' or "City" LIKE '%9%'
SELECT * FROM MWRPH WHERE "State" LIKE '%0%' or "State" LIKE '%2%' or "State" LIKE '%3%' or "State" LIKE '%4%' or "State" LIKE '%5%' or "State" LIKE '%6%' or "State" LIKE '%7%' or "State" LIKE '%8%' or "State" LIKE '%9%'
SELECT * FROM MWRPH WHERE Ucase("Zip Code") LIKE '%A%' or Ucase("Zip Code") LIKE '%B%' or Ucase("Zip Code") LIKE '%C%' or Ucase("Zip Code") LIKE '%D%' or Ucase("Zip Code") LIKE '%E%' or Ucase("Zip Code") LIKE '%F%' or Ucase("Zip Code") LIKE '%G%' or Ucase("Zip Code") LIKE '%H%' or Ucase("Zip Code") LIKE '%I%' OR Ucase("Zip Code") LIKE '%J%' or Ucase("Zip Code") LIKE '%K%' or Ucase("Zip Code") LIKE '%L%' or Ucase("Zip Code") LIKE '%M%' or Ucase("Zip Code") LIKE '%N%' or Ucase("Zip Code") LIKE '%O%' or Ucase("Zip Code") LIKE '%P%' or Ucase("Zip Code") LIKE '%Q%' or Ucase("Zip Code") LIKE '%R%' OR Ucase("Zip Code") LIKE '%S%' or Ucase("Zip Code") LIKE '%T%' or Ucase("Zip Code") LIKE '%U%' or Ucase("Zip Code") LIKE '%V%' or Ucase("Zip Code") LIKE '%X%' or Ucase("Zip Code") LIKE '%Y%' or Ucase("Zip Code") LIKE '%Z%'
SELECT * FROM MWRPH 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 '%#%' OR "Zip Code" LIKE '%$%'
SELECT * FROM MWRPH WHERE Ucase("Office") LIKE '%A%' or Ucase("Office") LIKE '%B%' or Ucase("Office") LIKE '%C%' or Ucase("Office") LIKE '%D%' or Ucase("Office") LIKE '%E%' or Ucase("Office") LIKE '%F%' or Ucase("Office") LIKE '%G%' or Ucase("Office") LIKE '%H%' or Ucase("Office") LIKE '%I%' OR Ucase("Office") LIKE '%J%' or Ucase("Office") LIKE '%K%' or Ucase("Office") LIKE '%L%' or Ucase("Office") LIKE '%M%' or Ucase("Office") LIKE '%N%' or Ucase("Office") LIKE '%O%' or Ucase("Office") LIKE '%P%' or Ucase("Office") LIKE '%Q%' or Ucase("Office") LIKE '%R%' OR Ucase("Office") LIKE '%S%' or Ucase("Office") LIKE '%T%' or Ucase("Office") LIKE '%U%' or Ucase("Office") LIKE '%V%' or Ucase("Office") LIKE '%X%' or Ucase("Office") LIKE '%Y%' or Ucase("Office") LIKE '%Z%'
OHAPP
UPDATE ohapp SET "DATE CREATED" = '02/10/1969' WHERE "date created" is null
UPDATE ohapp SET "DATE CREATED" = '02/10/1969' WHERE "DATE CREATED" = '12/30/1899'
SELECT * FROM OHAPP WHERE NOT [PROVIDER] IN(SELECT [CODE] FROM MWPHY)
DELETE FROM OHAPP WHERE NOT [PROVIDER] IN(SELECT [CODE] FROM MWPHY)
MWPAT - CHECK SOME OTHER FIELDS
SELECT [WEIGHT] FROM MWPAT ORDER BY [WEIGHT]
SELECT * FROM MWPAT ORDER BY "DATE CREATED" DESC
UPDATE MWPAT SET [WEIGHT] = 0 WHERE [WEIGHT] <> 0
SELECT [SEX] asd, mwpat.* FROM MWPAT WHERE [SEX] is null ORDER BY [SEX] DESC
UPDATE MWPAT SET [SEX] = 'Male' WHERE [SEX] is null
MWCAS
' DELETE CASES THAT DO NOT MATCH. PATIENTS THAT DO NOT EXIST BUT HAVE A CASE
SELECT * FROM MWCAS WHERE NOT [CHART NUMBER] IN(SELECT [CHART NUMBER] FROM MWPAT)
DELETE FROM MWCAS WHERE NOT [CHART NUMBER] IN(SELECT [CHART NUMBER] FROM MWPAT)
'SELECT * FROM MWCAS WHERE NOT [BILLING CODE] IN(SELECT [BILLING CODE] FROM MWBCO)
MWCLA
SELECT * FROM MWCLA WHERE NOT [CHART NUMBER] IN(SELECT [CHART NUMBER] FROM MWPAT)
DELETE FROM MWCLA WHERE NOT [CHART NUMBER] IN(SELECT [CHART NUMBER] FROM MWPAT)
' find claims with an invalid EMC receiver that does not exist. Set receive to null
SELECT * FROM MWCLA WHERE NOT [EMC RECEIVER 2] IN(SELECT [CODE] FROM MWECR)
UPDATE MWCLA SET [EMC RECEIVER 1] = NULL WHERE NOT [EMC RECEIVER 1] IN(SELECT [CODE] FROM MWECR)
SELECT * FROM MWCLA WHERE NOT [EMC RECEIVER 2] IN(SELECT [CODE] FROM MWECR)
UPDATE MWCLA SET [EMC RECEIVER 2] = NULL WHERE NOT [EMC RECEIVER 2] IN(SELECT [CODE] FROM MWECR)
SELECT * FROM MWCLA WHERE NOT [EMC RECEIVER 2] IN(SELECT [CODE] FROM MWECR)
UPDATE MWCLA SET [EMC RECEIVER 3] = NULL WHERE NOT [EMC RECEIVER 3] IN(SELECT [CODE] FROM MWECR)
MWTRN
' Find transactions that have a claim number that does not exist
SELECT [CLAIM NUMBER] FROM MWTRN WHERE NOT [CLAIM NUMBER] IN(SELECT [CLAIM NUMBER] FROM MWCLA) AND [CLAIM
NUMBER] <> 0
UPDATE MWTRN SET [CLAIM NUMBER] = 0 WHERE NOT [CLAIM NUMBER] IN(SELECT [CLAIM NUMBER] FROM MWCLA) AND [CLAIM
NUMBER] <> 0
' Find transactions that do not have a matching chart number - orphan transactions
SELECT * FROM MWTRN WHERE NOT [CHART NUMBER] IN(SELECT [CHART NUMBER] FROM MWPAT) ORDER BY [CHART NUMBER]
DELETE FROM MWTRN WHERE NOT [CHART NUMBER] IN(SELECT [CHART NUMBER] FROM MWPAT)
' INVALID CHART NUMBER IN TRANSACTION TABLE
SELECT * FROM MWTRN WHERE "CHART NUMBER" LIKE '%&%' OR "CHART NUMBER" LIKE '%:%' OR "CHART NUMBER" LIKE '%;%'
OR "CHART NUMBER" LIKE '%-%' or "CHART NUMBER" LIKE '%)%' OR "CHART NUMBER" LIKE '%(%' OR "CHART NUMBER" LIKE
'%[%' OR "CHART NUMBER" LIKE '%]%' OR "CHART NUMBER" LIKE '%!%' OR "CHART NUMBER" LIKE '%#%' OR "CHART NUMBER"
LIKE '%$%'
' INVALID INSURANCE CATEGORY IN TRANSACTION TABLE
SELECT [INSURANCE CATEGORY] ASD, MWTRN.* FROM MWTRN WHERE [INSURANCE CATEGORY] <> 'A' ORDER BY [INSURANCE
CATEGORY] ASC
UPDATE MWTRN SET [INSURANCE CATEGORY] = NULL WHERE [INSURANCE CATEGORY] <> 'A'
' FIND INVALID USER CODES
SELECT [USER CODE] FROM MWTRN WHERE NOT [USER CODE] IN(SELECT [USER CODE] FROM MWSEC) ORDER BY [USER CODE]
' FIND TRANSACTIONS WITHOUT A TRANSACTION TYPE
SELECT [TRANSACTION TYPE] FROM MWTRN WHERE [TRANSACTION TYPE] IS NULL
********************************************************************************************************************************************
NOTE: The case below was used for a practice where the Program Options / FORCE DOCUMENT NUMBERS was NOT checked, causing the ERA to use a complete different document number when applying the Medicare payments electronically. This cause an error on the screen to the user "There are payment that must be applied. Do you want to apply them now?" blocking user and not allowing user to get out of the screen. The cause , when using document number (it must be checked in program option - use it a default for all practices using ERA), the ERA payment where posted on a separate screen, causing charges to be on one documents by itself and payments on another document by itself without any charges.
JOIN BETWEEN MWPX AND MWTRN
Linking between mwtrn and mwpx happens between
Payment Reference = Entry Number
Charge Reference = Entry Number
Select * FROM mwpax WHERE mwpax.[payment reference] not IN (SELECT [Entry number] FROM mwtrn WHERE mwtrn.[entry number] = mwpax.[Payment reference])
Select * FROM mwpax WHERE mwpax.[charge reference] not IN (SELECT [Entry number] FROM mwtrn WHERE mwtrn.[entry number] = mwpax.[Charge reference])
Check MWTRN to see if "Document Number" is blank. This will cause problems
select * from mwtrn where "Document Number" is null or "Document Number" like '%.%'
Troubleshooting MWTRN and MWPAX (payment files that relates mwtrn charges to payments)
Select "Chart Number", "Case Number", "Entry Number", "Claim Number", "Date From", "Amount", "Units", "Guarantor Amount Paid", "Adjustment Amount", "Insurance 1 Amount Paid", "Insurance 2 Amount Paid", "Insurance 3 Amount Paid", "Document Number", "Description", "Procedure Code", "Transaction Type", "Unapplied Amount" from mwtrn where ("chart number" = '00012195' and "Case Number" = 41280)
Select * from mwpax where "Payment Reference" IN (298991, 293378, 298515, 298516, 298517, 299887) or "Charge Reference" in (298991, 293378, 298515, 298516, 298517, 299887)
(Below is a query doing the relation between mwpax and mwtrn for one patient, one case, one transaction, TO DO ALL PATIENTS REMOVE THE HIGHLIGHTED SECTION)
Select m9.[Payment Reference], m9.[Charge Reference], m11.[Document Number] FROM mwpax m9, mwtrn m11 WHERE m9.[Charge Reference] = m11.[Entry Number] and m11.[Chart Number] = '00012195' and m11.[Case Number] = 41280
(ALL PATIENTS)
Select m9.[Payment Reference], m9.[Charge Reference], m11.[Document Number] FROM mwpax m9, mwtrn m11 WHERE m9.[Charge Reference] = m11.[Entry Number]
(Right click on results to export above results to a new table on "C:\MWPMTREF.ADT" to be able to do the next step)
Select m11.[Chart Number], m11.[Case number], m11.[Entry Number], m9.[Payment Reference], m9.[Charge Reference], m9.[Document Number], m11.[Document Number] DocumentShouldBe FROM "C:\MWPMTREF.ADT" m9, mwtrn m11 WHERE (m9.[Payment Reference] = m11.[Entry Number]) AND (NOT m9.[Document Number] = m11.[Document Number])
(Right click on results to export results to a new table on "C:\MWTRNTOPROCESS.ADT" to be able to do the next step)
Update mwtrn set mwtrn.[Document Number] = "C:\MWTRNTOPROCESS.ADT".[Document Number] FROM mwtrn, "C:\MWTRNTOPROCESS.ADT" WHERE "C:\MWTRNTOPROCESS.ADT".[Payment Reference] = mwtrn.[Entry Number]
The following is to list the unmatched document numbers in mwtrn. Then right click on results to export results to a new table on "c:\MWTRANWITHONLY1DOCUMENTREC.ADT"
Select mwtrn.[Chart Number] as MyChart, mwtrn.[Case Number] as MyCase, mwtrn.[Document Number], count(mwtrn.[Document Number]) as MyCount FROM mwtrn WHERE not [Document Number] is null GROUP BY mwtrn.[Document Number], mwtrn.[Chart Number], mwtrn.[Case Number] HAVING COUNT(mwtrn.[Document Number]) = 1 order by mwtrn.[Chart Number]
Some of the records listed above can and should be deleted but some must be kept like charges without any payments. To differentiate them, use the list in c:\MWTRANWITHONLY1DOCUMENTREC.ADT" and where the transactions are COMMENTS - Transaction type = Z (BACKUP DATA FIRST)
** View and check records to be delete BEFORE deleting. Description should something like "Billed Medicare..."
Select * from mwtrn, "c:\MWTRANWITHONLY1DOCUMENTREC.ADT" M2 where ((mwtrn.[Chart Number] = M2.[MyChart]) AND (mwtrn.[Document Number] = M2.[Document Number]) AND (mwtrn.[Case Number] = M2.[MyCase]) AND (mwtrn.[Transaction Type] = 'Z'))
** Tag items to delete
UPDATE MWTRN SET "User Code" = 'TODELETE' FROM mwtrn, "c:\MWTRANWITHONLY1DOCUMENTREC.ADT" M2 where ((mwtrn.[Chart Number] = M2.[MyChart]) AND (mwtrn.[Document Number] = M2.[Document Number]) AND (mwtrn.[Case Number] = M2.[MyCase]) AND (mwtrn.[Transaction Type] = 'Z'))
** Delete items tagged
DELETE FROM MWTRN WHERE "User Code" = 'TODELETE'
DONE!!!
********************************************************************************************************************************************
When we do the data conversion for some practice, the system defaults to "is Null" on fields that are True or False under the case showing a gray area with a check mark for the value. This causes claims to get denied electronically. To fix this problems use the following code (example Jacobso @ Greenwich Gyno)
Update mwcas SET "Policy Is Capitated" = False, "Policy #2 Crossover Claim" = False, "Related to Employment" = False, "Same or Similar Symptoms" = False, "Emergency" = False, "EPSDT" = False, "Family Planning" = False, "Outside Lab Work" = False, "Pregnancy Indicator" = False, "Deductible Met" = False, "Certification_Code_Applies" = False where "Policy Is Capitated" is null and "Policy #2 Crossover Claim" is null and "Related to Employment" is null and "Emergency" is null and "EPSDT" is null
Setup default values for MWPRO. I used the following code to import procedure codes (FEE schedule) for Burbige.
** Note: Import FEE schedule from TXT file. Export / Import into free table using | as delimiter between fields. Include field names as part of TXT table.
INSERT INTO mwpro("Code 1", "Code 2", "Code 3", "Description", "Type", "Type of Service", "Time To Do Procedure", "Insurance Category", "Patient Only Responsible", "Medicare Allowed Amount", "Amount A", "Amount B", "Amount C", "Amount D", "Amount E", "Amount F", "Amount G", "Amount H", "Amount I", "Amount J", "Amount K", "Amount L", "Amount M", "Amount N", "Amount O", "Amount P", "Amount Q", "Amount R", "Amount S", "Amount T", "Amount U", "Amount V", "Amount W", "Amount X", "Amount Y", "Amount Z", "Taxable", "Inactive", "Adjustment Amount Negativ", "PrePayment", "User Code", "Approved", "Recall Code", "Tax Rate", "Purchased Service", "Default Units", "Bill_Patient", "Cost of Service/Product") SELECT trim("Code 1"), trim("Code 1"), trim("Code 1"), trim("Description"), 'A', trim("Type of Service"), 0, 'A', False, 0.00, "Amount A", "Amount B", "Amount C", "Amount D", "Amount E", 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, False, False, False, False, 'TCSP', True, False, 0.00, False, 0, False, 0 FROM "C:\PROC1.ADT"
Find provider pin numbers for each insurance company. Run following query then export results to TXT file
select mwins.name, mwpins.* from "mwins" , "mwpins" where "mwins"."Code" = "mwpins"."Insurance Code" AND "PROVIDER CODE" = 'MT'
Set Crossover to TRUE when the insurance code is Mediare. The insurance code is FIR01 in this sample
UPDATE MWCAS SET [Policy #2 Crossover Claim]=TRUE where "insurance carrier #1" = 'FIR01'
**********************************************************************************************************************************************************
LINK BETWEEN MWTRN, MWCAS, MWINS, AND MWINS -given to Joel Landau to export data into Excel from Medisoft
SELECT
TRN.[CHART NUMBER],
[DATE FROM] as DOS,
PAT.[LAST NAME],
PAT.[FIRST NAME],
PAT.[DATE OF BIRTH] AS DOB,
PAT.[STREET 1] AS Street,
PAT.[CITY] AS City,
PAT.[Zip Code] as Zip,
PAT.[Phone 1] as Phone,
PAT.[Sex] as Sex,
PAT.[Social Security Number] as SSN,
TRN.[Case Number],
CAS.[Insurance Carrier #1] AS Insurance1Code,SELECT
TRN.[CHART NUMBER],
[DATE FROM] as DOS,
PAT.[LAST NAME],
PAT.[FIRST NAME],
PAT.[DATE OF BIRTH] AS DOB,
PAT.[STREET 1] AS Street,
PAT.[CITY] AS City,
PAT.[Zip Code] as Zip,
PAT.[Phone 1] as Phone,
PAT.[Sex] as Sex,
PAT.[Social Security Number] as SSN,
TRN.[Case Number],
CAS.[Insurance Carrier #1] AS Insurance1Code,
CAS.[Policy Number #1] as PolicyNo,
INS.[Name] as Insurance,
[Procedure Code] as CPT_Code,
[Diagnosis Code 1] as Diagnosis,
[Amount] as Charge,
[Adjustment Amount] as Adjustment,
[Insurance 1 Amount Paid] + [Insurance 2 Amount Paid] + [Insurance 3 Amount Paid] + [Guarantor Amount Paid] as Receipts,
([Adjustment Amount] + [Insurance 1 Amount Paid] + [Insurance 2 Amount Paid] + [Insurance 3 Amount Paid] + [Guarantor Amount Paid]) + [Amount] as Balance
FROM
MWTRN TRN, MWCAS CAS, MWINS INS, MWPAT PAT
WHERE
(TRN.[Insurance 1 Responsible] = TRUE AND TRN.[Insurance 1 Paid] = FALSE)
AND
(TRN.[CASE NUMBER] = CAS.[CASE NUMBER]) AND (CAS.[Insurance Carrier #1] = INS.[CODE] AND CAS.[CHART NUMBER] = PAT.[CHART NUMBER])
AND
(CAS.[Insurance Carrier #1] = 'HEA03' )
AND
(TRN.[TRANSACTION TYPE] <= 'H')
INS.[Name] as Insurance,
[Procedure Code] as CPT_Code,
[Diagnosis Code 1] as Diagnosis,
[Amount] as Charge,
[Adjustment Amount] as Adjustment,
[Insurance 1 Amount Paid] + [Insurance 2 Amount Paid] + [Insurance 3 Amount Paid] + [Guarantor Amount Paid] as Receipts
FROM
MWTRN TRN, MWCAS CAS, MWINS INS, MWPAT PAT
WHERE
(TRN.[Insurance 1 Responsible] = TRUE AND TRN.[Insurance 1 Paid] = FALSE)
AND
(TRN.[CASE NUMBER] = CAS.[CASE NUMBER]) AND (CAS.[Insurance Carrier #1] = INS.[CODE] AND CAS.[CHART NUMBER] = PAT.[CHART NUMBER])
AND
(CAS.[Insurance Carrier #1] = 'HEA03' )
AND
(TRN.[TRANSACTION TYPE] <= 'H')
**************************************************************************************************************************************************************
The following script is to select all patients that have a particular diagosis code under Code 1, Code 2, Code 3, Code 4. The system only lists diagnosis code 1 but it searches on 1,2,3, and 4
SELECT
TRN.[CHART NUMBER],
PAT.[LAST NAME],
PAT.[FIRST NAME],
PAT.[DATE OF BIRTH] AS DOB,
PAT.[STREET 1] AS Street,
PAT.[CITY] AS City,
PAT.[Zip Code] as Zip,
PAT.[Phone 1] as Phone,
PAT.[Sex] as Sex,
PAT.[Social Security Number] as SSN,
[Diagnosis Code 1] as Diagnosis
FROM
MWTRN TRN, MWCAS CAS, MWINS INS, MWPAT PAT
WHERE
(TRN.[CASE NUMBER] = CAS.[CASE NUMBER]) AND (CAS.[CHART NUMBER] = PAT.[CHART NUMBER])
AND
(TRN.[DIAGNOSIS CODE 1] = '172.9'
OR
TRN.[DIAGNOSIS CODE 2] = '172.9'
OR
TRN.[DIAGNOSIS CODE 3] = '172.9'
OR
TRN.[DIAGNOSIS CODE 4] = '172.9')
GROUP BY TRN.[CHART NUMBER],
PAT.[LAST NAME],
PAT.[FIRST NAME],
PAT.[DATE OF BIRTH],
PAT.[STREET 1],
PAT.[CITY],
PAT.[Zip Code],
PAT.[Phone 1],
PAT.[Sex],
PAT.[Social Security Number],
[Diagnosis Code 1]
**************************************************************************************************************************************************************
SELECT ALL CASES THAT DO NOT HAVE AN ASSIGNED PROVIDER
SELECT * FROM MWCAS WHERE "ASSIGNED PROVIDER" IS NULL
Move PHONE NUMBER from Extra to Fax field in Ins file
update mwrph set "Fax" = '(' + Left ("extra 2", 3) +
')' + Left(right ("extra 2",7),3) + '-' + Right("Extra
2", 4)Update Phone 1, Phone 2 to include (203) when less than 8 Char
UPDATE mwpat SET "Phone 1" = '(203)' + RTRIM("Phone 1") WHERE LENGTH("Phone 1") <= 8 AND "Phone 1" <> ''SELECT ALL CASES WHERE THE GUARANTOR IS NOT THE SAME AS THE PATIENT
SELECT * FROM MWCAS WHERE Not "Chart Number" = "Guarantor";
UPDATE ALL CASES WHERE THE GUARANTOR IS NOT THE SAME AS THE PATIENT WITH THE PATIENT'S CHART NUMBER
UPDATE MWCAS SET "Guarantor" = "Chart Number" WHERE Not "Chart Number" = "Guarantor";
How to Update a phone number that is in the form of 2034263610 to (203)426-3610
Update mwrph set "Fax" = '(' + Left ("extra 2", 3) + ')' + Left(right ("extra 2",7),3) + '-' + Right("Extra 2", 4)How to update CASE file where user forgot to select an Illness Indicator (Illness or Injury).
(Below script assumes that user entered an ijury/illness date)
Update mwcas set [illness indicator] = 'Injury' from mwcas where not [Date of Injury/Illness] is null
**************************************************************************************************************************************************************
How to fix PINS for a provider.
Update MWPINS table PROVIDER INSURANCE PINS QUALIFIERS (Provider Identification Number) with G2 for the ones that are blank
SELECT * FROM MWPINS WHERE "TYPE" = 'P' and [GROUP_QUALIFIER] IS NULL
UPDATE MWPINS SET [GROUP_QUALIFIER] = 'G2', [PIN_QUALIFIER] = 'G2' WHERE "TYPE" = 'P' and [GROUP_QUALIFIER] IS NULL
Update MWPINS table PROVIDER INSURANCE PINS (Provider Identification Number) with the practice TAX ID for the ones that are blank
SELECT * FROM MWPINS WHERE "TYPE" = 'P' AND [PIN NUMBER] IS NULL
UPDATE MWPINS SET [PIN NUMBER] = 'XXXXXXXX' WHERE "TYPE" = 'P' AND [PIN NUMBER] IS NULL
UPDATE MWPINS SET [GROUP ID] = 'XXXXXXXX' WHERE "TYPE" = 'P' AND [GROUP ID] IS NULL
Insert missing PINS for each insurance company for a single provider ( Single provider in this case is CDILA in this case)
The following is to view what will be INSERTED into MWPINS
SELECT
"CODE", "Provider Code", "Pin Number", "Group ID", "PIN_QUALIFIER", "GROUP_QUALIFIER", "TYPE"
FROM
(SELECT [CODE] as "CODE", 'CDILA' as "Provider Code", '061060714' as "Pin Number", '061060714' as "Group ID", 'G2' as "PIN_QUALIFIER", 'G2' as "GROUP_QUALIFIER", 'P' as "TYPE"
FROM
MWINS
WHERE
MWINS.[CODE] NOT IN (SELECT [INSURANCE CODE] FROM MWPINS WHERE MWPINS.[PROVIDER CODE] = 'CDILA' AND MWPINS.[INSURANCE CODE] = MWINS.[CODE])) as "MyTable"
The following is to INSERT the records into MWPINS
INSERT INTO
MWPINS ( "INSURANCE CODE", "Provider Code", "Pin Number", "Group ID", "PIN_QUALIFIER", "GROUP_QUALIFIER", "TYPE" )
SELECT
"CODE", "Provider Code", "Pin Number", "Group ID", "PIN_QUALIFIER", "GROUP_QUALIFIER", "TYPE"
FROM
(SELECT [CODE] as "CODE", 'CDILA' as "Provider Code", '061060714' as "Pin Number", '061060714' as "Group ID", 'G2' as "PIN_QUALIFIER", 'G2' as "GROUP_QUALIFIER", 'P' as "TYPE"
FROM
MWINS
WHERE
MWINS.[CODE]
NOT IN (
SELECT [INSURANCE CODE] FROM MWPINS WHERE MWPINS.[PROVIDER CODE] = 'CDILA' AND MWPINS.[INSURANCE CODE] = MWINS.[CODE])) as "MyTable"
**************************************************************************************************************************************************************
Patient balance for a particular procedure
SELECT
TRN.[CHART NUMBER],
PAT.[LAST NAME],
PAT.[FIRST NAME],
SUM([Amount]) as Charge,
SUM([Adjustment Amount]) as Adjustment,
SUM([Insurance 1 Amount Paid] + [Insurance 2 Amount Paid] + [Insurance 3 Amount Paid] + [Guarantor Amount Paid]) as Receipts,
SUM(([Adjustment Amount] + [Insurance 1 Amount Paid] + [Insurance 2 Amount Paid] + [Insurance 3 Amount Paid] + [Guarantor Amount Paid]) + [Amount]) as Balance
FROM
MWTRN TRN, MWCAS CAS, MWINS INS, MWPAT PAT
WHERE
(TRN.[Insurance 1 Responsible] = TRUE)
AND
(TRN.[CASE NUMBER] = CAS.[CASE NUMBER]) AND (CAS.[Insurance Carrier #1] = INS.[CODE] AND CAS.[CHART NUMBER] = PAT.[CHART NUMBER])
AND
(TRN.[TRANSACTION TYPE] <= 'H')
AND
(TRN.[PROCEDURE CODE] = 'Q9949')
AND
(TRN.[DATE FROM] >= CONVERT('01/01/2008',SQL_DATE) AND TRN.[DATE FROM] <= CONVERT('03/28/2008',SQL_DATE))
GROUP BY TRN.[CHART NUMBER], [LAST NAME], [FIRST NAME]
**************************************************************************************************************************************************************
User wants to get a list of patients for Medicare only that have:
A) Proc Code: 78608 and 78815 (these are two separate transactions)
or
B) Proc Code: 78608 and 78816 (these are two separate transactions)To do this, we create temp tables on C:
C:\PROC1 = 78608
C:\PROC2 = 78815
C:\PROC3 = 78816
SELECT
TRN.[CHART NUMBER],
[DATE FROM] as DOS,
TRN.[Case Number],
INS.[Name] as Insurance,
[Procedure Code] as CPT_Code,
[Diagnosis Code 1] as Diagnosis,
[Amount] as Charge,
[Adjustment Amount] as Adjustment,
[Insurance 1 Amount Paid] + [Insurance 2 Amount Paid] + [Insurance 3 Amount Paid] + [Guarantor Amount Paid] as Receipts,
([Adjustment Amount] + [Insurance 1 Amount Paid] + [Insurance 2 Amount Paid] + [Insurance 3 Amount Paid] + [Guarantor Amount Paid]) + [Amount] as Balance FROM
MWTRN TRN, MWCAS CAS, MWINS INS, MWPAT PAT
WHERE
(UCASE(INS.[Name]) like '%MEDICARE%' ) AND
(TRN.[CASE NUMBER] = CAS.[CASE NUMBER]) AND (CAS.[Insurance Carrier #1] = INS.[CODE] AND CAS.[CHART NUMBER] = PAT.[CHART NUMBER])
AND
(TRN.[Procedure Code] = '78608')
AND
(TRN.[TRANSACTION TYPE] <= 'H')
ORDER BY TRN.[CHART NUMBER] ASC
Export results to C:\PROC1.ADTSELECT
TRN.[CHART NUMBER],
[DATE FROM] as DOS,
TRN.[Case Number],
INS.[Name] as Insurance,
[Procedure Code] as CPT_Code,
[Diagnosis Code 1] as Diagnosis,
[Amount] as Charge,
[Adjustment Amount] as Adjustment,
[Insurance 1 Amount Paid] + [Insurance 2 Amount Paid] + [Insurance 3 Amount Paid] + [Guarantor Amount Paid] as Receipts,
([Adjustment Amount] + [Insurance 1 Amount Paid] + [Insurance 2 Amount Paid] + [Insurance 3 Amount Paid] + [Guarantor Amount Paid]) + [Amount] as Balance FROM
MWTRN TRN, MWCAS CAS, MWINS INS, MWPAT PAT
WHERE
(UCASE(INS.[Name]) like '%MEDICARE%' )
AND
(TRN.[CASE NUMBER] = CAS.[CASE NUMBER]) AND (CAS.[Insurance Carrier #1] = INS.[CODE] AND CAS.[CHART NUMBER] = PAT.[CHART NUMBER])
AND
(TRN.[Procedure Code] = '78815')
AND
(TRN.[TRANSACTION TYPE] <= 'H')
ORDER BY TRN.[CHART NUMBER] ASCExport results to C:\PROC2.ADT
' Get patients final list
SELECT TRN1.* FROM "C:\PROC1.ADT" TRN1, "C:\PROC2.ADT" TRN2 WHERE (TRN1.[CHART NUMBER] = TRN1.[CHART NUMBER]) AND (TRN1.[DOS] = TRN2.[DOS])
SELECT
TRN.[CHART NUMBER],
[DATE FROM] as DOS,
TRN.[Case Number],
INS.[Name] as Insurance,
[Procedure Code] as CPT_Code,
[Diagnosis Code 1] as Diagnosis,
[Amount] as Charge,
[Adjustment Amount] as Adjustment,
[Insurance 1 Amount Paid] + [Insurance 2 Amount Paid] + [Insurance 3 Amount Paid] + [Guarantor Amount Paid] as Receipts,
([Adjustment Amount] + [Insurance 1 Amount Paid] + [Insurance 2 Amount Paid] + [Insurance 3 Amount Paid] + [Guarantor Amount Paid]) + [Amount] as Balance FROM
MWTRN TRN, MWCAS CAS, MWINS INS, MWPAT PAT
WHERE
(UCASE(INS.[Name]) like '%MEDICARE%' )
AND
(TRN.[CASE NUMBER] = CAS.[CASE NUMBER]) AND (CAS.[Insurance Carrier #1] = INS.[CODE] AND CAS.[CHART NUMBER] = PAT.[CHART NUMBER])
AND
(TRN.[Procedure Code] = '78816')
AND
(TRN.[TRANSACTION TYPE] <= 'H')
ORDER BY TRN.[CHART NUMBER] ASCExport results to C:\PROC3.ADT
' Get patients final list
SELECT TRN1.* FROM "C:\PROC1.ADT" TRN1, "C:\PROC3.ADT" TRN2 WHERE (TRN1.[CHART NUMBER] = TRN1.[CHART NUMBER]) AND (TRN1.[DOS] = TRN2.[DOS])
**************************************************************************************************************************************************************
Objective: User wants to make patients inactive for any patient that has not have any activity since xxxx date (Past 1 year in this example 12/21/2009)
Step 1
Select max([Date From]) as MaxDate, [CHART NUMBER] from mwtrn where [transaction type] <= 'H' GROUP BY [CHART NUMBER] ORDER BY MaxDate
Step 2 - Check Data
SELECT CONVERT(([Adjustment Amount] + [Insurance 1 Amount Paid] + [Insurance 2 Amount Paid] + [Insurance 3 Amount Paid] + [Guarantor Amount Paid] + [Amount]), "SQL_MONEY") as Balance, mwtrn.*FROM MWTRN WHERE [CHART NUMBER] = 'HAYMA000' AND [TRANSACTION TYPE] <= 'H' ORDER BY [DATE FROM] DESC
Step 3
Export the data into a table called mwpatTMP1
Step 4
To see the data that will be updated to Inactive run the following SQL
SELECT [Chart Number] from mwpat where [Chart Number] in (Select [Chart Number] as ChartToBeInactive from mwpatTMP1 WHERE CONVERT([MaxDate], "SQL_DATE") <= CONVERT('12/31/2009', "SQL_DATE"))
Run the following SQL to make patients inactive for those that did not have any activity since '12/31/2009'
UPDATE MWPAT set [Inactive] = True WHERE [Chart Number] in (Select [Chart Number] as ChartToBeInactive from mwpatTMP1 WHERE CONVERT([MaxDate], "SQL_DATE") <= CONVERT('12/31/2009', "SQL_DATE"))
**************************************************************************************************************************************************************
Unapplied amount error comes up: "You must apply the unapplied amount..." - user cannot get out after clicking on Update All
To fix the problem, run the following SQL in ARC32. This is caused because some amount are showing as 0.0001, making the system think that an amount must applied.
Update MWtrn set [unapplied amount] = 0 where [unapplied amount] > 0 and [unapplied amount] < 1
**************************************************************************************************************************************************************
SQL Script to update EMC Receiver for MWINS.
To check how many records are with the invalid Receiver:
select * from MWINS where [EMC RECEIVER]= 'PROXY'
To Update all those invalid records with a valid receiver:
UPDATE MWINS SET [EMC RECEIVER] = 'REL00' WHERE [EMC RECEIVER] IS NULL or [EMC RECEIVER]= 'PROXY'
SQL Script for EMC Receiver for MWCLA.
To check for invalid EMC Receivers:
SELECT [EMC RECEIVER 3] FROM MWCLA WHERE NOT [EMC RECEIVER 1] IN(SELECT [CODE] FROM MWECR)
To Update the EMC Receivers with the correct code for the claims:
UPDATE MWCLA SET [EMC Receiver 1] = 'REL00' WHERE [EMC Receiver 1] IS NULL or [EMC Receiver 1]= 'PROXY'
To update claims with an invalid EMC receiver that does not exist. Set receiver to null:
UPDATE MWCLA SET [EMC RECEIVER 1] = NULL WHERE NOT [EMC RECEIVER 1] IN(SELECT [CODE] FROM MWECR)
**************************************************************************************************************************************************************