Re: Creating a Trouble Ticket

From: --CELKO-- <71062.1056_at_compuserve.com>
Date: 3 Feb 2003 13:46:59 -0800
Message-ID: <c0d87ec0.0302031346.7f0c1da5@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 - 15:46:59 CST