I am having issues with a sql procedure I wrote quite some time ago. The procedure is designed to generate a report that displays all missing records within our database.
For example, if ticket numbers 10, 11, and 13 have been used, then the procedure should return ticket number 12 on the report.
We manage each ticket book and issue them out accordingly when a person takes one. When the ticket book is issued, all 25 tickets in the book are created in the database, and when each individual ticket is entered into the database, the ticket is marked as used. This is how the procedure checks what tickets are used and not used.
This is the current procedure we are utilizing
create procedure FindMissingTickets
select TicketNum, TicketBookNum, UnitID, DateIssued, IssuedBy
from TicketBooks a
and a.TicketNum < (select MAX(b.TicketNum)
from TicketBooks b
and b.BookType='Truck' or b.BookType='Work')
and a.BookType='Truck' or a.BookType='Work'
order by TicketNum desc
It's just missing parentheses, you need to place parentheses around the last part of your where clause:
and (a.BookType='Truck' or a.BookType='Work')