CODEWITHSUNDEEP
sqlsql-server
bkersh1
bkersh1

Reputation: 43

SQL Server 2008, how to get value of a certain column where another column changed?

I'm trying to add a field called "change date" which I indicated in the table. I have the table already with the other three columns. It is shown below but I want to get the date of the last change for each contract. Would this involve some kind of loop? I am not sure where to start here. The dates are just examples, it could be anywhere from 2007 to current. Thanks for the help.

Date Contract Status Change Date
9/30/2015 1 A 6/30/2015
8/31/2015 1 A 6/30/2015
7/31/2015 1 A 6/30/2015
6/30/2015 1 A 6/30/2015
9/30/2015 2 D 8/31/2015
8/31/2015 2 D 8/31/2015
7/31/2015 2 A 6/30/2015
6/30/2015 2 A 6/30/2015
9/30/2015 3 D 9/30/2015
8/31/2015 3 E 8/31/2015
7/31/2015 3 A 6/30/2015
6/30/2015 3 A 6/30/2015

Upvotes: 1

Views: 48

Answers (4)

zarruq
zarruq

Reputation: 2465

You Can achieve this using Windows Aggregate Functions.

Below is the query that will return your desired result.

 select  Date_Contract,
    contract,
    status, 
    min(date_Contract) over(partition by contract, status) 
    from table1;

DEMO

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271061

You can seemingly do this with a window function:

select t.*, min(date) over (partition by contract, status) as change_date
from t;

But the problem is a bit trickier. You want the earliest date from the most recent group of similar statuses. One method is to identify the groups using a difference of window functions. Then calculate the minimum within each group and then the maximum of that value:

select t.*,
       max(group_change_date) over (partition by contract, status) as change_date
from (select t.*,
             min(date) over (partition by contract, status) as group_change_date
      from (select t.*,
                   row_number() over (partition by contract order by date) as seqnum_cd,
                   row_number() over (partition by contract, status order by date) as seqnum_cds,
            from t
           ) t
      ) t;

Upvotes: 2

Koby Douek
Koby Douek

Reputation: 16693

You can use MAX and GROUP BY like this:

select MAX([Date]), contract from tbl GROUP BY conrtact

Result:

Date      | Contract 
===================
9/30/2015 | 1
9/30/2015 | 2
9/30/2015 | 3

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 82010

You can use the window function max() over

Example

Select [Date]
      ,[Contract]
      ,[Status]
      ,[Change Date] = max([Date]) over (Partition By [Contract])
 from YourTable

Returns

enter image description here

Upvotes: 0

Related Questions