3

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
S3S
  • 24,809
  • 5
  • 26
  • 45
  • 2
    sorry, but still dont understand the logic behind your problem. Your sample is incomplete should have empID with both cases also include the expected result. Please read [**How-to-Ask**](http://stackoverflow.com/help/how-to-ask) And here is a great place to [**START**](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) to learn how improve your question quality and get better answers. – Juan Carlos Oropeza Oct 13 '16 at 14:33
  • Sorry you don't understand @JuanCarlosOropeza. I can include the expected result if it will help you, but i noted what the expected or desired result should be in the OP. The sample is complete, and would only be replicated for other EmpIDs i can assure you. – S3S Oct 13 '16 at 14:58
  • Again, I already say I didnt understand it. And your description of the problem wasnt complete otherwise the answers below wouldnt have to guess what result you want. – Juan Carlos Oropeza Oct 13 '16 at 15:01
  • @JuanCarlosOropeza Sorry you feel that way, but they seemed to nail it. Cheers. – S3S Oct 13 '16 at 15:03
  • Dont worry for my feeling, you are lucky Gordon Jedi powers found the androids you were looking for. But even him wasnt sure what your want. Is just a friendly advice try to work a litle more in your question so we dont have to guess. – Juan Carlos Oropeza Oct 13 '16 at 15:13
  • @JuanCarlosOropeza I tried to make it as clear as possible. I even gave the DDL and populated the tables which maybe 1% of people do. I admit it's not a typical question and therefore hard to understand. However, it isn't due to lack of effort / work to make it as clear as possible. I can assure you that I have read the How-to-Ask and spaghettidba, which is a favorite of Sean Lange and others including myself--though they are usually more appropriately referenced in my opinion. I am, however, thankful for Gordon's and everyone's answers. – S3S Oct 13 '16 at 15:52

2 Answers2

3

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • But that will result in only 1 record with `Enrolled` . I think he wants "The maximum date available or **all** the records with 'Enrolled'" – sagi Oct 13 '16 at 14:42
  • Thanks Gordon, this is close to what I was trying using `MAX(COALSECE())` but I was just thinking backwards. Your solution works so far--and i expect it to on the larger data set. Mind elaborating on your window functions method? – S3S Oct 13 '16 at 14:46
  • @scsimon . . . The window function would calculate the maximum date and the maximum enrolled date as separate variables, and then use a `where` clause to choose the best one. – Gordon Linoff Oct 14 '16 at 00:11
1

I would use this:

select EmpID, EffectiveDate
from #emp e1
where EnrollStatus = 'Enrolled'
union
select e1.EmpID, max(e1.EffectiveDate)
from #emp e1
where e1.EnrollStatus <> 'Enrolled'
and not exists (select 1 from #emp e2 where e1.EmpID = e2.EmpID and e2.EnrollStatus = 'Enrolled')
group by e1.EmpID

It gets the effective date for those enrolled, and the max effective date for those not enrolled (and who do not have a status of enrolled at any point)

JohnHC
  • 10,935
  • 1
  • 24
  • 40