|
dowens
Yak Posting Veteran
82 Posts |
Posted - 2005-07-14 : 09:45:53
|
| SELECT Left(CL.[Member],9)+ '-' + Right(CL.[Member],2)AS MbrId, CL.LOB AS LOB, CL.Region AS Region, CL.GroupID AS GrpId, CL.Document AS ClmId, PR.SeqNo AS SeqNo, RevExists, CASE WHEN CL.ClStatus = 'P' THEN 'PAID' WHEN CL.ClStatus in ('S','',null) THEN 'OPEN' WHEN CL.ClStatus in ('E','A') THEN 'APPROVED' END AS Status, CL.LastSuspDt AS LastSuspDt, Sus.SusCode AS SuspCode, Sus.LongDesc AS SuspDesc, CL.AuthNo AS AuthId, CL.Vendor AS VendId, VN.Address1 AS VendAddr1, VN.Address2 AS VendAddr2, VN.Address3 AS VendAddr3, '' AS VendCity, '' AS VendSt, '' AS VendZip, CN.SpecCode AS VendType, CL.Provider AS ProvId, PR.Specialty AS ProvSpec, CL.PatientId AS PatientId, CL.ClaimPcp AS PCPId , PHSpec.ProvSpec AS PCPSpec, CL.OrderProv AS OrderPhys, PR3.ParNonPar AS ParFlag, CL.Diag1 AS ICD9_1, MD2.DCode AS ICD9_2, MD3.DCode AS ICD9_3, PR.PlCode AS SvcLoc, PR2.CapLine AS CapCd, CL.CapacityCd AS Capacity, CL.Drg AS DRG, CL.ClmType AS ClmType, PR.ServType AS TOS, CL.ClmType AS AdmitType, CASE WHEN (AU.ReferBy IS NULL) THEN 'N' WHEN (DATALENGTH(AU.ReferBy) > 0) THEN 'Y' ELSE 'N' END AS ReferFlag, CL.Dos1 AS DOS, PR.ThruDt AS ThruDt, CL.DischDt AS DischDt, PR.Line_Code AS CPTCd, COALESCE(SUBSTRING(PR.Line_Code, 6, 2),'') AS CPTMod, PR2.Qty AS Qty, PR2.Adj AS AdjCd, CL.Invoice AS InvNo, PY.PayDt AS PayDt, CLChecks.CheckNo AS CheckNo, CL.PaySub AS PayToVF, PR.GlNo AS GLNo, PR2.ReqAmt AS 'Billed', PR2.MaxAwp AS 'Max', PR.CoPayAmt AS 'CoPay', PR2.DdAmt AS 'Ded', PR2.CoinsAmt AS 'Coins', PR.Line_Disc AS 'Disc', PR2.WthdAmt AS 'With', PR2.CobAmt AS 'COB', CASE WHEN (CL.PaySub = 'F') THEN PR2.PayAmt ELSE PR3.CoveredAmt END AS 'Fam', PR2.PayAmt AS 'Paid', CL.LastUpd AS LastUpdDt FROM dbo.dw_MasterClaim CL LEFT JOIN dbo.dw_MasterClaim_ProcLine PR ON CL.Document = PR.Document LEFT JOIN dbo.dw_MasterClaim_ProcLine2 PR2 ON CL.Document = PR2.Document and PR2.seqno = PR.seqno LEFT JOIN dbo.dw_MasterClaim_ProcLine3 PR3 ON CL.Document = PR3.Document and PR3.seqno = PR.seqno LEFT JOIN dbo.dw_MasterClaim_Pay PY ON CL.Document = PY.Document LEFT JOIN dbo.dw_MasterClaim_Diagnosis MD2 ON CL.Document = MD2.Document AND MD2.SeqNo = 2 LEFT JOIN dbo.dw_MasterClaim_Diagnosis MD3 ON CL.Document = MD3.Document AND MD3.SeqNo = 3 LEFT JOIN dbo.dw_Authorization AU ON CL.AuthNo = AU.AuthID LEFT JOIN dbo.dw_Consultant CN ON CL.Vendor = CN.ConID LEFT JOIN dbo.dw_Vendor VN ON CL.Vendor = VN.VendorNumber LEFT JOIN (SELECT Document, CheckNo FROM dbo.dw_MasterClaim_Checks WHERE SeqNo = 1) CLChecks ON CL.Document = CLChecks.Document LEFT JOIN (SELECT ClmNumber, SusCode, LongDesc FROM dbo.dw_SusClaims, dbo.dw_ClAdj WHERE AdjID = SusCode AND SusCode = 'S') Sus ON CL.Document = Sus.ClmNumber LEFT JOIN ( SELECT PhID, ProvSpec FROM dbo.dw_Physician_ProvSpec WHERE SeqNo = 1) PHSpec ON CL.PcpVendor = PHSpec.PhIDWHERE CL.LOB = 'T500' |
 |
|
View problem