Hello,
I was wondering if someone could point me in the right direction to a report....I need a list of all subscribers that have Metlife dental insurance. I need an easy way to get their email and address so I can send them a letter.
Thanks
Help finding report
-
- Posts: 353
- Joined: Mon Feb 25, 2008 3:09 am
Re: Help finding report
Try this.
drtmz
/*449 List of active patients (not subscribers) with primary insurance plan listed,
Carrier Names and group names are cutoff for space consideration
Check RAW button to show both PatNum and Patient Name*/
SELECT IFNULL(Left(c.CarrierName, 15), 'None') AS CarrierName, Left(GroupName, 15) AS GroupName,
PlanType, p.PatNum, CONCAT(p.LName,', ',p.FName,' ',p.MiddleI) AS PatName, ib.SubscriberId,p.email
FROM patient p
LEFT JOIN patplan pp ON pp.PatNum=p.PatNum
LEFT JOIN inssub ib ON ib.InsSubNum=pp.InsSubNum
LEFT JOIN insplan ip ON ip.PlanNum=ib.PlanNum
LEFT JOIN carrier c ON c.CarrierNum=ip.CarrierNum
WHERE p.PatStatus=0 AND (pp.Ordinal=1 OR ISNULL(c.CarrierName))
AND CarrierName like '%MetLife%'
ORDER BY c.CarrierName, GroupName, p.LName;
drtmz
/*449 List of active patients (not subscribers) with primary insurance plan listed,
Carrier Names and group names are cutoff for space consideration
Check RAW button to show both PatNum and Patient Name*/
SELECT IFNULL(Left(c.CarrierName, 15), 'None') AS CarrierName, Left(GroupName, 15) AS GroupName,
PlanType, p.PatNum, CONCAT(p.LName,', ',p.FName,' ',p.MiddleI) AS PatName, ib.SubscriberId,p.email
FROM patient p
LEFT JOIN patplan pp ON pp.PatNum=p.PatNum
LEFT JOIN inssub ib ON ib.InsSubNum=pp.InsSubNum
LEFT JOIN insplan ip ON ip.PlanNum=ib.PlanNum
LEFT JOIN carrier c ON c.CarrierNum=ip.CarrierNum
WHERE p.PatStatus=0 AND (pp.Ordinal=1 OR ISNULL(c.CarrierName))
AND CarrierName like '%MetLife%'
ORDER BY c.CarrierName, GroupName, p.LName;