scsimon scsimon - 1 month ago 15
SQL Question

How to conditionally limit results based on a single column value

I'm working in a 3rd Party DB which uses row versioning heavily. Usually this isn't an issue, but with a particular dataset it is due to the loose business rules on the front end. As you will notice, the

INNER JOIN
is used to get the group of rows with the most recent
EffectiveDate
for each
EmpID
. This logic should always be used unless there is a row where
EnrollmentStatus = 'Enrolled'
. If this exists, then this group of rows should be returned. By group of rows I mean
EmpID
and
EffectiveDate
.

In the data set below the desired results would be the 4 rows where
EffectiveDate = '2015-12-15'
since it contains the record where
EnrollmentStatus = 'Enrolled'
. If no records for each
EmpID
have a
EnrollmentStatus = 'Enrolled'
then the inner join will suffice.

I'm sure I'm overlooking the elegant way to do this.

if object_id('tempdb..#emp') is not null drop table #emp

create table #emp
(EmpID int,
EmpBenID int,
EffectiveDate datetime,
EligibilityDate datetime,
EnrollBeginDate datetime,
OverrideEnrollBeginDate datetime,
EnrollEndDate datetime,
OverrrideEnrollEndDate datetime,
EnrollStatus varchar(64))

insert into #emp(EmpID, EmpBenID, EffectiveDate, EligibilityDate, EnrollBeginDate,OverrideEnrollBeginDate,EnrollEndDate,OverrrideEnrollEndDate,EnrollStatus)
values
(1950,55403,'2015-12-15 00:00:00','1998-11-02 00:00:00',NULL,NULL,NULL,NULL,'Not Enrolled'),
(1950,55404,'2015-12-15 00:00:00','1998-11-02 00:00:00','1998-12-01 00:00:00',NULL,NULL,NULL,'Enrolled'),
(1950,55405,'2015-12-15 00:00:00','1998-11-02 00:00:00',NULL,NULL,NULL,NULL,'Not Enrolled'),
(1950,55406,'2015-12-15 00:00:00','1998-11-02 00:00:00',NULL,NULL,NULL,NULL,'Not Enrolled'),
(1950,55407,'2016-01-12 00:00:00','1998-11-02 00:00:00',NULL,NULL,NULL,NULL,'Not Enrolled'),
(1950,55408,'2016-01-12 00:00:00','1998-11-02 00:00:00','2011-01-19 00:00:00',NULL,'2011-08-31 00:00:00',NULL,'Not Enrolled'),
(1950,55409,'2016-01-12 00:00:00','1998-11-02 00:00:00',NULL,NULL,NULL,NULL,'Not Enrolled'),
(1950,55410,'2016-01-12 00:00:00','1998-11-02 00:00:00',NULL,NULL,NULL,NULL,'Not Enrolled')


select e.*
from #emp e
inner join
(select EmpID, Max(EffectiveDate) dt
from #emp
--Attempted multiple filtering methods here while trying to avoid a sub-select
group by EmpID) e2 on e2.EmpID = e.EmpID and e2.dt = e.EffectiveDate

Answer

I would do this using window functions, but that is another matter. If I understand correctly, you want this logic:

select e.* 
from #emp e inner join
     (select EmpID,
             coalesce(max(case when EnrollmentStatus = 'Enrolled' then EffectiveDate end), 
                      max(EffectiveDate)
                     ) dt
      from #emp
--Attempted multiple filtering methods here while trying to avoid a sub-select
      group by EmpID
     ) e2
     on e2.EmpID = e.EmpID and e2.dt = e.EffectiveDate;

This gets the date for 'Enrolled', if it exists. Otherwise, it gets the maximum date.

Comments