Select latest record with some more conditions

Question

ID    Level Effective Date  ExpirationDate
000012-12     2 12/01/2005  NULL
000012-12    1    12/01/2005  NULL
000012-12    2    12/01/2005  01/01/2009
000012-A12    2 10/01/1994  11/30/2005
000012-A12    2 01/01/1999  11/30/2005
000012-A12    2 09/01/2001  11/30/2005
000012-A12    1 12/01/2005  12/31/2007

Only most current Records will be fetched. It means in the above scenario Exp date - If null the record is still active. If greater then current time stamp, its future exp date , which means still active. if less then current time stamp , then terminated. Most current is the most active or latest terminated record. If it has active and terminated then only active will be shown. Else last terminated record.

One ID can have 2 rows for same effective date and exp date but multiple levels. So in that case we would need to select only 1 record for level one.

So as per the data set above below is the intended output

Output

000012-12    1   12/01/2005  NULL
000012-A12  2   12/01/2005  01/01/2009

Please help

Thomas. Please look into the following data set. Insert #Test( Id, Level, EffectiveDate, ExpirationDate ) Values ('000872-A24',1,'1994-10-01',NULL); Insert #Test( Id, Level, EffectiveDate, ExpirationDate ) Values ('000872-A24',1,'1999-01-01',NULL);
Insert #Test( Id, Level, EffectiveDate, ExpirationDate ) Values ('000872-A24',2,'2001-09-01',NULL ); Insert #Test( Id, Level, EffectiveDate, ExpirationDate ) Values ('000872-A24',1,'2003-01-01','2007-12-31');

when you run the query it should give 000872-A24 2 09/01/2001 NULL

but now it returns 000872-A24 1 01/01/2003 12/31/2007

Answer 1

It is difficult to provide an answer without knowing the database product.

1. if there is no auto_increment/identity column 
2. and if there is no other primary key (which is a bad idea obviously) 
3. and if the given database product supports `CURRENT_TIMESTAMP` (each DBMS will likely have some equivalent to the current date and time) 
4. and if the target date by which you measure "latest" is the current date and time


Select Id, Level
From Table As T
Where T. EffectiveDate =    (
                            Select Max(T2.EffectiveDate)
                            From Table As T2
                            Where T2.ID = T.ID
                                And ( T2.EffectiveDate Is Null
                                    Or (
                                        CURRENT_TIMESTAMP >= T2.EffectiveDate
                                        And CURRENT_TIMESTAMP <= T2.ExpirationDate
                                        )
                                    )
                            )   

You will note a number of caveats in my answer. That is an indicatation that we need more information:

1. What database product and version?
2. Is there an auto_incrementing, unique key on the table?
3. How does the Level fit into the results you want? (Please expand your sample data to include edge cases).
4. What should happen if the current date and time is prior to the effective date that has a null expiration date?

EDIT

Now that we know you are using SQL Server 2008, that makes the solution easier:

If object_id('tempdb..#Test') is not null
    Drop Table #Test;
GO
Create Table #Test (
                    PkCol int not null identity(1,1) Primary Key
                    , Id varchar(50) not null
                    , Level int not null
                    , EffectiveDate datetime not null
                    , ExpirationDate datetime null
                    );

Insert #Test( Id, Level, EffectiveDate, ExpirationDate ) Values ('000012-12',2,'12/01/2005',NULL);
Insert #Test( Id, Level, EffectiveDate, ExpirationDate ) Values ('000012-12',1,'12/01/2005',NULL); 
Insert #Test( Id, Level, EffectiveDate, ExpirationDate ) Values ('000012-12',2,'12/01/2005','01/01/2009');
Insert #Test( Id, Level, EffectiveDate, ExpirationDate ) Values ('000012-A12',2,'10/01/1994','11/30/2005');
Insert #Test( Id, Level, EffectiveDate, ExpirationDate ) Values ('000012-A12',2,'01/01/1999','11/30/2005');
Insert #Test( Id, Level, EffectiveDate, ExpirationDate ) Values ('000012-A12',2,'09/01/2001','11/30/2005');
Insert #Test( Id, Level, EffectiveDate, ExpirationDate ) Values ('000012-A12',1,'12/01/2005','12/31/2007');

With Items As
    (
    Select PkCol, Id, Level, EffectiveDate, ExpirationDate
        , Row_Number() Over (   Partition By Id
                                Order By EffectiveDate Desc, Coalesce(ExpirationDate,'99991231') Desc, Level Asc ) As Num
    From #Test
    )
Select PkCol, Id, Level, EffectiveDate, ExpirationDate
From Items
Where Num = 1

In your sample output, you have the combination ('000012-A12',2,'12/01/2005','01/01/2009') which does not appear in your original data.

I'm using two features that were added in SQL Server 2005: common-table expressions and ranking functions. The common-table expression Item acts like a in-place view or query. The ranking function Row_Number is where the real magic happens. As the name implies, it returns a sequential list of numbers ordered by the Order By clause. However, it also restarts numbering for each Id value (that's the Partition By bit). By filtering on Num = 1, I'm returning the "top" value for each Id.