Identify missing rows using sequential number

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)

Identify missing rows using sequential number

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2012-09-17 : 05:45:39

I have a table containing orders. Each Till generates a Ticket number for every order. I want to be able to identify if any of the orders are missing.

For example:

CREATE TABLE #Orders (TransactionDate DATETIME, StoreNo INT, TillNo INT, TicketNo INT)
GO
INSERT INTO #Orders (TransactionDate, StoreNo, TillNo, TicketNo)
SELECT CURRENT_TIMESTAMP, 1, 1, 1
UNION 
SELECT CURRENT_TIMESTAMP, 1, 1, 2
UNION 
SELECT CURRENT_TIMESTAMP, 1, 1, 3
UNION 
SELECT CURRENT_TIMESTAMP, 1, 1, 5
UNION 
SELECT CURRENT_TIMESTAMP, 1, 1, 6
UNION 
SELECT CURRENT_TIMESTAMP, 1, 1, 10
UNION 
SELECT CURRENT_TIMESTAMP, 1, 1, 11
UNION 
SELECT CURRENT_TIMESTAMP, 1, 1, 12
UNION 
SELECT CURRENT_TIMESTAMP, 1, 1, 14
UNION
SELECT CURRENT_TIMESTAMP, 1, 2, 45
UNION 
SELECT CURRENT_TIMESTAMP, 1, 2, 47
UNION 
SELECT CURRENT_TIMESTAMP, 1, 2, 48
UNION 
SELECT CURRENT_TIMESTAMP, 1, 2, 51
UNION 
SELECT CURRENT_TIMESTAMP, 1, 2, 54
UNION 
SELECT CURRENT_TIMESTAMP, 2, 1, 3456
UNION 
SELECT CURRENT_TIMESTAMP, 2, 1, 3470
UNION 
SELECT CURRENT_TIMESTAMP, 2, 1, 3472
GO

SELECT * FROM #Orders

In the above scenario, the results should look like this:


TransactionDate	        StoreNo	TillNo	TicketNo
2012-09-17 10:43:22.987	1	1	4
2012-09-17 10:43:22.987	1	1	7
2012-09-17 10:43:22.987	1	1	8
2012-09-17 10:43:22.987	1	1	9
2012-09-17 10:43:22.987	1	1	13
2012-09-17 10:43:22.987	1	2	46
2012-09-17 10:43:22.987	1	2	49
2012-09-17 10:43:22.987	1	2	50
2012-09-17 10:43:22.987	1	2	52
2012-09-17 10:43:22.987	1	2	53
2012-09-17 10:43:22.987	2	1	3457
2012-09-17 10:43:22.987	2	1	3458
2012-09-17 10:43:22.987	2	1	3459......... ETC

Thanks

Hearty head pats

Andy Hyslop
Starting Member

14 Posts

Posted - 2012-09-17 : 06:57:23

Hi

Does this help?


CREATE TABLE #Orders (TransactionDate DATETIME, StoreNo INT, TillNo INT, TicketNo INT)
GO
INSERT INTO #Orders (TransactionDate, StoreNo, TillNo, TicketNo)
SELECT CURRENT_TIMESTAMP, 1, 1, 1
UNION 
SELECT CURRENT_TIMESTAMP, 1, 1, 2
UNION 
SELECT CURRENT_TIMESTAMP, 1, 1, 3
UNION 
SELECT CURRENT_TIMESTAMP, 1, 1, 5
UNION 
SELECT CURRENT_TIMESTAMP, 1, 1, 6
UNION 
SELECT CURRENT_TIMESTAMP, 1, 1, 10
UNION 
SELECT CURRENT_TIMESTAMP, 1, 1, 11
UNION 
SELECT CURRENT_TIMESTAMP, 1, 1, 12
UNION 
SELECT CURRENT_TIMESTAMP, 1, 1, 14
UNION
SELECT CURRENT_TIMESTAMP, 1, 2, 45
UNION 
SELECT CURRENT_TIMESTAMP, 1, 2, 47
UNION 
SELECT CURRENT_TIMESTAMP, 1, 2, 48
UNION 
SELECT CURRENT_TIMESTAMP, 1, 2, 51
UNION 
SELECT CURRENT_TIMESTAMP, 1, 2, 54
UNION 
SELECT CURRENT_TIMESTAMP, 2, 1, 3456
UNION 
SELECT CURRENT_TIMESTAMP, 2, 1, 3470
UNION 
SELECT CURRENT_TIMESTAMP, 2, 1, 3472
GO


