July 14, 2015 at 2:58 pm
Here's an issue I'm running into...I have what each Insurance is paying...but the patient co pay is displaying for each record. For example:
MRNFirstNameLastNameDate_Filled Drug AssistancePayPatient_CoPaypayor Insurance
1111hdhdhdhdhdhd5/21/2015Tylenol $28,773.00 $5.00 COMMERCIAL A
1111hdhdhdhdhdhd5/21/2015Tylenol $1,840.00 $5.00 COPAY CARD B
1111hdhdhdhdhdhd6/18/2015Tylenol $28,773.00 $5.00 COMMERCIAL A
1111hdhdhdhdhdhd6/18/2015Tylenol $1,840.00 $5.00 COPAY CARD B
The Patient really only paid a $5 copay...but it's showing for each record. I don't want people to get confused and think the patient paid $20 total. Is there a way to get that to somehow display once?
July 14, 2015 at 3:43 pm
What kind of grouping do you have in your report? Could you post some sample data (not real please - we don't want to see anything we are not supposed to!)
Sounds like you want the co-pay to be in the same section as the "claim number" or whatever, not in with the details. If you had to use an aggregate, you could use FIRST() and it should work fine.
July 14, 2015 at 4:21 pm
That data in the post is actually sample data. I removed name, etc.
July 14, 2015 at 4:35 pm
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 '%Med%'
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 '%Med%'
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 4:36 pm
Is there a way to attach an excel file? My results example would probably display cleaner
July 14, 2015 at 4:44 pm
Put the Copay column into the same group as MRN and then use FIRST(Fields!Copay.Value)
July 14, 2015 at 4:55 pm
Pietlinden, isn't CoPay already in the Group By of Table A? Sorry, I'm a little confused
July 14, 2015 at 5:48 pm
Is there a way to attach an excel file? My results example would probably display cleaner
Just click the Edit Attachments below the message, then you can upload the file. If you can, please include a sample of what you want your data to look like. Here's my best guess. I based the report on a union query (dummy data that you provided). Hopefully this is close.
July 14, 2015 at 6:02 pm
Pietlinden, for some reason I can't open the attachment
July 14, 2015 at 8:08 pm
you can't open it directly. You have to import it into an SSRS report project first.
(If you're using Excel 2013, you can do all this in Excel. Just create a connection to your database, specify the view that does all the T-SQL specific functions, and then build your report based on that.)
I guess I'll have to try another way.
I have a dataset in SSRS with these columns:
MRN, Copay, Date_Filled, Drug, AssistancePay
I added a tablix to my report surface, and then added
Date_Filled, Drug, and AssistancePay to the tablix (so I should have 3 columns).
Right-click on gray selector to the left of the first column (where Date_Filled is). Select "Add Group (Parent Group)" from the context-menu.
Group by: MRN
Add Group Header
Then right-click the cell to the right of "[MRN]". If you click the blue "fields" button (or whatever it's called!), select the Copay field. SSRS may put Sum([Copay]), but change it to First([Copay]) (just type over the SUM nonsense).
Then all you should need to do is format a bunch of fields, but that's easy.
July 14, 2015 at 8:53 pm
Pietlinden...I couldn't get the file to open in SQL Server. It's a "me" problem. Can you give a snapshot of the results?
July 14, 2015 at 10:15 pm
Here's a screenshot of what I did. Hopefully it's clearer now.
July 14, 2015 at 10:23 pm
That looks great. How did you do that? Can you paste the query here?
July 15, 2015 at 7:30 am
My data is fake, because I didnt have yours.
SELECT 1111 AS MRN, 'hdhdhd' AS FirstName, 'hdhdhd' AS LastName, '5/21/2015' AS Date_Filled, 'Tylenol' AS Drug, 1840.00 AS AssistancePay, 5.00 AS Copay,
'COPAY CARD' AS Payor, 'B' AS Insurance
UNION ALL
SELECT 1111 AS MRN, 'hdhdhd' AS FirstName, 'hdhdhd' AS LastName, '6/18/2015' AS Date_Filled, 'Tylenol' AS Drug, 28773.00 AS AssistancePay, 5.00 AS Copay,
'COMMERCIAL' AS Payor, 'A' AS Insurance
UNION ALL
SELECT 1111 AS MRN, 'hdhdhd' AS FirstName, 'hdhdhd' AS LastName, '6/18/2015' AS Date_Filled, 'Tylenol' AS Drug, 1840.00 AS AssistancePay, 5.00 AS Copay,
'COPAY CARD' AS Payor, 'B' AS Insurance
July 15, 2015 at 7:36 am
Unfortunately it's patient data, so I can't give you real data. Would the actual query help?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply