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 TicketID1 000012 000023 000034 000055 000066 000077 000098 000109 0000110 0000211 0000312 0000413 0000514 0000615 0000716 0000817 0000918 00010My 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 xwhere 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 MissingTicketCountFROM sequence a INNER JOIN sequence b ON a.Id+1 = b.IDWHERE CAST(a.TicketId AS INT) + 1 <> CAST(b.TicketId AS INT) AND CAST(b.TicketID AS INT) <> 1; |
 |
|
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" |
 |
|
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! |
 |
|
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 @SampleVALUES ( 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 MissingTicketCountFROM cteSourceWHERE Items < theMax-- SunitabeckSELECT a.*,b.TicketId NextTicketId, CAST(b.TicketID AS INT) - CAST(a.TicketID AS INT)-1 AS MissingTicketCountFROM @sample a INNER JOIN @sample b ON a.Id+1 = b.IDWHERE 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" |
 |
|
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 @SampleVALUES ( 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 MissingTicketCountFROM cteSourceWHERE Items < theMax-- SunitabeckSELECT a.*,b.TicketId NextTicketId, CAST(b.TicketID AS INT) - CAST(a.TicketID AS INT)-1 AS MissingTicketCountFROM @sample a INNER JOIN @sample b ON a.Id+1 = b.IDWHERE 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!! |
 |
|
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. |
 |
|
|
|
|