Response to ticketing question
Date: Fri, 29 May 1992 15:59:00 GMT
Message-ID: <1992May29.145845.13559_at_synapse.bms.com>
Check this out. It seems to work. Heres the spoll file.
SQL> desc tickets
Name Null? Type ------------------------------- -------- ---- SALESMAN CHAR(10) TICKETNO NUMBER
SQL> select * from tickets order by 1,2;
SALESMAN TICKETNO
---------- ----------
JOHN 1 JOHN 2 JOHN 76 JOHN 77 RICK 51 RICK 52 RICK 176 RICK 177 TOM 151 TOM 152 WAYNE 76 SALESMAN TICKETNO
---------- ----------
WAYNE 77
12 rows selected.
SQL> get sales.sql
1 select a.salesman, a.ticketno , MAX(b.ticketno),
2 (MAX(b.ticketno) -a.ticketno+1)
3 from ticketS b, ticketS c, ticketS a
4 where
5 a.ticketno = 1+c.ticketno (+)
6 and a.salesman = c.salesman(+)
7 and c.ticketno IS NULL
8 and (b.salesman,b.ticketno) IN
9 ( select d.salesman, d.ticketno
10 from ticketS d
11 where d.salesman = a.salesman
12 connect by prior d.ticketno = d.ticketno - 1
13 start with d.ticketno = a.ticketno )
14 group by a.salesman, a.ticketno
15* order by a.salesman, a.ticketno
SQL> /
SALESMAN TICKETNO MAX(B.TICKETNO) (MAX(B.TICKETNO)-A.TICKETNO+1) ---------- ---------- --------------- ------------------------------ JOHN 1 2 2 JOHN 76 77 2 RICK 51 52 2 RICK 176 177 2 TOM 151 152 2 WAYNE 76 77 2
6 rows selected.
Anil Sinha Received on Fri May 29 1992 - 17:59:00 CEST