Re: Creating a Trouble Ticket
Date: 3 Feb 2003 13:46:59 -0800
Message-ID: <c0d87ec0.0302031346.7f0c1da5_at_posting.google.com>
>> Any idea how to implement the running number or the service
request? <<
One idea is to build a table of ticket numbers, like one of those bakery shop machines with the tear off paper tickets.
CREATE TABLE TroubleTickets
(ticket_date DATE NOT NULL,
ticket_nbr CHAR(11) NOT NULL
CHECK (ticket_prefix SIMILAR TO
'SA[0-9][0-9][01][0-9][0-3][0-9][0-9][0-9][0-9]'), used_ticket_nbr CHAR(1) NOT NULL DEFAULT 'n'
CHECK (used_ticket_nbr IN ('n', 'y')), PRIMARY KEY(ticket_date, ticket_nbr));
Every time you use a ticket, you mark it as used. Generating a day's worth of tickets is pretty easy. Build one table of the "yymmdd" strings with a real date and a Sequence table with CHAR(3) sequential numbers. Load the ticket machine every morning with:
INSERT INTO TroubleTickets (ticket_date, ticket_nbr, used_ticket_nbr) SELECT CURRENT_TIMESTAMP,
'SA' || T1.date_string || S1.seq_string, 'n' FROM YYMMDD AS T1 CROSS JOIN Sequence AS S1
WHERE T1.cal_date = CURRENT_TIMESTAMP
AND NOT EXISTS
(SELECT * FROM TroubleTickets AS T1 WHERE T1.cal_date = CURRENT_TIMESTAMP;
Then you can clear out used numbers and save the last unused number with
DELETE FROM TroubleTickets
WHERE ticket_date < CURRENT_TIMESTAMP
AND (used_ticket_nbr = 'y'
OR ticket_nbr > (SELECT MIN(ticket_nbr) FROM TroubleTickets AS T1 WHERE T1.ticket_date = TroubleTickets.ticket_date AND T1.used_ticket_nbr = 'n'));
Then you will need a transaction to get a lock on a ticket number when you issue the trouble tickets, butn that is pretty straight forward. Received on Mon Feb 03 2003 - 22:46:59 CET