Re: Thinking about code or SP

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Kenny M. (KennyM_at_discussions.microsoft.com)
Date: 01/04/05 

Date: Mon, 3 Jan 2005 17:37:05 -0800

Good!!

But what can I modify if I want to get all the numbers inside the gap in one
column?

"John Gilson" wrote:

> "Kenny M." <KennyM@discussions.microsoft.com> wrote in message
> news:068685F0-23B2-460F-B553-F41973C2CBAC@microsoft.com...
> > hi
> >
> > I have a SqlServer DB. A table receives tickets in secuence 1,2,3,4,5.... I
> > want to create an routine that can detect if a ticket is missing so I just
> > need to detect if the secuence is broken (1,2,3,5... 4 is missing)
> >
> >
> > I want to create a eficient routine (ASPNET/ C#) but I was thinking that
> > maybe a SP or TSQL instruction can facilitate the job.
> >
> > Is there something in SQLServer Stuff that can help me?
> >
> >
> > ______
> > Kenny M.
>
> CREATE TABLE Tickets
> (
> ticket_nbr INT NOT NULL PRIMARY KEY
> )
>
> -- Sample data
> INSERT INTO Tickets (ticket_nbr)
> VALUES (5)
> INSERT INTO Tickets (ticket_nbr)
> VALUES (6)
> INSERT INTO Tickets (ticket_nbr)
> VALUES (10)
> INSERT INTO Tickets (ticket_nbr)
> VALUES (12)
>
> If you need only gaps within the sequence, try:
>
> SELECT MAX(T2.ticket_nbr) + 1 AS begin_gap,
> T1.ticket_nbr - 1 AS end_gap
> FROM Tickets AS T1
> INNER JOIN
> Tickets AS T2
> ON T2.ticket_nbr < T1.ticket_nbr
> GROUP BY T1.ticket_nbr
> HAVING MAX(T2.ticket_nbr) + 1 < T1.ticket_nbr
> ORDER BY begin_gap
>
> begin_gap end_gap
> 7 9
> 11 11
>
> If the sequence must begin at 1 and you need the preceding gap also, try:
>
> SELECT COALESCE(MAX(T2.ticket_nbr) + 1, 1) AS begin_gap,
> T1.ticket_nbr - 1 AS end_gap
> FROM Tickets AS T1
> LEFT OUTER JOIN
> Tickets AS T2
> ON T2.ticket_nbr < T1.ticket_nbr
> GROUP BY T1.ticket_nbr
> HAVING COALESCE(MAX(T2.ticket_nbr) + 1, 1) < T1.ticket_nbr
> ORDER BY begin_gap
>
> begin_gap end_gap
> 1 4
> 7 9
> 11 11
>
> --
> JAG
>
>
>

Relevant Pages

  • Re: yellow lines
    ... You should take some pictures of the big gap. ... magistrate if you don't actually get BEFORE a magistrate. ... Either a sympathetic magistrate OR an enforcement ... Our local police will not issue tickets on vehicles parked on yellows ...
    (uk.legal)
  • Re: Senior Railcard Question
    ... I think you may find not everyone likes Saver tickets because of the complex array of restrictions. ... I'm not against fully flexible, unrestricted tickets, but IMO Savers are a messy way of to handle the middle gap in the market. ... I pay a price to get in and often see less than 20% of seats full. ... As more tickets are sold the price goes up, you either take a seat for the next showing at the going rate or buy a cheaper ticket for another showing. ...
    (uk.railway)
  • Looking for a macro to automatically generate numbers in "cut-stack" order...
    ... tickets in "cut-stack" order. ... given "nnn" sheets of paper, the order of each ticket number is ... and the second page contains the following sequence of numbers: ... In the macro, I want to be prompted for the Start_Number, Stop_Number ...
    (microsoft.public.excel)
  • Re: Lottery Math? Question
    ... The odds against three particular tickets producing that sort of sequence are 1 in 77,000 approx, although the odds of that sort of run happening at any time are substantially less exciting because you either have to account for the number of eligible sequences or discount the first 'win'. ... If there was a convenient lottery outlet I'd buy a ticket for Wednesday's rollover (M£7 jackpot), ...
    (rec.gambling.lottery)
  • Re: Query to find amissing number
    ... Let's assume we have a table of people who bought tickets that are ... PRIMARY KEY (buyer, ticket_nbr)); ... FROM Tickets AS T1, Sequence AS S1 ... AND seq NOT IN (SELECT ticket_nbr -- get missing numbers ...
    (comp.databases.sybase)