Cacher is the code snippet organizer for pro developers

This script can be used to query dependents who are currently enrolled and is about to/have already gone overage according to plan rule set up.

This is useful when sometimes "Overage Dependent" alert is being triggered but generates no records, or need to figure out what type of enrollment caused the alert to be triggered, among other possible ways of use.

-------------------------------------
select

         e.enrollID

       , e.userid

       , e.planid

       , pd.plandetailid

       , pn.planname

       , bpt.bptName

       , zrlv.relationshipdescription as [Relationship]

       , ua.birthDate

       , DATEDIFF(hour,ua.birthdate,GETDATE())/8766 as [Age] -- replace the red "getdate()" to any time that you want to check age status

       , DATEDIFF(day,convert(date, cast(year(getdate()) as varchar(4)) + '-' + cast(month(ua.birthdate) as varchar(2)) + '-' + cast(day(ua.birthdate) as varchar(2))),getdate())*(-1)

             as [X days past(-)/away(+) birthday of this year] -- replace the red "getdate()" to any time that you want to check age status

       , ua.fullTimeStudentYN as [Full-time student]

       , pd401k.maxAge

       , pdis.maxAge

       , pHRA.rraChildMaxAge

       , phvd.nonStudentMaxAge

       , phvd.studentMaxAge

       , phvd.maxAge

       , plife.suppDepMaxAge

       , plife.suppDepFTStudentMaxAge

       , plife.employeeMaxAge

       , plife.spouseMaxAge

       , pltc.suppDepMaxAge

       , pltc.suppDepFTStudentMaxAge

 

from enroll e with (nolock)

inner join plandetail pd with (nolock) on e.planid = pd.planid

inner join planname pn with (nolock) on e.planid = pn.planid

inner join useraccount ua with (nolock) on ua.userid = e.userid

left join benefitPlanType bpt with (nolock) on bpt.benefitPlanTypeID = e.benefitPlanTypeID

left join zrelationshiplv zrlv with (nolock) on zrlv.relationshiplv = ua.relationshiplv

left join plandetail401kDeductions pd401k with (nolock) on pd.plandetailid = pd401k.plandetailid

left join plandetaildisability pdis with (nolock) on pd.plandetailid = pdis.plandetailid

left join plandetailHRA pHRA with (nolock) on pd.plandetailid = pHRA.plandetailid

left join plandetailhvd phvd with (nolock) on pd.plandetailid = phvd.plandetailid

left join plandetaillife plife with (nolock) on pd.plandetailid = pLife.plandetailid

left join plandetailltc pltc with (nolock) on pd.plandetailid = pltc.plandetailid

where

       e.userid <> e.primaryUserID

and ua.clientid in (48695) -- replace clientid of your interest

and getdate() between e.effectiveDate and e.terminationDate -- replace with the time you used to calculate Age above

and e.enrollChoice = 1

and

(

             (DATEDIFF(hour,ua.birthdate,GETDATE())/8766) >= (isnull(pd401k.maxAge,999) - 1)

       or

             (DATEDIFF(hour,ua.birthdate,GETDATE())/8766) >= (isnull(pdis.maxAge,999) - 1)

       or

             (DATEDIFF(hour,ua.birthdate,GETDATE())/8766) >= (isnull(pHRA.rraChildMaxAge,999) - 1)

       or

             (DATEDIFF(hour,ua.birthdate,GETDATE())/8766) >= (isnull(phvd.nonStudentMaxAge,999) - 1)

       or

             (DATEDIFF(hour,ua.birthdate,GETDATE())/8766) >= (isnull(phvd.studentMaxAge,999) - 1)

       or

             (DATEDIFF(hour,ua.birthdate,GETDATE())/8766) >= (isnull(phvd.maxAge,999) - 1)

       or

             (DATEDIFF(hour,ua.birthdate,GETDATE())/8766) >= (isnull(plife.suppDepMaxAge,999) - 1)

       or

             (DATEDIFF(hour,ua.birthdate,GETDATE())/8766) >= (isnull(plife.suppDepFTStudentMaxAge,999) - 1)

       or

             (DATEDIFF(hour,ua.birthdate,GETDATE())/8766) >= (isnull(plife.employeeMaxAge,999) - 1)

       or

             (DATEDIFF(hour,ua.birthdate,GETDATE())/8766) >= (isnull(plife.spouseMaxAge,999) - 1)

       or

             (DATEDIFF(hour,ua.birthdate,GETDATE())/8766) >= (isnull(pltc.suppDepMaxAge,999) - 1)

       or

             (DATEDIFF(hour,ua.birthdate,GETDATE())/8766) >= (isnull(pltc.suppDepFTStudentMaxAge,999) - 1)

)

order by [relationship] asc, [Age] asc, userid asc, enrollid desc

 -------------------------------------