July 15, 2015 at 9:03 am
Got it! I added two derived tables to the query to flatten it out. Seemed to do the trick:
select C.MRN,
C.FirstName,
C.LastName,
C.Date_of_Birth,
t.PatientCoPay,
t.AssistancePay as "CoPay Card Assistance",
t.CoPayCardInsurance,
t.CoPay_Policy_Number,
u.AssistancePay as "Commercial Assistance",
u.CommercialInsurance,
u.Commercial_Policy_Number,
C.Date_Filled,
C.Physician
from(
SELECT distinct HR.MRN MRN,
HR.FIRST_NAME FirstName,
HR.LAST_NAME LastName,
CONVERT(VARCHAR(10),HR.DOB, 101) Date_of_Birth ,
CONVERT(VARCHAR(10),lablog.curdate, 101) Date_Filled ,
--patins.policy Policy_Number,
NCPDP.drugname Drug,
--claims.expected AssistancePay ,
--ncpdp.copay Patient_CoPay ,
--patins.payor,
--INSCOMP.ORG Insurance,
hr.physician Physician ,
ncpdp.occ
,CLAIMS.CFK_INVOICES InvoiceNum
FROM INSCOMP
JOIN CLAIMS ON INSCOMP.NO = CLAIMS.CFK_INSCOMP
JOIN HR ON CLAIMS.CFK_HR = HR.[MRN]
JOIN NCPDP ON NCPDP.mrn = hr.mrn
AND NCPDP.BILLNO = claims.CFK_INVOICES
JOIN parts ON parts.name_ = NCPDP.drugname
JOIN patins ON PATINS.CPK_PATINS = claims.CFK_PATINS
JOIN labels ON labels.no = parts.no
JOIN lablog ON lablog.CPK_LABLOG = ncpdp.LABLOGNO
WHERE lablog.curdate BETWEEN '04/01/2015'AND '06/30/2015'
AND [cprsql].dbo.HR.MRN = 003137
AND CFK_INSCOMP <> 99999
AND INSCOMP.DELFLAG= 0
AND CLAIMS.DELFLAG= 0
AND HR.DELFLAG = 0
AND lablog.ndc IN ('61958180101',
'00074309328',
'00004035730',
'00004035239',
'00004036030',
'00004036530',
'00004035009',
'00085435301',
'00085132301',
'00085131601',
'00085127901',
'00085129701',
'00085129702',
'00085137001',
'00085137002',
'00085131602',
'68084017965',
'65862029018',
'65862020768',
'66435010899',
'66435010599',
'66435010699',
'66435010799',
'66435010118',
'66435010216',
'00781204304',
'00781204304',
'00093722758',
'00093722763',
'00093722772',
'00093722777',
'68382026012',
'68382004603',
'00074327156',
'00074328256',
'00074322456')
AND ncpdp.occ = 08
AND exists
(SELECT 1
FROM INSCOMP AS INSCOMP_B
JOIN CLAIMS AS CLAIMS_B ON INSCOMP_B.NO = CLAIMS_B.CFK_INSCOMP
JOIN HR AS HR_B ON CLAIMS_B.CFK_HR = HR_B.[MRN]
JOIN NCPDP AS NCPDP_B ON NCPDP_B.mrn = HR_B.mrn
AND NCPDP_B.BILLNO = CLAIMS_B.CFK_INVOICES
JOIN parts AS parts_B ON parts_B.name_ = NCPDP_B.drugname
JOIN patins AS patins_B ON patins_B.CPK_PATINS = CLAIMS_B.CFK_PATINS
JOIN labels AS labels_B ON labels_B.no = parts_B.no
JOIN lablog AS lablog_B ON lablog_B.CPK_LABLOG = NCPDP_B.LABLOGNO
WHERE INSCOMP_B.org LIKE '%Medtrak%'
AND lablog_B.curdate BETWEEN '04/01/2015'AND '06/30/2015'
AND HR_B.MRN = 003137
AND CLAIMS_B.CFK_INSCOMP <> 99999
AND INSCOMP_B.DELFLAG= 0
AND CLAIMS_B.DELFLAG= 0
AND HR_B.DELFLAG = 0
AND lablog_B.ndc IN ('61958180101', '00074309328', '00004035730', '00004035239', '00004036030', '00004036530', '00004035009', '00085435301', '00085132301', '00085131601', '00085127901', '00085129701', '00085129702', '00085137001', '00085137002', '00085131602', '68084017965', '65862029018', '65862020768', '66435010899', '66435010599', '66435010699', '66435010799', '66435010118', '66435010216', '00781204304', '00781204304', '00093722758', '00093722763', '00093722772', '00093722777', '68382026012', '68382004603', '00074327156', '00074328256', '00074322456')
AND NCPDP_B.occ = 08
AND HR.MRN = HR_B.MRN)
GROUP BY HR.MRN,
HR.[FIRST_NAME],
HR.[LAST_NAME],
CONVERT(VARCHAR(10),HR.DOB, 101) ,
CONVERT(VARCHAR(10),lablog.curdate, 101) ,
patins.policy,
NCPDP.drugname,
claims.expected ,
ncpdp.copay ,
patins.payor,
INSCOMP.ORG,
hr.physician,
patins.rank,
ncpdp.occ
,CLAIMS.CFK_INVOICES
)C
join (
select distinct ncpdp.MRN,ncpdp.BILLNO as BillNum,ncpdp.copay as PatientCoPay, CLAIMS.EXPECTED as AssistancePay, (ncpdp.copay + CLAIMS.EXPECTED) as OriginalCopay,patins.insurance as CoPayCardInsurance,patins.policy as CoPay_Policy_Number
--,*
from NCPDP
join CLAIMS on CFK_INVOICES = ncpdp.BILLNO
join PATINS on PATINS.CPK_PATINS = claims.CFK_PATINS
where patins.PAYOR = 'COPAY CARD'
and OCC = 08
and NCPDP.DELFLAG <> 1
and claims.DELFLAG <> 1
and patins.DELFLAG <> 1
and ncpdp.MRN = 3137
--and ncpdp.BILLNO = 40451
) t on t.mrn = C.MRN and C.InvoiceNum = t.BillNum
join (
select distinct ncpdp.MRN,ncpdp.BILLNO as BillNum,ncpdp.copay as PatientCoPay, CLAIMS.EXPECTED as AssistancePay, (ncpdp.copay + CLAIMS.EXPECTED) as OriginalCopay,patins.insurance as CommercialInsurance,patins.policy as Commercial_Policy_Number
--,*
from NCPDP
join CLAIMS on CFK_INVOICES = ncpdp.BILLNO
join PATINS on PATINS.CPK_PATINS = claims.CFK_PATINS
where patins.PAYOR = 'COMMERCIAL'
and OCC = 08
and NCPDP.DELFLAG <> 1
and claims.DELFLAG <> 1
and patins.DELFLAG <> 1
and ncpdp.MRN = 3137
--and ncpdp.BILLNO = 40451
) u on u.mrn = C.MRN and C.InvoiceNum = u.BillNum
Viewing post 16 (of 15 total)
You must be logged in to reply to this topic. Login to reply