Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Gaps in a sequence

4 posts in General Discussion Last posting was on 2005-07-05 06:35:17.0Z
Eddie Sizemore Posted on 2005-07-04 14:32:46.0Z
From: "Eddie Sizemore" <esizemoreno@spamqx.net>
Newsgroups: ianywhere.public.general
Subject: Gaps in a sequence
Lines: 23
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
NNTP-Posting-Host: 12-203-198-23.client.insightbb.com
X-Original-NNTP-Posting-Host: 12-203-198-23.client.insightbb.com
Message-ID: <42c9488e$1@forums-1-dub>
Date: 4 Jul 2005 07:32:46 -0700
X-Trace: forums-1-dub 1120487566 12.203.198.23 (4 Jul 2005 07:32:46 -0700)
X-Original-Trace: 4 Jul 2005 07:32:46 -0700, 12-203-198-23.client.insightbb.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4604
Article PK: 8409

I need to find missing tickets numbers. Each ticket number range is unique
by SiteCode. I have the following procedure that will find the first
missing ticket in a gap but I need to find all missing tickets.


ALTER PROCEDURE "DBA"."spMissingTicketsByDate"(IN FromDate Date)
RESULT(SiteCode CHAR(10), Ticket INTEGER, TimeIn TIMESTAMP, TimeOut
TIMESTAMP)
BEGIN
SELECT SiteCode, Ticket + 1 AS MissingTicket, TimeIn, TimeOut FROM
DBA.Ticket AS t1
WHERE "DATE"(TimeOut) >= StartDate AND
NOT EXISTS(SELECT 1 FROM DBA.Ticket AS t2 WHERE t1.SiteCode =
t2.SiteCode AND t2.Ticket = t1.Ticket + 1) AND
t1.Ticket <> (SELECT MAX(Ticket) FROM DBA.Ticket AS tm WHERE tm.SiteCode
= t1.SiteCode)
ORDER BY TimeOut desc
END


Eddie Sizemore


Glenn Paulley Posted on 2005-07-04 17:31:52.0Z
Newsgroups: ianywhere.public.general
Subject: Re: Gaps in a sequence
From: Glenn Paulley <paulley@ianywhere.com>
References: <42c9488e$1@forums-1-dub>
Organization: iAnywhere Solutions
Message-ID: <Xns968989A583A6paulleyianywherecom@10.22.241.106>
User-Agent: Xnews/5.04.25
NNTP-Posting-Host: paulley-t41.sybase.com
X-Original-NNTP-Posting-Host: paulley-t41.sybase.com
Date: 4 Jul 2005 10:31:52 -0700
X-Trace: forums-1-dub 1120498312 10.25.99.149 (4 Jul 2005 10:31:52 -0700)
X-Original-Trace: 4 Jul 2005 10:31:52 -0700, paulley-t41.sybase.com
Lines: 64
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4606
Article PK: 8410

"Eddie Sizemore" <esizemoreno@spamqx.net> wrote in
news:42c9488e$1@forums-1-dub:

> I need to find missing tickets numbers. Each ticket number range is
> unique by SiteCode. I have the following procedure that will find the
> first missing ticket in a gap but I need to find all missing tickets.
>
>
> ALTER PROCEDURE "DBA"."spMissingTicketsByDate"(IN FromDate Date)
> RESULT(SiteCode CHAR(10), Ticket INTEGER, TimeIn TIMESTAMP, TimeOut
> TIMESTAMP)
> BEGIN
> SELECT SiteCode, Ticket + 1 AS MissingTicket, TimeIn, TimeOut FROM
> DBA.Ticket AS t1
> WHERE "DATE"(TimeOut) >= StartDate AND
> NOT EXISTS(SELECT 1 FROM DBA.Ticket AS t2 WHERE t1.SiteCode =
> t2.SiteCode AND t2.Ticket = t1.Ticket + 1) AND
> t1.Ticket <> (SELECT MAX(Ticket) FROM DBA.Ticket AS tm WHERE
> tm.SiteCode
> = t1.SiteCode)
> ORDER BY TimeOut desc
> END
>
>
> Eddie Sizemore
>
>
>

One way I can think of to do this is (requires 9.x software):

a) compose a derived table that produces a Cartesian product of the
provided catalog table ROWGENERATOR to itself as many times as is
necessary to generate the number of tickets you have. Suppose we have a
variable, @starting_ticket_no, that contains the starting number of the
first ticket in the sequence:

( select (A.row_num + ( (B.row_num-1) * 256) + (@starting_ticket_no -1))
as ticket_seq
from rowgenerator as A, rowgenerator as B
order by ticket_seq )

This gives 256*256 = 65K sequential ticket numbers. You can modify this
derived table as necessary to give you ticket numbers by sitecode.

b) LEFT OUTER JOIN this derived table to your ticket table, joining on
both ticket_seq and sitecode.

Rows in the result that have NULL values from the ticket table will be
the "gaps" you're looking for.

Glenn
--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288


GeoffAtDatagaard Posted on 2005-07-05 06:00:13.0Z
From: GeoffAtDatagaard <geoff@datagaard.com.au>
User-Agent: Mozilla/5.0 (Windows; U; Win98; en-US; rv:1.8a6) Gecko/20050111
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: Gaps in a sequence
References: <42c9488e$1@forums-1-dub>
In-Reply-To: <42c9488e$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Original-NNTP-Posting-Host: static-203-87-21-88.sa.chariot.net.au
Message-ID: <42ca21e8@forums-2-dub>
X-Original-Trace: 4 Jul 2005 23:00:08 -0700, static-203-87-21-88.sa.chariot.net.au
Lines: 40
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 4 Jul 2005 23:00:11 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 4 Jul 2005 23:00:13 -0700
X-Trace: forums-1-dub 1120543213 10.22.108.75 (4 Jul 2005 23:00:13 -0700)
X-Original-Trace: 4 Jul 2005 23:00:13 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4607
Article PK: 8412

