rak11 rak11 - 2 years ago 61
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).

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.

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 Source

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...

    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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download