I wasn't sure what could be the title for my question so sorry about that.
I'm trying to write a SQL query to achieve the no. of members who should get reimbursed from a pharmacy.
For example : I went to pharmacy, I took a vaccine but by mistake I paid from my pocket. so now Pharmacy needs to reimburse me that amount. Lets say I have the data like:
MemberId Name ServiceDate PresNumber PersonId ClaimId AdminFee(in $) 1 John 1/1/2011 123 345 456 0 1 John 1/21/2011 123 345 987 20 2 Mike 2/3/2011 234 567 342 0 2 Mike 2/25/2011 234 567 564 30 5 Linda 1/4/2011 432 543 575 0 5 Linda 4/6/2011 987 543 890 0 6 Sonia 2/6/2011 656 095 439 0
This data shows all members from that pharmacy who got reimbursed and who haven't.
I need to find out the member having AdminFee 0 but i also need to check another record for the same member having same PresNumber, same PersonId where the ServiceDate falls within 30 Days of the Original Record.
If another record meets this criteria and the AdminFee field contains a value (is NOT 0) then it means that person has already been reimbursed. So from the data you can see John and Mike have already been reimbursed and Linda and Sonia need to be reimbursed.
Can anybody help me how to write an SQL query on this?
You don't mention what SQL engine you're using, so here is some generic SQL. You'll need to adapt the date math and the return of True/False ( in the second option) to whatever engine you're using:
-- Already reimbursed SELECT * FROM YourTable YT1 WHERE AdminFee = 0 AND EXISTS (SELECT * FROM YourTable YT2 WHERE YT2.MemberID = YT1.MemberID AND YT2.PresNumber = YT1.PresNumber AND YT2.ServiceDate >= YT1.ServiceDate - 30 AND AdminFee > 0) -- Need reimbursement SELECT * FROM YourTable YT1 WHERE AdminFee = 0 AND NOT EXISTS (SELECT * FROM YourTable YT2 WHERE YT2.MemberID = YT1.MemberID AND YT2.PresNumber = YT1.PresNumber AND YT2.ServiceDate >= YT1.ServiceDate - 30 AND AdminFee > 0)
-- Both in one. SELECT YT1.*, CASE WHEN YT2.MemberID IS NULL THEN False ELSE True END AS AlreadyReimbursed FROM YourTable YT1 JOIN YourTable YT2 ON YT1.MemberID = YT2.MemberID AND YT1.PresNumber = YT2.PresNumber AND YT1.ServiceDate <= YT2.ServiceDate + 30 WHERE YT1.AdminFee = 0 AND YT2.AdminFee > 0)