July 14, 2015 at 9:04 am
I'm writing a query and in this query I want it to pull patients that must have one specific insurance, but list any insurance they have....not just the one they must have.
For example:
Patient A must have Insurance B
But, I want to list what Insurance B, C, and D paid out....make sense?
Right now, in the where if i said Insurance = Insurance B I only get records for patients and Insurance B
July 14, 2015 at 9:06 am
Hi
Can you post the complete query please?
Maybe you need to do a LEFT JOIN.
:w00t: !!!GOOGLE IS YOUR BEST FRIEND!!! :w00t:
July 14, 2015 at 9:08 am
cory.bullard76 (7/14/2015)
I'm writing a query and in this query I want it to pull patients that must have one specific insurance, but list any insurance they have....not just the one they must have.For example:
Patient A must have Insurance B
But, I want to list what Insurance B, C, and D paid out....make sense?
Right now, in the where if i said Insurance = Insurance B I only get records for patients and Insurance B
Something like:
SELECT <column list from the B alias>
FROM tableA A
JOIN tableA B
ON A.patientid = B.patientid
WHERE A.Insurance = 'B'
Basically, you need to hit the table twice. Once to get the patients that qualify, then again to get all of the other information.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 14, 2015 at 9:13 am
Here is the Query
select
[cprsql].dbo.HR.MRN MRN
,[cprsql].dbo.HR.[FIRST_NAME] FirstName
,[cprsql].dbo.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
,[CPRSQL].dbo.INSCOMP.ORG Insurance
,hr.physician Physician
,patins.rank
,ncpdp.occ
--,labels.delivdate
from [CPRSQL].dbo.INSCOMP
join [CPRSQL].dbo.CLAIMS
on [CPRSQL].dbo.INSCOMP.[NO] = [CPRSQL].dbo.CLAIMS.[CFK_INSCOMP]
join [cprsql].dbo.HR
on [CPRSQL].dbo.CLAIMS.CFK_HR = [CPRSQL].dbo.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 '%Insurance A%'
and lablog.curdate between '04/01/2015'and '06/30/2015'
--and TOTALPAID <> 0
and CFK_INSCOMP <> 99999
and [CPRSQL].dbo.INSCOMP.DELFLAG= 0
and [CPRSQL].dbo.CLAIMS.DELFLAG= 0
and [cprsql].dbo.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 [cprsql].dbo.HR.MRN
,[cprsql].dbo.HR.[FIRST_NAME]
,[cprsql].dbo.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
,[CPRSQL].dbo.INSCOMP.ORG
,hr.physician
,patins.rank
,ncpdp.occ
order by 1,5,13
July 14, 2015 at 9:18 am
On which table is located Patient information and Insurance informtation ?
:w00t: !!!GOOGLE IS YOUR BEST FRIEND!!! :w00t:
July 14, 2015 at 9:24 am
The Patient Information is in the HR Table and the Insurance is coming from INSCOMP
July 14, 2015 at 9:30 am
why do you need a where clause Insurance = Insurance X ?
If you do that of course you will get only lines with Insurance X
I don't get the logic you want to do
Can you please explain me exactly what you want to get ?
:w00t: !!!GOOGLE IS YOUR BEST FRIEND!!! :w00t:
July 14, 2015 at 9:36 am
I'm not the best at explaining...so here goes nothing...
Basically I'm pulling data on Patients that has Insurance X. But, patients that have that insurance will have other insurances as well. I want those insurances listed as well.
July 14, 2015 at 9:47 am
OK but if you want to list all insurance you don't need any filter on the where clause for Insurance table
But if you need specific insurance to be listed then you have to do a OR between each insurance you want to list
WHERE (Insurance = InsuranceA OR Insurance = InsuranceB ... ) AND ...
:w00t: !!!GOOGLE IS YOUR BEST FRIEND!!! :w00t:
July 14, 2015 at 9:48 am
Wayne...I tried the Alias table angle...still couldn't get it to work
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
,B.ORG Insurance
,hr.physician Physician
,patins.rank
,ncpdp.occ
--,labels.delivdate
from INSCOMP A
join CLAIMS
on A.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
left outer join INSCOMP B
on B.no = A.no
where --[CPRSQL].dbo.INSCOMP.payor in ('Grant','Copay Card','Assistance')
A.org like '%Medtrak%'
and lablog.curdate between '04/01/2015'and '06/30/2015'
--and TOTALPAID <> 0
and [cprsql].dbo.HR.MRN = 002085
and CFK_INSCOMP <> 99999
and A.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
,B.ORG
,hr.physician
,patins.rank
,ncpdp.occ
order by 1,5,13
July 14, 2015 at 9:54 am
Mohamed, that's the thing. I need them to have Insurance X. An 'Or' would return patients that have Insurance Z only, for example, wouldn't it?
For example.
Bill has Insurance X which pays $20,000
But he has Insurance Z which is is secondary and that pays $1,000
He'd make the report.
Sara has Insurance Z which pays $40,000
She wouldn't make the report because she lacks Insurance X
July 14, 2015 at 2:45 pm
Ok, I got it figured out. But, here's what I'm running into...I have what each Insurance is paying...but the patient co pay is displaying for each record. For example:
MRNFirstNameLastNameDate_of_BirthDate_Filled Policy_NumberDrug AssistancePayPatient_CoPay payor Insurance
1111hdhdhdhdhdhd11/2/1901 5/21/2015 hdhdhhd65Tylenol $28,773.00 $5.00 COMMERCIAL A
1111hdhdhdhdhdhd11/3/1901 5/21/2015 hdhdhhd66Tylenol $1,840.00 $5.00 COPAY CARD B
1111hdhdhdhdhdhd11/4/1901 6/18/2015 hdhdhhd65Tylenol $28,773.00 $5.00 COMMERCIAL A
1111hdhdhdhdhdhd11/5/1901 6/18/2015 hdhdhhd66Tylenol $1,840.00 $5.00 COPAY CARD B
The Patient really only paid a $5 copay...but it's showing for each record. Is there a way to get that to somehow display once?
July 14, 2015 at 3:12 pm
OK good you made some improvements on your suery
I looked at the result you got
I wonder why the birthday is different for the same line?
Maybe you have a field on your group by clause that is differentiate line you wish to be merge
:w00t: !!!GOOGLE IS YOUR BEST FRIEND!!! :w00t:
July 14, 2015 at 4:08 pm
Sounds like you need to use EXISTS and a subquery instead of INNER JOIN.
Your subquery would return all CustomerIDs who had 'Policy X', and then you'd use exists...
SELECT c.CustomerID, c.PolicyNumber
FROM Customer c
WHERE EXISTS (SELECT 1 FROM Policy p WHERE PolicyType = 'Policy X' AND PolicyHolderID = c.CustomerID)
I'm sure my query is off, but the idea is that what is returned in the subquery filters what's in the outer query. Use EXISTS because you only want distinct values returned in the outer query. If you use INNER JOIN instead, you'll get duplicates.
July 14, 2015 at 4:39 pm
Sorry, Mohammed....I removed the person's birthday and put in a fake birthday...when I drug it down it changed for each line.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply