Missing number in series between two entities

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 number in series between two entities

Author

Topic

Coolda
Starting Member

10 Posts

Posted - 2012-05-08 : 12:11:04

I have a list that displays two different DevIDs but I need to distinguish a missing ID for each seperate DevID. I need a query that could locate the missing entries in a sequence that rolls over at a certain point.

An Example of my dataset:
ID DevID TicketID Date
48292 157 5865 04/02/12
48293 157 5866 04/02/12
48294 157 5867 04/02/12
48295 157 5868 04/02/12
48296 157 5869 04/02/12
48297 157 5870 04/02/12
48298 157 5871 04/02/12
48299 1 8258 04/03/12
48300 1 8259 04/03/12
48301 1 8260 04/03/12
48302 1 8261 04/03/12
48303 1 8262 04/03/12
48304 1 8263 04/03/12
48305 1 8264 04/04/12
48306 1 8265 04/04/12
48307 1 8268 04/07/12
48308 1 8269 04/07/12
48309 1 8270 04/04/12
48310 157 5872 04/02/12
48311 157 5873 04/02/12
48312 157 5876 04/05/12
48313 157 5877 04/05/12
48314 157 5878 04/05/12
48315 157 5879 04/05/12
48316 157 5880 04/05/12

I need a query that displays the missing TicketIDs for each specific DevID.

The result set should be something like

DevID BeginDate EndDate BegTicket EndTicket
1 4/4/2012 4/7/2012 8265 8268

157 4/2/2012 4/5/2012 5873 5876

Any help would be much appreciated, Thanks!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-08 : 12:22:51

[code]
SELECT t1.DevID,
t2.[Date] AS BeginDate,
t1.[Date] AS EndDate,
t2.TicketID AS BeginTicket,
t1.TicketID AS EndTicket
FROM table t1
INNER JOIN table t2
ON t1.DevID = t2.DevID
AND t2.Date = (SELECT MAX(Date)
FROM table
WHERE DevID = t1.DevID
AND Date < t1.Date
)
WHERE t1.TicketID - t2.TicketID > 1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Coolda
Starting Member

10 Posts

Posted - 2012-05-08 : 12:52:25

quote:

Originally posted by visakh16


SELECT t1.DevID,
t2.[Date] AS BeginDate,
t1.[Date] AS EndDate,
t2.TicketID AS BeginTicket,
t1.TicketID AS EndTicket
FROM table t1
INNER JOIN table t2
ON t1.DevID = t2.DevID 
AND t2.Date = (SELECT MAX(Date)
               FROM table
               WHERE DevID = t1.DevID 
               AND Date < t1.Date
               )
WHERE t1.TicketID - t2.TicketID > 1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

When I try this it would give me a result like:

DevID BeginDate EndDate BegTicket EndTicket
1 4/4/2012 4/7/2012 8263 8268
1 4/4/2012 4/7/2012 8264 8268
1 4/4/2012 4/7/2012 8265 8268

157 4/2/2012 4/5/2012 5871 5876
157 4/2/2012 4/5/2012 5872 5876
157 4/2/2012 4/5/2012 5873 5876

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-05-08 : 14:00:50

[code]SELECT
DevID,
[Date] AS BeginDate,
NextDate AS EndDate,
TicketID AS BegTicket,
NextTicketID AS EndTicket
FROM
(
SELECT
*,
LEAD(TicketID, 1, 0) OVER (PARTITION BY DEVID ORDER BY TicketID) NextTicketID,
LEAD([Date], 1, '19000101') OVER (PARTITION BY DEVID ORDER BY TicketID) NextDate
FROM
TableName
) AS A
WHERE
(NextTicketID - TicketID) > 1
AND NextTicketID <> 0
[/code]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-05-08 : 14:05:29

Here is Another way using a cte:

;WITH cte AS
(
	SELECT *, ROW_NUMBER() OVER (PARTITION BY DEVID ORDER BY TicketID) AS RowNum
	FROM TableName
)

