SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Identify missing rows using sequential number		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Bex
Aged Yak Warrior

United Kingdom
578 Posts
Posted - 09/17/2012 :  05:45:39  Show Profile  Send Bex an AOL message  Reply with Quote
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

United Kingdom
13 Posts
Posted - 09/17/2012 :  06:57:23  Show Profile  Reply with Quote
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
Go to Top of Page

Bex
Aged Yak Warrior

United Kingdom
578 Posts
Posted - 09/17/2012 :  07:32:40  Show Profile  Send Bex an AOL message  Reply with Quote
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
Go to Top of Page

Andy Hyslop
Starting Member

United Kingdom
13 Posts
Posted - 09/18/2012 :  04:16:05  Show Profile  Reply with Quote
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
16746 Posts
Posted - 09/18/2012 :  05:34:33  Show Profile  Reply with Quote
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
Time is always against us
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
16746 Posts
Posted - 09/18/2012 :  06:17:53  Show Profile  Reply with Quote
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
Time is always against us
Edited by - khtan on 09/18/2012 06:19:59
Go to Top of Page

Andy Hyslop
Starting Member

United Kingdom
13 Posts
Posted - 09/18/2012 :  08:24:16  Show Profile  Reply with Quote
Ignore, Tally table was too small!
Edited by - Andy Hyslop on 09/18/2012 08:25:50
Go to Top of Page

Bex
Aged Yak Warrior

United Kingdom
578 Posts
Posted - 09/18/2012 :  11:19:07  Show Profile  Send Bex an AOL message  Reply with Quote
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
16746 Posts
Posted - 09/18/2012 :  11:24:10  Show Profile  Reply with Quote
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
Time is always against us
Go to Top of Page

Bex
Aged Yak Warrior

United Kingdom
578 Posts
Posted - 09/20/2012 :  06:45:08  Show Profile  Send Bex an AOL message  Reply with Quote
Thank you both for your help. I was getting nowhere, but now I have my solution up and working! Much appreciated :)

Hearty head pats
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000