Finding out of sequence records

Question

Our company issues ticket books to our contractors, and we track both the books, and the individual tickets inside the books (25 per book), to ensure nothing goes missing. Our books are numbers and so are the individual tickets within the book. The book is the same number as the first ticket inside the book. For example, ticket book 101 would contain tickets 101-125, ticket book 126 would contain tickets 126-150, etc...

Each contractor will receive a book, and they must use their tickets in sequential order. If a contractor has book 101, they must use the tickets in order 101, 102, 103, etc... To enforce this, we track the tickets as they are inputted into our database, by looking for out of sequence tickets, and display this in a report. For example, if the ticket come in 101, 103, then 102 must be displayed in this report as missing.

Currently this is the SQL query that I am running to check this:

select TicketNum, TicketBookNum, UnitID, DateIssued, IssuedBy
from TicketBooks a
where Used='No'
and TicketNum < (select MAX(b.TicketNum)
        from TicketBooks b
        where b.Used='Yes'
        and b.UnitID=a.UnitID
        and BookType='Truck' or BookType='Work'
        and a.DateIssued <= b.DateIssued)
and BookType='Truck'
or BookType='Work'
order by DateIssued desc

This query retrieves all unused tickets, and compares them to the used tickets to see if the unused ticket numbers are lower than the highest used ticket number for the Unit(contractor). If it is lower, that means a ticket has been skipped. Once again an example: 101, 103 are used, now the highest used ticket number is 103 and unused is 102, meaning it is lower and will display in the report.

There is a possibility at our company that a ticket book may be returned, and re-issued again. In this case, the new ticket book number may be out of order for this report. Example being:

Contractor A is using ticket book 201
Contractor B leaves company and returns ticket book 151
Contractor A finishes ticket book 201
Contractor A gets new ticket book from office. This is ticket book 151

Now this SQL statement will return all of book 151 as being missing, as all the tickets that are used in book 201 are of a higher ticket number and book 151. I can try to just look for out of sequence records within the book, but I need to be able to tell if the last ticket in the book was skipped and the first ticket in their new book was used.

What I am thinking I need to do is somehow also take into account the DateIssued of the book to return missing tickets.

I have been pulling my hair out with this problem, so any help is more than greatly appreciated.

This is example data from our database:

TicketNum  | TicketBookNum   |  UnitID        |      DateIssued    |   IssuedBy
----------------------------------------------------------------------------
105073          105051          151       2016-04-23 10:02:40.000   kbusch
105074          105051          151       2016-04-23 10:02:40.000   kbusch
105075          105051          151       2016-04-23 10:02:40.000   kbusch
102801          102801          117       2016-04-22 10:19:23.000   kbusch
102802          102801          117       2016-04-22 10:19:23.000   kbusch
102803          102801          117       2016-04-22 10:19:23.000   kbusch
102804          102801          117       2016-04-22 10:19:23.000   kbusch
102805          102801          117       2016-04-22 10:19:23.000   kbusch
102806          102801          117       2016-04-22 10:19:23.000   kbusch
102807          102801          117       2016-04-22 10:19:23.000   kbusch
102808          102801          117       2016-04-22 10:19:23.000   kbusch
102809          102801          117       2016-04-22 10:19:23.000   kbusch
102810          102801          117       2016-04-22 10:19:23.000   kbusch
102811          102801          117       2016-04-22 10:19:23.000   kbusch
102812          102801          117       2016-04-22 10:19:23.000   kbusch
102813          102801          117       2016-04-22 10:19:23.000   kbusch
102814          102801          117       2016-04-22 10:19:23.000   kbusch
102815          102801          117       2016-04-22 10:19:23.000   kbusch
102816          102801          117       2016-04-22 10:19:23.000   kbusch
102817          102801          117       2016-04-22 10:19:23.000   kbusch
102818          102801          117       2016-04-22 10:19:23.000   kbusch
102819          102801          117       2016-04-22 10:19:23.000   kbusch
102820          102801          117       2016-04-22 10:19:23.000   kbusch
102821          102801          117       2016-04-22 10:19:23.000   kbusch
102822          102801          117       2016-04-22 10:19:23.000   kbusch
102823          102801          117       2016-04-22 10:19:23.000   kbusch
102824          102801          117       2016-04-22 10:19:23.000   kbusch
102825          102801          117       2016-04-22 10:19:23.000   kbusch

As you can see, all 25 tickets in ticketbook 102801 are present. The reasoning for this is because the unit had already completed ticketbook 103001 in the past. Ticket book 102801 was issued out of order. The expected output that I would like would not display this book because it is the newest, but if the last ticket in their previous book was not used, and the first ticket in this book was used, then that skipped ticket would display in this report. Even though the tickets in this book are of a lower number than the previous book.

EDIT for Tom H's solution: Some missing tickets are not showing. For example:

Unit 403 has ticket book 94801 which was issued on April 2, 2015. Unit 403 has used tickets 94801-94815, but has not yet used tickets 94816-94825. Additionally, Unit 403 was issued ticket book 96751 on July 1, 2015 and has used tickets 96751-96762 so I would need the tickets 94816-94825 displayed in this report.

Another example:

Unit 142 was issued ticket book 99751 on October 2, 2015 and has used tickets 99751-99754 but not the last ticket in the book, ticket 99775. Unit 142 has been issued several books since then and used all tickets in those books; therefore, ticket 99775 should display in the report.