SELECT 
t1.col1 AS startOfGroup
, MIN(t2.col1) AS endOfGroup 
,TransactionDate
, StoreNo
,CA.TillNo



FROM 
(
SELECT 
col1 = TicketNo+1 

FROM 
#Orders tbl1
  
WHERE NOT EXISTS(SELECT * FROM #Orders tbl2 WHERE tbl2.TicketNo - tbl1.TicketNo = 1)
AND TicketNo <> (SELECT MAX(TicketNo) FROM #Orders)) t1
  
INNER JOIN (	SELECT 
				col1 = TicketNo-1 
				FROM #Orders tbl1
				WHERE NOT EXISTS(SELECT * FROM #Orders tbl2 WHERE tbl1.TicketNo - tbl2.TicketNo = 1)
				AND TicketNo <> (SELECT MIN(TicketNo) FROM #Orders)
			) t2 
			
ON t1.col1 <= t2.col1

CROSS APPLY	(	SELECT DISTINCT 
				TransactionDate
				, TillNo 
				, StoreNo
				
				FROM #Orders 
				WHERE TicketNo = t1.col1 - 1
			 ) CA
				
 GROUP BY 
 t1.col1
 ,CA.TillNo
 , StoreNo
 ,TransactionDate

  
  SELECT  *
  FROM    #Orders
  
  DROP TABLE #Orders

Andy

Bex
Aged Yak Warrior

580 Posts

Posted - 2012-09-17 : 07:32:40

Hi Andy

Thanks for the taking the time to develop that solution, I really appreciate it. That is definitely part way there - now I still need to generate a list detailing all those missing groups.

Also, something that I didn't mention in my previous post, this potentially will be querying across 500 stores, who each have multiple tills (the number I don't know), and have a few hundred transactions a day. The report will not be generated frequently, but the results have to be returned in a reasonable amount of time.

Thanks

Hearty head pats

Andy Hyslop
Starting Member

14 Posts

Posted - 2012-09-18 : 04:16:05

Hi

Apologies got caught up in something.

Hopefully this should give you what you need, you will need to create a Tally or Numbers table for the CROSS APPLY


CREATE TABLE #Orders (TransactionDate DATETIME, StoreNo INT, TillNo INT, TicketNo INT)
GO
INSERT INTO #Orders (TransactionDate, StoreNo, TillNo, TicketNo)
SELECT CURRENT_TIMESTAMP, 1, 1, 1
UNION 
SELECT CURRENT_TIMESTAMP, 1, 1, 2
UNION 
SELECT CURRENT_TIMESTAMP, 1, 1, 3
UNION 
SELECT CURRENT_TIMESTAMP, 1, 1, 5
UNION 
SELECT CURRENT_TIMESTAMP, 1, 1, 6
UNION 
SELECT CURRENT_TIMESTAMP, 1, 1, 10
UNION 
SELECT CURRENT_TIMESTAMP, 1, 1, 11
UNION 
SELECT CURRENT_TIMESTAMP, 1, 1, 12
UNION 
SELECT CURRENT_TIMESTAMP, 1, 1, 14
UNION
SELECT CURRENT_TIMESTAMP, 1, 2, 45
UNION 
SELECT CURRENT_TIMESTAMP, 1, 2, 47
UNION 
SELECT CURRENT_TIMESTAMP, 1, 2, 48
UNION 
SELECT CURRENT_TIMESTAMP, 1, 2, 51
UNION 
SELECT CURRENT_TIMESTAMP, 1, 2, 54
UNION 
SELECT CURRENT_TIMESTAMP, 2, 1, 3456
UNION 
SELECT CURRENT_TIMESTAMP, 2, 1, 3470
UNION 
SELECT CURRENT_TIMESTAMP, 2, 1, 3472
GO



WITH CTE
AS
(

SELECT 
t1.col1 AS startOfGroup
, MIN(t2.col1) AS endOfGroup 
,TransactionDate
, StoreNo
,CA.TillNo
,ROW_NUMBER() OVER ( ORDER BY t1.col1) STID
,ROW_NUMBER() OVER ( ORDER BY MIN(t2.col1)) ETID



FROM 
(
SELECT 
col1 = TicketNo+1 

FROM 
#Orders tbl1
  
WHERE NOT EXISTS(SELECT * FROM #Orders tbl2 WHERE tbl2.TicketNo - tbl1.TicketNo = 1)
AND TicketNo <> (SELECT MAX(TicketNo) FROM #Orders)) t1
  
INNER JOIN (	SELECT 
				col1 = TicketNo-1 
				FROM #Orders tbl1
				WHERE NOT EXISTS(SELECT * FROM #Orders tbl2 WHERE tbl1.TicketNo - tbl2.TicketNo = 1)
				AND TicketNo <> (SELECT MIN(TicketNo) FROM #Orders)
			) t2 
			
ON t1.col1 <= t2.col1

CROSS APPLY	(	SELECT DISTINCT 
				TransactionDate
				, TillNo 
				, StoreNo
				
				FROM #Orders 
				WHERE TicketNo = t1.col1 - 1
			 ) CA
				
 GROUP BY 
 t1.col1
 ,CA.TillNo
 , StoreNo
 ,TransactionDate

  
)

 
 SELECT  *
 FROM    CTE
 CROSS APPLY ( SELECT * FROM TALLY T WHERE CTE.STID = CTE.ETID AND T.NUMBER BETWEEN CTE.startOfGroup AND CTe.endOfGroup) AS C

Andy

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-09-18 : 05:34:33

Andy, you query assume the TicketNo runs continuously from Store 1 to 2. Your query returns Ticket ID from 55 to 3455 for Store 1, Till 2.

This might not be true. The TicketNo should runs by Store & Till.

Bex, do correct me if i am wrong here.

Also, in your sample data, you have used CURRENT_TIMESTAMP as the TransactionDate as such the sample data that you have generated all have the same date & time. And as a result, your expected result all have the same date & time. so for those missing TicketNo, what do you what to show as TransactionDate ?

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-09-18 : 06:17:53

Here is my take.

;WITH CTE AS
(
	SELECT	TransactionDate, StoreNo, TillNo, TicketNo, 
		row_no = row_number() over ( partition by StoreNo, TillNo order by TicketNo)
	FROm	#Orders
),
MISSING AS
(
	SELECT	c1.TransactionDate, c1.StoreNo, c1.TillNo, frTicket = c1.TicketNo + 1, toTicket = c2.TicketNo - 1
	FROM	CTE c1
		inner join CTE c2	on	c1.StoreNo	= c2.StoreNo
					and	c1.TillNo	= c2.TillNo
					and	c1.row_no	= c2.row_no - 1	
	WHERE	c1.TicketNo	<> c2.TicketNo - 1	
)
SELECT	m.TransactionDate, m.StoreNo, m.TillNo, TicketNo = t.NUMBER
FROM	MISSING m
	INNER JOIN TALLY t	on	t.NUMBER between m.frTicket AND m.toTicket
ORDER BY StoreNo, TillNo, TicketNo

the TransactionDate is based on the transaction before the start of the missing TicketNo. It does not really mean the missing TicketNo is that date or date & time. Especially in the case when the missing TicketNo is the last or first Ticket of the day.

KH
[spoiler]Time is always against us[/spoiler]

Andy Hyslop
Starting Member

14 Posts

Posted - 2012-09-18 : 08:24:16

Ignore, Tally table was too small!

Bex
Aged Yak Warrior

580 Posts

Posted - 2012-09-18 : 11:19:07

Hi Khtan

You were spot on in your assumption by the way, the ticket numbers for each Store/Till can vary greatly.

Thank you very much for your query - that is exactly what I am looking for. One question though, what is the TALLY reference to?

Thanks

Hearty head pats

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-09-18 : 11:24:10

TALLY is just a number table. It is just simply a single column table that contains the numbers

http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/you-require-a-numbers-table.aspx

for my query, i am assuming the number table is TALLY and the column is NUMBER

KH
[spoiler]Time is always against us[/spoiler]

Bex
Aged Yak Warrior

580 Posts

Posted - 2012-09-20 : 06:45:08

Thank you both for your help. I was getting nowhere, but now I have my solution up and working! Much appreciated :)

Hearty head pats

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources