CODEWITHSUNDEEP
sqlsql-server
ACJ
ACJ

Reputation: 13

Selecting changed rows from an audit table

I have an audit table which logs when a user changes a nominal code for a parts list.

As you can see the first column is the part, the second column is the date changed and the third is a nominal code.
This is just a sample of the data, the table actually has a huge amount of rows.

I want to bring back only those parts where the nominal code has changed, showing what it was originally and what it is now.
Basically there was a bug where a process kept changing the code, so I need to know what it was before.

| PART       | DATE CHANG | Nominal code
|------------|------------|-------
| ENGINEWORK | 05/08/2014 | 4000 |
| ENGINEWORK | 06/08/2014 | 4007 |
| ENGINEWORK | 04/09/2014 | 4007 |
| ENGINEWORK | 05/09/2014 | 4007 |
| ENGINEWORK | 15/09/2014 | 4007 |
| ENGINEWORK | 25/09/2014 | 4007 |
| HYPOIDOIL  | 05/08/2014 | 4005 |
| HYPOIDOIL  | 07/08/2014 | 4005 |
| HYPOIDOIL  | 08/08/2014 | 4000 |
| HYPOIDOIL  | 14/08/2014 | 4000 |
| LLB382     | 05/08/2014 | 5000 |
| LLB382     | 07/08/2014 | 4000 |
| LLB382     | 07/08/2014 | 5000 |
| LLB382     | 08/08/2014 | 4000 |
| LLB382     | 14/08/2014 | 4000 |
| LLB382     | 20/10/2014 | 4000 |

Upvotes: 1

Views: 152

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You seem to want the first and last values for the code. I would suggest window functions:

with t as (
      select t.*, row_number() over (partition by part order by datechange) as seqnum,
             count(*) over (partition by part) as cnt
      from audit t
     )
select part,
       max(case when seqnum = 1 then nominalcode end) as firstcode,
       max(case when seqnum = cnt then nominalcode end) as lastcode
from t
group by part
having max(case when seqnum = 1 then nominalcode end) <> max(case when seqnum = cnt then nominalcode end);

Upvotes: 2

Will
Will

Reputation: 928

I believe your desired result set shows each time the part number changes. This query will find every time the code changes value for a part and the date in which that event occurred.

    with rankedResult
    as (
        select t.*,
            row_number() over (
                partition by part order by dateChanged
                ) as seqnum
        from Audit t
        )
    select t.part,
        t.code CurrentCode,
        t2.code ChangedToCode,
        t2.dateChanged ChangedDate
    from rankedResult t
    inner join rankedResult t2 on t.part = t2.part
        and t.seqnum = t2.seqnum + 1
        and t.code != t2.code;

Upvotes: 0

Related Questions