Notifications
Clear all
Topic starter
I usually work in CEMR but currently I am working on a project in CPS and trying to create a SQL query to pull insurance information. I have found a couple of views that pull some insurance information but I need to be able to pull the insured party name to pull into an INS1-4 HL7 segment and the database layout is completely different from CEMR.
Below is what I have so far. Do I need to setup a case statement that would say if the insured party is self to return first/last from PatientProfile, if it was Guarantor return the same from that table otherwise return the values from vPatientInsurance? That seems overly complicated for what I am asking but I suppose I can do it. Is there another view I should be looking at that would get the insured party's name and relationship?
I think I am on the right track I would just like someone with CPS experience to tell me if my logic is correct as well as if there is a better way to do this.
select G.*, priins.*, secins.* from PatientProfile P
left join vPatientInsurance priins on priins.PId = p.PID and priins.OrderForClaims = 1 and priins.Inactive = 0
left join vPatientInsurance secins on secins.PId = p.PID and secins.OrderForClaims = 2 and secins.Inactive = 0
LEFT JOIN Guarantor G ON G.GuarantorId = P.GuarantorId
Posted : April 11, 2017 2:55 pm
