Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Missing values in a sequence with SQL

Author  Topic 

Coolda
Starting Member

10 Posts

Posted - 2012-05-03 : 09:42:16
Hello,

I was wondering if I can get some assistance on an issue I have.
I need a query that could locate the missing entries in a sequence that rolls over at a certain point.

An example of my data set:

ID TicketID
1 00001
2 00002
3 00003
4 00005
5 00006
6 00007
7 00009
8 00010
9 00001
10 00002
11 00003
12 00004
13 00005
14 00006
15 00007
16 00008
17 00009
18 00010

My Ticket values restart to one after a certain point. When I run the query I have, it would not have any errors because of the latter rows. But I need a query that would be able to determine the missed values for TicketID: 4 and 8.

My current query is:

select start, stop from (
select m.Ticketid + 1 as start,
(select min(Ticketid) - 1 from sequence as x where x.Ticketid > m.Ticketid) as stop
from sequence as m
left outer join sequence as r on m.Ticketid = r.Ticketid - 1
where r.Ticketid is null
) as x
where stop is not null;


Would this be possible?

Thanks for the help!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-03 : 09:58:43
I didn't quite follow the logic you are trying to implement in your query, but would something like this work for you?
SELECT
a.*,b.TicketId NextTicketId,
CAST(b.TicketID AS INT) - CAST(a.TicketID AS INT)-1 AS MissingTicketCount
FROM
sequence a
INNER JOIN sequence b ON a.Id+1 = b.ID
WHERE
CAST(a.TicketId AS INT) + 1 <> CAST(b.TicketId AS INT)
AND CAST(b.TicketID AS INT) <> 1;
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-05-03 : 09:59:04
1. The rollover always start with new value of 1?
2. The rollover always end with old value of 10?



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

Coolda
Starting Member

10 Posts

Posted - 2012-05-03 : 10:05:32
SwePeso: Yes you are correct. TicketID starts with a value of 1 then when it hits 10 it goes back to 1 but the ID field continues to count in sequential order.

sunitabeck: Thanks! I believe this is working. Much appreciated!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-05-03 : 10:25:52
[code]DECLARE @Sample TABLE
(
ID INT NOT NULL,
TicketID INT NOT NULL
)

INSERT @Sample
VALUES ( 1, 1),
( 2, 2),
( 3, 3),
( 4, 5),
( 5, 6),
( 6, 7),
( 7, 9),
( 8, 10),
( 9, 1),
(10, 2),
(11, 3),
(12, 5),
(13, 6),
(14, 7),
(15, 8),
(16, 9),
(17, 10)

-- SwePeso
;WITH cteSource(TicketID, Items, theMax)
AS (
SELECT f.TicketID,
ISNULL(d.Items, 0) AS Items,
MAX(d.Items) OVER () AS theMax
FROM (
VALUES ( 1),
( 2),
( 3),
( 4),
( 5),
( 6),
( 7),
( 8),
( 9),
(10)
) AS f(TicketID)
LEFT JOIN (
SELECT TicketID,
COUNT(*) AS Items
FROM @Sample
GROUP BY TicketID
) AS d ON d.TicketID = f.TicketID
)
SELECT TicketID,
theMax - Items AS MissingTicketCount
FROM cteSource
WHERE Items < theMax

-- Sunitabeck
SELECT
a.*,b.TicketId NextTicketId,
CAST(b.TicketID AS INT) - CAST(a.TicketID AS INT)-1 AS MissingTicketCount
FROM
@sample a
INNER JOIN @sample b ON a.Id+1 = b.ID
WHERE
CAST(a.TicketId AS INT) + 1 <> CAST(b.TicketId AS INT)
AND CAST(b.TicketID AS INT) <> 1;[/code]


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

Coolda
Starting Member

10 Posts

Posted - 2012-05-03 : 10:41:33
quote:
Originally posted by SwePeso

DECLARE	@Sample TABLE
(
ID INT NOT NULL,
TicketID INT NOT NULL
)

INSERT @Sample
VALUES ( 1, 1),
( 2, 2),
( 3, 3),
( 4, 5),
( 5, 6),
( 6, 7),
( 7, 9),
( 8, 10),
( 9, 1),
(10, 2),
(11, 3),
(12, 5),
(13, 6),
(14, 7),
(15, 8),
(16, 9),
(17, 10)

-- SwePeso
;WITH cteSource(TicketID, Items, theMax)
AS (
SELECT f.TicketID,
ISNULL(d.Items, 0) AS Items,
MAX(d.Items) OVER () AS theMax
FROM (
VALUES ( 1),
( 2),
( 3),
( 4),
( 5),
( 6),
( 7),
( 8),
( 9),
(10)
) AS f(TicketID)
LEFT JOIN (
SELECT TicketID,
COUNT(*) AS Items
FROM @Sample
GROUP BY TicketID
) AS d ON d.TicketID = f.TicketID
)
SELECT TicketID,
theMax - Items AS MissingTicketCount
FROM cteSource
WHERE Items < theMax

-- Sunitabeck
SELECT
a.*,b.TicketId NextTicketId,
CAST(b.TicketID AS INT) - CAST(a.TicketID AS INT)-1 AS MissingTicketCount
FROM
@sample a
INNER JOIN @sample b ON a.Id+1 = b.ID
WHERE
CAST(a.TicketId AS INT) + 1 <> CAST(b.TicketId AS INT)
AND CAST(b.TicketID AS INT) <> 1;



N 56°04'39.26"
E 12°55'05.63"




Oh nice, this works as well. Thanks!!
Go to Top of Page

Coolda
Starting Member

10 Posts

Posted - 2012-05-07 : 10:54:33
Is it possible to display which TicketIDs are missing, not just a count?

Especially if the count is greater then 1.

Go to Top of Page
   

- Advertisement -