July 14, 2015 at 4:56 pm
ok no problem 🙂
can you repost your query with the last modification you made please ?
and try to select all the field from your join table, there must be a table that is doing the double lines
if you find out which one it is then put it on a subquery as suggested by piete
:w00t: !!!GOOGLE IS YOUR BEST FRIEND!!! :w00t:
July 14, 2015 at 5:02 pm
I pretty much duplicated the original query and made them derived tables. One is pulling the specific Insurance and the other is any insurance for that Medication...And then joined them together:
select distinct A.*
from
(select
HR.MRN MRN
,HR.FIRST_NAME FirstName
,HR.LAST_NAME LastName
,CONVERT(VARCHAR(10),HR.DOB, 101) Date_of_Birth
--,[CPRSQL].dbo.CLAIMS.[CFK_INVOICES] "Invoice Number"
--,CONVERT(VARCHAR(10),NCPDP.DateFilled, 101) DateFilled
,CONVERT(VARCHAR(10),lablog.curdate, 101) Date_Filled
--,lablog.curdate
,patins.policy Policy_Number
,NCPDP.drugname Drug
,claims.expected AssistancePay
--,claims.totalpaid
,ncpdp.copay Patient_CoPay
,patins.payor
,INSCOMP.ORG Insurance
,hr.physician Physician
--,patins.rank
,ncpdp.occ
--,labels.delivdate
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 tickci
--on tickci.mrn = hr.mrn
join lablog
on lablog.CPK_LABLOG = ncpdp.LABLOGNO
where --[CPRSQL].dbo.INSCOMP.payor in ('Copay Card')
--inscomp.org like '%Medtrak%'
lablog.curdate between '04/01/2015'and '06/30/2015'
--and TOTALPAID <> 0
and [cprsql].dbo.HR.MRN = 003137
and CFK_INSCOMP <> 99999
and INSCOMP.DELFLAG= 0
and CLAIMS.DELFLAG= 0
and HR.DELFLAG = 0
--and NCPDP.copay > 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
group by HR.MRN
,HR.[FIRST_NAME]
,HR.[LAST_NAME]
,CONVERT(VARCHAR(10),HR.DOB, 101)
--,[CPRSQL].dbo.CLAIMS.[CFK_INVOICES] "Invoice Number"
-- ,CONVERT(VARCHAR(10),NCPDP.DateFilled, 101)
--,CONVERT(VARCHAR(10),tickci.delivdate, 101)
,CONVERT(VARCHAR(10),lablog.curdate, 101)
--,lablog.curdate
,patins.policy
,NCPDP.drugname
,claims.expected
--,claims.totalpaid
,ncpdp.copay
,patins.payor
,INSCOMP.ORG
,hr.physician
,patins.rank
,ncpdp.occ
)A
join
(select
HR.MRN MRN
,HR.FIRST_NAME FirstName
,HR.LAST_NAME LastName
,CONVERT(VARCHAR(10),HR.DOB, 101) Date_of_Birth
--,[CPRSQL].dbo.CLAIMS.[CFK_INVOICES] "Invoice Number"
--,CONVERT(VARCHAR(10),NCPDP.DateFilled, 101) DateFilled
,CONVERT(VARCHAR(10),lablog.curdate, 101) Date_Filled
--,lablog.curdate
,patins.policy Policy_Number
,NCPDP.drugname Drug
,claims.totalpaid
,ncpdp.copay
,patins.payor
,INSCOMP.ORG Insurance
,hr.physician Physician
,patins.rank
,ncpdp.occ
--,labels.delivdate
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 tickci
--on tickci.mrn = hr.mrn
join lablog
on lablog.CPK_LABLOG = ncpdp.LABLOGNO
where --[CPRSQL].dbo.INSCOMP.payor in ('Grant','Copay Card','Assistance')
inscomp.org like '%Medtrak%'
and lablog.curdate between '04/01/2015'and '06/30/2015'
--and TOTALPAID <> 0
and [cprsql].dbo.HR.MRN = 003137
and CFK_INSCOMP <> 99999
and INSCOMP.DELFLAG= 0
and CLAIMS.DELFLAG= 0
and HR.DELFLAG = 0
--and NCPDP.copay > 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
group by HR.MRN
,HR.[FIRST_NAME]
,HR.[LAST_NAME]
,CONVERT(VARCHAR(10),HR.DOB, 101)
--,[CPRSQL].dbo.CLAIMS.[CFK_INVOICES] "Invoice Number"
-- ,CONVERT(VARCHAR(10),NCPDP.DateFilled, 101)
--,CONVERT(VARCHAR(10),tickci.delivdate, 101)
,CONVERT(VARCHAR(10),lablog.curdate, 101)
--,lablog.curdate
,patins.policy
,NCPDP.drugname
,claims.totalpaid
,ncpdp.copay
,patins.payor
,INSCOMP.ORG
,hr.physician
,patins.rank
,ncpdp.occ
)B
on A.MRN = B.MRN
July 14, 2015 at 5:31 pm
can you try this ?
i changed the join to an exists subquery
SELECT 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
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
:w00t: !!!GOOGLE IS YOUR BEST FRIEND!!! :w00t:
July 14, 2015 at 5:38 pm
Mohamed, that is alot cleaner! Thanks. It's been a while since I wrote SQL. I'm still getting the $5 copay on all 4 records...do I try the first_value to this to try to get $5 to display once?
July 14, 2015 at 5:48 pm
ok good
i don't know if the logic is still correct, i put a max() on the copay and remove it from the group
here is the code:
SELECT 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 ,
MAX(ncpdp.copay) Patient_CoPay ,
patins.payor,
INSCOMP.ORG Insurance,
hr.physician Physician ,
ncpdp.occ
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 ,
patins.payor,
INSCOMP.ORG,
hr.physician,
patins.rank,
ncpdp.occ
:w00t: !!!GOOGLE IS YOUR BEST FRIEND!!! :w00t:
July 14, 2015 at 6:11 pm
I think the issue is the requestor wants the insurance and fill date listed...so, you end up with 4 records. For example..
Name CoPay Assistance Insurance Fill Date
Jeff Smith $5 $10,000 MyInsurance 5/21/2015
Jeff Smith $5 $5,000 Great Insurance 5/21/2015
Jeff Smith $5 $10,000 MyInsurance 6/22/2015
Jeff Smith $5 $5,000 Great Insurance 6/22/2015
July 15, 2015 at 9:04 am
Got it! I added two more Derived tables to flatten the data out. Went from 4 records to 2
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
July 15, 2015 at 9:43 am
Super^^
I am happy you finally solved it:satisfied:
:w00t: !!!GOOGLE IS YOUR BEST FRIEND!!! :w00t:
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply