Response to ticketing question

From: <KML110_at_psuvm.psu.edu>
Date: Fri, 29 May 1992 08:30:40 EDT
Message-ID: <92150.083040KML110_at_psuvm.psu.edu>

**As a newcomer to the bulletin boards, I am unclear on how to bring up old postings. This message is in response to a posting from 5/26-5/27. Please notify the poster that this response is out here. Thanks - KML **

The following problem was posted: how to resolve a series of ticket sequences sold by salesman, considering that the sequences were not contiguous: Jack sold tickets 1-50 and 100-149, while Kevin sold tickets 51-99 and 150-200. How do you get a report that shows:

           Salesman    Starting   Ending    Count
             JACK         1          50      50
                         100        149      50

             KEVIN       51          99      49
                         150        200      51

The original idea, to group by salesman, will not work since that will return one record per salesman. You need one record per combination of salesman and starting ticket number. My solution follows.

First, create a view called starting_points:

        create view starting_points as
        select distinct x.salesman, x.ticket from tickets x, tickets y
        where (x.ticket=y.ticket+1 and x.salesman != y.salesman or x.ticket=1);

This will display the starting point of each sequence for each salesman. Do the same for the ending points, this time using -1 and also picking up the max ticket number (starting_points assumed a min ticket number of 1. This can easily be changed to a subquery using min, similar to the one in ending_points)

    create view ending_points as select distinct x.salesman,x.ticket     from tickets x, tickets y
    where (x.ticket=y.ticket-1 and x.salesman != y.salesman

         or x.ticket=(select max(ticket) from x));

So we now have the ending points of each sequence for each salesman. now put the two together:

        select s.salesman,s.ticket,e.ticket,e.ticket-s.ticket+1
        from starting_points S, ending_points E
        where S.salesman = E.salesman
        and E.ticket = (select min(ticket) from ending_points Z
        where z.salesman = s.salesman and z.ticket > s.ticket);

and you're done! Received on Fri May 29 1992 - 14:30:40 CEST