Cacher is the code snippet organizer for pro developers

This script can be used to find the roles of the EEs who approved their own pending enrollment records, within a certain client, or enroll approval time range, or plan effective date (easy filter for OE records, or certain plans).



Replace underlined part with the information you would like to query on.



select *

from userRole with (nolock) -- check if the EE has manager role (roleID = 10)

where userid in (

       select distinct

             pe.userid   

       from pendingEnroll pe with (nolock)

       join useraccount ua with (nolock) on pe.userid = ua.userid

       join useraccount ua2 with (nolock) on ua2.userid = pe.savedBy

       where

             ua.clientid = 40718 -- change to the client ID you're looking at

       and ua2.firstname = ua.firstname

       and ua2.lastname = ua.lastname

       and pe.savedDateTime between '2017-01-01 00:00:00.000' and '9999-12-31 23:59:59.997' -- pending enrollment saved/approved date

       and pe.effectiveDate between '2017-01-01 00:00:00.000' and '9999-12-31 23:59:59.997' -- plan effective date

       and pe.removedDate is NULL -- pending enroll are saved by EE if it is removed, so need to exclude those records

       and pe.pendingYN in (0) -- approved pending enroll, more values see table "zpendingEnrollLV"

)

order by userid -- easy to compare different roles for same person