CODEWITHSUNDEEP
sqlsql-server
Davide Porro
Davide Porro

Reputation: 29

SQL Server: conditional subqueries?

I am designing a query on a single table, but I am almost stuck when I have to get different information conditionally based on subquery's results.

All is about a table named UUTResults where I store information for each UUT (unit Under Test) tested on my system. The UUTresults Table's relevant fields are:

UUTresultsId (PrimaryKey)
SerialNumber (Unit under Test S/N)
StatusId (The Test Result). it can be 1 (Passed), 2 (Failed), 3 (Error), 4 (Aborted)

Some UUT are tested more than once and each retest might have different results. Example:
10 Distinct Serial Numbers
6 Serials Tested Once = 6 Entries in the UUTResults Table
4 distinct Serials tested three times = 12 Entries in the UUTResults Table
Total 18 Entries in the UUTResults Table

Here a simplified example of possible records logged in the table: 

UUTresultsId  SerialNumber  StatusId 
    1            1            1        
    2            1            2        
    3            2            1        
    4            1            3        
    5            2            1        
    ...         ...         ...
    18           10           2

GOAL: I need to get SerialNumber and the Result according the following rule:

  1. Only Serial Numbers tested more than once are considered.
  2. I need to consider only the last test (higher Primary Key) for each distinct SerialNumber.
  3. If the Last test does not match a condition (not euql to PASSED or FAILED) I need to get the result logged before for THAT serial number.
  4. This would be enough but just to have it completely done it’d be great to go on at point 2. Until the result is acceptable (Passed or Failed).

With data from the table above, SerialNuber 1 has been tested 3 times but since the last result is ERROR (3) I need to get the test before for that Serial Number (PK UUTresultsId == 2): the result is 2 (FAILED) and it's fine so I can go on with the next Serial Number.

I have created a query that covers points 1 and 2 by using JOIN to join queries and MAX() on primary key (see attched image) but I'm stuck wth points 3 (and 4)
enter image description here

does anybody like quizzes? I Hope so :)

Thank you!

Upvotes: 0

Views: 148

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

If you need the last test for each SerialNumber that is PASS or FAIL, you can use filtering and window functions:

select r.*
from (select r.*,
             row_number() over (partition by SerialNumber order by UUTresultsId desc) as seqnum
      from UUTresults r
      where statusid in (1, 2)
     ) r
where seqnum = 1;

Note: This assumes that UUTresultsId is a monotonically increasing id used to determine the "latest". You don't have a date/time so this seems like a reasonable assumption.

Upvotes: 1

Related Questions