CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Mike Mont
Mike Mont

Reputation: 11

SQL Server: Find latest effective date for Company/DeptID

We have a DeptID table that has multiple records per company/deptID/action. How can I pull a list that only includes the latest eff date for a company/deptID, regardless of the action? I would like to show these columns and a few other in the record with the query.

 Co#   DeptID  Action  Eff Date
 ---   ------  ------  --------
 01    12      Closed  03/22/2018 
 01    24      Closed  03/03/2014 
 01    24      Open    07/01/2010
 01    365     Closed  01/01/2008
 01    365     Open    05/01/2010

This SQL is close, but I can't get it to list only the last effective date with the action. If I remove the action, then it will list the last effective date of the company/deptID, but I need to know for which action it applies to - open or close?

select distinct 
    loc.CompanyNumber, loc.DeptID, loc.Action, 
    max(loc.effdate) over (partition by companynumber, deptid) as LocmaxDate
from 
    [dbo].[LocationsOpenClosed] as loc

Upvotes: 1

Views: 38

Answers (2)

StelioK
StelioK

Reputation: 1781

You can use a CTE (Common Table Expression) combined with Rownumber() like this:

; with 
cte as 
(select loc.CompanyNumber
    , loc.DeptId
    , loc.Action
    , loc. EffDate
    , RowNumber() over (partition by loc.CompanyNumber, loc.DeptId order by loc.EffDate desc) as RN
from    [dbo].[LocationsOpenClosed] as loc
) 
select  cte.CompanyNumber
    , cte.DeptId
    , cte.Action
    , cte.EffDate
from    cte
where   1 = 1
    and cte.RN = 1

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can use subquery :

select loc.*
from [dbo].[LocationsOpenClosed] as loc
where effdate = (select max(loc1.effdate) 
                 from [dbo].[LocationsOpenClosed] as loc1
                 where loc1.companynumber = loc.companynumber 
                       loc1.deptid = loc.deptid
                 );

However, i would also suggest to use ranking function :

select *
from (select *, 
             row_number() over (partition by companynumber, deptid order by effdate desc) as seq
      from [dbo].[LocationsOpenClosed]
     ) loc
where seq = 1;

Upvotes: 2

Related Questions