SQL Query (Dynamic?) to Verify Each History Entry Has a Match

Question

I have a stored procedure that is generating a random float number as an ID marker, and as soon as it is called it stores the random number + .1 into a history/logging table, and then the end result is stored with the same random number +.2 (as a sequencer/sorter).

Is there an easy way to dynamiclly query a column filled with these sequence numbers (as exampled below) and to verify there are an even number of them (pairs) in order to easily make sure that the stored procedure has always processed properly?

1568.1
1568.2
8452.1
8452.2
9886.1
9886.2
5455.1
3682.1
3682.2
4857.1
4857.2

In the sample data above I would like a query that alerts to the presence of 5455.1 as it does not have a second part (5455.2) which it always should have as my stored procedure logs a second entry with the results of the call (success or failure).

Of course, I need it to not only alert me that the problem/oddball exists (as I could simply run a count on the table and see if the number is even to determine that), I would like to be able to select the mismatches so I am not sorting through 10,000 lines of history/logs to find the one that does not have a partner.

Answer 1

First, your ID-Key should not be one column, it should be two. That is, instead of

CREATE TABLE OneCol  (IDKey DECIMAL(6, 1) Primary Key);

You should have something like:

CREATE TABLE TwoCol  
(
    IDRand INT, 
    PhaseID TINYINT
    Primary Key  (IDRand, PhaseID)
)

Assuming this then and additionally that the specific anomaly that you are looking for is an ID with {Phase=1) but no corresponding {Phase=2} this one way to write the query:

SELECT IDRand FROM TwoCol WHERE PhaseID = 1
EXCEPT
SELECT IDRand FROM TwoCol WHERE PhaseID = 2

Answer 2

Sorry for the length here, but I am hoping that it may help the next person that comes along...

As suggested above, I will split the random id into two fields - the first with the random id, and the second with the "phase" id. Using the except statement above works to return a list of random ids that were started (phase 1 exists) but are missing the second/completion phase.

However, since I do not want to spend time looking up each RID individually, I wrapped the EXCEPT statement in an IN statement as copied below. This allows me to control which fields are actually returned.

SELECT randomid, phaseid, info, type, time
FROM history
WHERE randomid IN (
SELECT randomid FROM history WHERE phaseid = '1' 
EXCEPT 
SELECT randomid FROM history WHERE phaseid = '2'  
)

Note: Wrapping the EXCEPT statement like I did results in the distinct portion (see link below) being ignored since I am specifying to select all. Also, I will need to improve my random number range and or add a date code to it in order to prevent a duplication. If I ever happened to get the same random number twice, and if one completed and one failed, the one that failed (did not have a phase 2) would not show up in the EXCEPT statement query results as the existence of ANY matching completion phase will satisfy the EXCEPT clause. It is not checking to see that there are that there is a completion phase for each and every starting phase, only that a completion phase with a matching random id exists at all.

If I had a poorly randomized number scheme that resulted in duplicates , I could have 16 entries in the table with the same random id (let's say the random id is 1337). The even number of entries with an RID of 1337 would look okay even though I may have simply had an even number of errors (checking for an even count of entries in the database really is not a good way to check for errors now that I think of it, although I could count the number of phase ids that are 1 and compare it to a count of those with a pid of 2 in order to verify the results of my query). Of those 16 entries, let's say that 6 were completed properly (phase 1 and phase 2), and the remaining 4 had failed somehow. Since an entry in the database existed for RID 1337 with a PID of 2, none of the 4 failures would show up.

Thanks for pointing me in the right direction, Barry!

========================================================

The following link seemed to go a little more in depth with the EXCEPT operator examples than other sites, despite it being older (statiung that only distinct results are returned, vs. all results).

http://www.databasejournal.com/features/mssql/article.php/3602601/Except-Operator-in-SQL-Server-2005.htm

========================================================

Pinal Dave's general summarization of the operator:

One of the JR. Developer asked me a day ago, does SQL Server has similar operation like MINUS clause in Oracle.

Absolutely, EXCEPT clause in SQL Server is exactly similar to MINUS operation in Oracle. The EXCEPT query and MINUS query returns all rows in the first query that are not returned in the second query. Each SQL statement within the EXCEPT query and MINUS query must have the same number of fields in the result sets with similar data types.