EDIT2:

Below is an image showing the data for 403's tickets

Below is showing the records returned from the query you have provided

You have columns in your query (like `used`) that don't appear in your sample data. Does every ticket appear in your table? — Tom H, May 12 '16 at 17:33
This is a "Gaps and Islands" problem, and if you google that term you will find many examples. — Tab Alleman, May 12 '16 at 17:34
@TomH `Used` is just a column that we utilized to tell if the ticket has been created in our application. Only the tickets that have been skipped will show in this report. — Chase Ernst, May 12 '16 at 17:37
@TabAlleman I am reading about this right now. I have never head about it. Thank you for the info. — Chase Ernst, May 12 '16 at 17:39

score 1 · Answer 1 · answered May 12 '16 at 18:39

1

I believe that this will give you what you're looking to get. It creates a resultset of all of the possible ticket numbers, then it checks to see which ticket numbers aren't in your table. The two CTEs for numbers are just to generate the numbers 0 through 24. Note that this isn't a good solution if the sizes of your ticket books is likely to change. Also, you should probably have separate TicketBooks and Tickets or UsedTickets tables.

;WITH CTE_TBs AS (
    SELECT
        TicketBookNum,
        UnitID,
        CASE WHEN LEAD(DateIssued) OVER (PARTITION BY UnitID ORDER BY DateIssued) IS NULL THEN 1 ELSE 0 END AS LastBook,
        MAX(TicketNum) AS MaxTicketNum
    FROM
        (SELECT DISTINCT TicketBookNum, TicketNum, UnitID, DateIssued FROM TicketBooks) SQ      -- Necessary because your database isn't properly normalized
    GROUP BY
        TicketBookNum, UnitID, DateIssued
),
CTE_Nums_1_5 AS (SELECT 1 AS num UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5),
CTE_Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY T1.num) - 1 AS num FROM CTE_Nums_1_5 AS T1 CROSS JOIN CTE_Nums_1_5 AS T2)
SELECT
    TB.UnitID,
    TB.TicketBookNum,
    TB.TicketBookNum + N.num AS MissingTicketNum
FROM
    CTE_TBs TB
LEFT OUTER JOIN CTE_Nums N ON (TB.TicketBookNum + N.num <= TB.MaxTicketNum) OR TB.LastBook = 0
WHERE
    NOT EXISTS (SELECT * FROM TicketBooks WHERE TicketNum = TB.TicketBookNum + N.num)

answered May 12 '16 at 18:39

Tom H

46,766
14
87
128

Thanks for the reply, Tom. I just got back from lunch, so I will go over this and reply to you soon. – Chase Ernst May 12 '16 at 19:35
It seems to not be returning the correct values. There are multiple instances where missing tickets within the book do not show with this procedure. Possibly start a private chat and I can share the data with you – Chase Ernst May 12 '16 at 20:20
If you can add a minimal example of the issue to your question then I can try to find the problem with the query. – Tom H May 12 '16 at 20:21
Okay, I will try my best. – Chase Ernst May 12 '16 at 20:22
I just inserted your data for unit 403 and ran the query and it returned the correct missing tickets. Please make sure that you have the correct copy of the query (I think I made one update on it shortly after initially posting). Otherwise, there must be something else causing the issue. – Tom H May 12 '16 at 20:38
Hmm.. I updated the question to reflect the exact data coming from queries. Possibly I am missing some information that would help you in assisting me. – Chase Ernst May 12 '16 at 20:43

score 1 · Answer 2 · answered May 13 '16 at 02:06

Once a contractor starts using a book he must use all tickets from it before starting a new book.

So, for a given contractor only one book (or none) in the database can have less than 25 rows. If a given contractor has more than one book in the database with less than 25 rows - there is a problem.

Note, that the logic above doesn't care when the book was issued or contractor started to use it.

CTE_IncompleteBooks groups all tickets into contractors and books, counts how many tickets were used in each book and leaves only those books that have less than 25 tickets.

Then we need to filter out those contractors that have only one incomplete book.

CTE_IncompleteBooksCount counts incomplete books for each contractor.

Final SELECT returns only those contractors that have more than one incomplete book.

WITH
CTE_IncompleteBooks
AS
(
    SELECT
        UnitID
        ,TicketBookNum
        ,COUNT(*) AS UsedTicketCount
    FROM TicketBooks
    --WHERE Used=Yes -- not clear from the question whether you need this filter
    GROUP BY
        UnitID
        ,TicketBookNum
    HAVING COUNT(*) < 25
)
,CTE_IncompleteBooksCount
AS
(
    SELECT
        UnitID
        ,TicketBookNum
        ,UsedTicketCount
        ,COUNT(*) OVER (PARTITION BY UnitID) AS IncompleteBookCount
    FROM CTE_IncompleteBooks
)
SELECT
    UnitID
    ,TicketBookNum
    ,UsedTicketCount
    ,IncompleteBookCount
FROM CTE_IncompleteBooksCount
WHERE IncompleteBookCount > 1
ORDER BY
    UnitID
    ,TicketBookNum
;

Sorry, I did not see your answer, as I was not near my computer. I will take a look at this soon, and reply back to you. Thanks. — Chase Ernst, May 13 '16 at 21:03

Finding out of sequence records

2 Answers2