How about something like :

SELECT SiteCode, Row_Num from RowGenerator,Tickets as t1 where
not exists(select 1 from Tickets as t2 where t2.SiteCode = t1.SiteCode
and t2.Ticket = Row_Num)
and Row_Num between (select min(Ticket) from Tickets as t3 where
t3.SiteCode = t1.SiteCode and "DATE"(TimeOut) >= FromDate) and
(select max(Ticket) from Tickets as t3 where
t3.SiteCode = t1.SiteCode and "DATE"(TimeOut) >= FromDate) and
"DATE"(t1.TimeOut) >= FromDate

Eddie Sizemore wrote:
> I need to find missing tickets numbers. Each ticket number range is unique
> by SiteCode. I have the following procedure that will find the first
> missing ticket in a gap but I need to find all missing tickets.
>
>
> ALTER PROCEDURE "DBA"."spMissingTicketsByDate"(IN FromDate Date)
> RESULT(SiteCode CHAR(10), Ticket INTEGER, TimeIn TIMESTAMP, TimeOut
> TIMESTAMP)
> BEGIN
> SELECT SiteCode, Ticket + 1 AS MissingTicket, TimeIn, TimeOut FROM
> DBA.Ticket AS t1
> WHERE "DATE"(TimeOut) >= StartDate AND
> NOT EXISTS(SELECT 1 FROM DBA.Ticket AS t2 WHERE t1.SiteCode =
> t2.SiteCode AND t2.Ticket = t1.Ticket + 1) AND
> t1.Ticket <> (SELECT MAX(Ticket) FROM DBA.Ticket AS tm WHERE tm.SiteCode
> = t1.SiteCode)
> ORDER BY TimeOut desc
> END
>
>
> Eddie Sizemore
>
>


GeoffAtDatagaard Posted on 2005-07-05 06:35:17.0Z
From: GeoffAtDatagaard <geoff@datagaard.com.au>
User-Agent: Mozilla/5.0 (Windows; U; Win98; en-US; rv:1.8a6) Gecko/20050111
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: Gaps in a sequence (correction)
References: <42c9488e$1@forums-1-dub> <42ca21e8@forums-2-dub>
In-Reply-To: <42ca21e8@forums-2-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: static-203-87-21-88.sa.chariot.net.au
X-Original-NNTP-Posting-Host: static-203-87-21-88.sa.chariot.net.au
Message-ID: <42ca2a25@forums-1-dub>
Date: 4 Jul 2005 23:35:17 -0700
X-Trace: forums-1-dub 1120545317 203.87.21.88 (4 Jul 2005 23:35:17 -0700)
X-Original-Trace: 4 Jul 2005 23:35:17 -0700, static-203-87-21-88.sa.chariot.net.au
Lines: 61
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4608
Article PK: 8414

Oops

I just remembered that RowGenerator will only return you 255 rows.
In my ASA databases I use a table called Numbers(Number Numeric(10))
which I pre-load with 0 to 9999999999. You could then do something like:

SELECT SiteCode, Number as TicketNo from Numbers,Tickets as t1 where
not exists(select 1 from Tickets as t2 where t2.SiteCode = t1.SiteCode
and t2.Ticket = TicketNo)
and Row_Num between (select min(Ticket) from Tickets as t3 where
t3.SiteCode = t1.SiteCode and "DATE"(TimeOut) >= FromDate) and
(select max(Ticket) from Tickets as t3 where
t3.SiteCode = t1.SiteCode and "DATE"(TimeOut) >= FromDate) and
"DATE"(t1.TimeOut) >= FromDate

GeoffAtDatagaard wrote:
>
> How about something like :
>
> SELECT SiteCode, Row_Num from RowGenerator,Tickets as t1 where
> not exists(select 1 from Tickets as t2 where t2.SiteCode = t1.SiteCode
> and t2.Ticket = Row_Num)
> and Row_Num between (select min(Ticket) from Tickets as t3 where
> t3.SiteCode = t1.SiteCode and "DATE"(TimeOut) >= FromDate) and
> (select max(Ticket) from Tickets as t3 where
> t3.SiteCode = t1.SiteCode and "DATE"(TimeOut) >= FromDate) and
> "DATE"(t1.TimeOut) >= FromDate
>
>
>
>
>
> Eddie Sizemore wrote:
>
>> I need to find missing tickets numbers. Each ticket number range is
>> unique by SiteCode. I have the following procedure that will find the
>> first missing ticket in a gap but I need to find all missing tickets.
>>
>>
>> ALTER PROCEDURE "DBA"."spMissingTicketsByDate"(IN FromDate Date)
>> RESULT(SiteCode CHAR(10), Ticket INTEGER, TimeIn TIMESTAMP, TimeOut
>> TIMESTAMP)
>> BEGIN
>> SELECT SiteCode, Ticket + 1 AS MissingTicket, TimeIn, TimeOut FROM
>> DBA.Ticket AS t1
>> WHERE "DATE"(TimeOut) >= StartDate AND
>> NOT EXISTS(SELECT 1 FROM DBA.Ticket AS t2 WHERE t1.SiteCode =
>> t2.SiteCode AND t2.Ticket = t1.Ticket + 1) AND
>> t1.Ticket <> (SELECT MAX(Ticket) FROM DBA.Ticket AS tm WHERE
>> tm.SiteCode = t1.SiteCode)
>> ORDER BY TimeOut desc
>> END
>>
>>
>> Eddie Sizemore
>>
>>