HOME

PreAdmission Testing

SELECT DISTINCT CnlyClaimNum, AllFlags, BillProvName, SvcProvNum, LineBeginDOS, CnlySubscriberId, MedicaidInd, POS, SubST, PaidDT, RevCD, ClaimBEGDOS, NoTouchFlag,

ClientLOB, CLMPaidAMT, DxCd1, SvcProvSpecCd, LinePaidAmt, BillStExp, ServiceReason

FROM scrClaimsDetail

WHERE POS in ('22','23','24') AND CLMPaidAMT>100.00 AND PaidDt BETWEEN DATE()-365 and DATE()-20 AND Product=[Line] AND RevCD IN ('0254','0255','0300','0301','0302',

'0303','0304','0305','0306','0307','0308','0309','0310','0311','0312','0313','0314','0315','0316','0317','0318','0319','0320','0321','0322','0323','0324','0325',

'0326','0327','0328','0329','0341','0343','0350','0351','0352','0353','0354','0355','0356','0357','0358','0359','0371','0372','0400','0401','0402','0403','0404',

'0405','0406','0407','0408','0409','0450','0460','0461','0462','0463','0464','0465','0466','0467','0468','0469','0471','0481','0489','0482','0483','0530','0531',

'0532','0533','0534','0535','0536','0537','0538','0539','0610','0611','0612','0613','0614','0615','0616','0617','0618','0619','0620','0621','0622','0623','0624',

'0625','0626','0627','0628','0629','0730','0731','0732','0733','0734','0735','0736','0737','0738','0739','0740','0741','0742','0743','0744','0745','0746','0747',

'0748','0749','0918','0920','0921','0922','0923','0924','0925','0926','0927','0928','0929');

Inpatient stay

SELECT DISTINCT CnlyClaimNum, BillProvName, SvcProvNum, LineBeginDOS, CnlySubscriberId, MedicaidInd, POS, PaidDT, ClaimBEGDOS, TOB, AdmitDX, ClmPaidAmt, ClaimEndDOS,

ProcCd, TaxonomyCd, Product, ServiceReason, AdmitDt, DXCD1

FROM scrClaimsDetail

WHERE TOB LIKE '11%' AND ClmPaidAmt<>0.00 AND ProcCd<>'' AND PaidDt BETWEEN DATE()-365 and DATE()-20 AND Product=[Line];

PreAdmission Testing to Inpatient Stay

SELECT PreTesting1.*, IP.*

FROM PreTesting1 INNER JOIN IP ON (PreTesting1.SvcProvNum=IP.SvcProvNum) AND (PreTesting1.CnlySubscriberId = IP.CnlySubscriberId)

WHERE PreTesting1.ClaimBEGDOS =IP.AdmitDT OR PreTesting1.ClaimBEGDOS +1 =IP.AdmitDT OR PreTesting1.ClaimBEGDOS +2 =IP.AdmitDT OR PreTesting1.ClaimBEGDOS +3 =IP.AdmitDT

and PreTesting1.CnlyClaimNum<>IP.CnlyClaimNum

ORDER BY PreTesting1.CLMPaidAMT DESC;