rak11 rak11 - 6 months ago 8
SQL Question

SQL Query To Capture Starting and Ending Revision Status

I am trying to write an SQL Server query where a specific ID can have multiple revisions, but I only want to pull the starting status and the current/end status. I don't necessarily care what happened along the way.

Here is a sample of the current database structure (I cannot assume the data will be sorted).

ID REV STATUS CURRENT NOTES
----------------------------------------------
1111 1 High F Note One
1111 2 High F Note Two
1111 3 Medium F Note Three
1111 4 Medium F Note Four
1111 5 Medium F Note Five
1111 6 Medium F Note Six
1111 7 Complete T Note Seven
2222 1 Medium F Note One
2222 2 High F Note Two
2222 3 Complete T Note Three
3333 1 Low T Note One
4444 1 Low F Note One
4444 2 Medium T Note Two


Here is my desired output.

ID START STATUS END STATUS END NOTE
---------------------------------------------------
1111 High Complete Note Seven
2222 Medium Complete Note Three
3333 Low Low Note One
4444 Low Medium Note Two


Any help would be greatly appreciated.

Answer

At first I thought you just wanted the first and last rows and I hadn't noticed the current column...

    select * from 
        (
        select *
            row_number() over (partition by id order by rev asc) rnA,
            row_number() over (partition by id order by rev desc) rnD
        from <table> revs
        ) r
    where rnA = 1 or rnD = 1

After actually reading the question closer...

select
    id,
    min(case when rev = 1 then status end) as "Start Status",
    min(case when current = 'T' then status end) as "End Status",
    min(case when current = 'T' then status end) as "End Note"
from <table> rev
group by id