SELECT
	A.DevID,
	A.[Date] AS BeginDate,
	B.[Date] AS EndDate,
	A.TicketID AS BegTicket,
	B.TicketID AS EndTicket	
FROM 
	cte AS A
INNER JOIN
	cte AS B
	ON A.RowNum + 1 = B.RowNum
	AND A.DevID = B.DevID
	AND (B.TicketID - A.TicketID) > 1

Coolda
Starting Member

10 Posts

Posted - 2012-05-08 : 14:31:07

quote:

Originally posted by Lamprey

Here is Another way using a cte:

;WITH cte AS
(
	SELECT *, ROW_NUMBER() OVER (PARTITION BY DEVID ORDER BY TicketID) AS RowNum
	FROM TableName
)

SELECT
	A.DevID,
	A.[Date] AS BeginDate,
	B.[Date] AS EndDate,
	A.TicketID AS BegTicket,
	B.TicketID AS EndTicket	
FROM 
	cte AS A
INNER JOIN
	cte AS B
	ON A.RowNum + 1 = B.RowNum
	AND A.DevID = B.DevID
	AND (B.TicketID - A.TicketID) > 1

Thanks! This is working perfectly.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-05-08 : 14:45:24

Sure thing. You might want to note that the first method I posted is probably more effecient (possbly about 2x).

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-08 : 17:03:25

[code]One more way to do that

SELECT M.DEVID ,
M.DATE [BeginDate],
M1.date [EndDate],
M.TicketId [BegTicket],
M1.TicketId [EndTicket],
(M1.TicketId-M.TicketId)-1 AS MissingCount
FROM MISSING M INNER JOIN MISSING M1
ON M.ID+1=M1.ID
WHERE M.TICKETID+1 <>M1.TICKETID
AND M.DEVID = M1.DEVID[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-08 : 19:43:05

quote:
Originally posted by Coolda

quote:
Originally posted by visakh16

SELECT t1.DevID,
t2.[Date] AS BeginDate,
t1.[Date] AS EndDate,
t2.TicketID AS BeginTicket,
t1.TicketID AS EndTicket
FROM table t1
INNER JOIN table t2
ON t1.DevID = t2.DevID
AND t2.Date = (SELECT MAX(Date)
FROM table
WHERE DevID = t1.DevID
AND Date < t1.Date
)
WHERE t1.TicketID - t2.TicketID > 1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

When I try this it would give me a result like:

DevID BeginDate EndDate BegTicket EndTicket
1 4/4/2012 4/7/2012 8263 8268
1 4/4/2012 4/7/2012 8264 8268
1 4/4/2012 4/7/2012 8265 8268

157 4/2/2012 4/5/2012 5871 5876
157 4/2/2012 4/5/2012 5872 5876
157 4/2/2012 4/5/2012 5873 5876

ok didnt realise date values can repeat


SELECT t1.DevID,
t2.[Date] AS BeginDate,
t1.[Date] AS EndDate,
t2.TicketID AS BeginTicket,
t1.TicketID AS EndTicket
FROM table t1
INNER JOIN table t2
ON t1.DevID = t2.DevID 
AND t2.ID = (SELECT MAX(ID)
               FROM table
               WHERE DevID = t1.DevID 
               AND ID < t1.ID
               )
WHERE t1.TicketID - t2.TicketID > 1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Coolda
Starting Member

10 Posts

Posted - 2012-05-09 : 10:05:34

What if the number rolls over after TicketID hits 9999. It goes back to 1.

When I try to use the query it won't recognize 9999 or 0001 if they are the missing entry(ies).

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-05-09 : 12:30:52

You might be able to order by the date first then ticketid. You also might have to add some special casing to handle 9999 to 00001 is only 1 after 9999.

Coolda
Starting Member

10 Posts

Posted - 2012-05-10 : 09:54:28

Thanks for the help guys.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources