Cacher is the code snippet organizer for pro developers

--This query can be used to check the enrollment types of certain plans that are assigned to a specific benefit class. This can be useful when sometimes enroll can't be saved/times out on the front end due to conflict enroll types (typical example is 2 plans of the same benefit plan type have auto-enroll at the same time). 
--The date filter on Benefit Class - Assign Plan page doesn't work well. So this query can help you quickly check if there's enroll type conflict.
--Replace benefit class ID at the red underlined part

SELECT
	bce.planID,
	pn.benefitPlanTypeID,
	zet.enrollmentTypeDescription,
	bce.benefitClassID,
	pd.effectiveDate,
	pd.expirationDate,
	bce.effectiveDate,
	bce.terminationDate,
	bce.savedUserID,
	bce.savedDateTime,
	pn.planName,
	pn.clientid,
	pn.enrollmentTypeLV
FROM benefitclasseligibility bce WITH (NOLOCK)
LEFT JOIN planname pn WITH (NOLOCK)
	ON pn.planID = bce.planID
LEFT JOIN planDetail pd WITH (NOLOCK)
	ON pd.planID = pn.planID
LEFT JOIN zEnrollmentTypeLV zet WITH (NOLOCK)
	ON zet.enrollmentTypeLV = pn.enrollmentTypeLV
WHERE pn.removedDate IS NULL
AND GETDATE() BETWEEN bce.effectiveDate AND bce.terminationDate
AND GETDATE() BETWEEN pd.effectiveDate AND pd.expirationDate
AND bce.benefitClassID = BenefitClassIDYouWantToCheck
ORDER BY pn.benefitPlanTypeID