September 3, 2009 at 3:03 am
Hi
I have a table in my database, which contains ID's these are numerical and represent a shop. I also have receipt numbers which are unique to each ID (ie. 100 could be there twice but it will only be there once for each ID)
I need to create a script which will look through each receipt number for each ID and find receipt numbers that are missing.
At the moment I am just displaying the ID's and receipt number and ordering by ID then receipt and manually reading through the lists to find the missing receipts - which is very painstaking and is giving me headaches... Can SQL do this job for me?
Any help is really appreciated
Thanks
Craig
September 3, 2009 at 3:50 am
If you just need something 'quick and dirty' this should do.
create table #table
(
id integer,
receiptnumber integer
)
go
insert into #table values(1,1)
insert into #table values(1,2)
insert into #table values(1,3)
insert into #table values(1,5)
insert into #table values(1,6)
insert into #table values(1,10)
insert into #table values(1,11)
go
Select id,Receiptnumber+1
from #table
except
Select id,Receiptnumber
from #table
September 3, 2009 at 3:57 am
Thanks for the reply, I'm not great (actually not even good with SQL) can you explain this to me please?
Say I have 3 different ID's
1001
1002
1003
1001 has receipt numbers 1, 2, 3, 4, 5, 7, 8, 9,
1002 has receipt numbers 1, 3, 4, 5, 6, 7, 8, 9,
1003 has receipt numbers 100, 101, 102, 103,105
how would I structure the query to find these
Thanks
Craig
September 3, 2009 at 4:07 am
Actually no need to explain it, this is perfect
Thank you very much
September 3, 2009 at 4:07 am
It looks like you need to do a join on the tables, though it is hard to tell with limited information.
Could you post the table structures, sample data and what the expected result should be?
this will make it easier for people to help
September 3, 2009 at 4:12 am
Exactly as above
create table #table
(
id integer,
receiptnumber integer
)
go
delete from #table
go
insert into #table values(1001,1)
insert into #table values(1001,2)
insert into #table values(1001,3)
insert into #table values(1001,4)
insert into #table values(1001,6)
insert into #table values(1001,7)
insert into #table values(1001,8)
insert into #table values(1001,9)
insert into #table values(1002,1)
insert into #table values(1002,3)
insert into #table values(1002,4)
insert into #table values(1002,5)
insert into #table values(1002,6)
insert into #table values(1002,7)
insert into #table values(1002,8)
insert into #table values(1002,9)
insert into #table values(1003,100)
insert into #table values(1003,101)
insert into #table values(1003,102)
insert into #table values(1003,103)
insert into #table values(1003,104)
insert into #table values(1003,105)
go
Select id,Receiptnumber+1
from #table
except
Select id,Receiptnumber
from #table
returns
id
----------- -----------
1001 5
1001 10
1002 2
1002 10
1003 106
Note that this will return potentially false positives for end of range..
Also it will not detect if the initial receipts are missing.
Do you know the expected ranges to help solve these issues ?
September 3, 2009 at 9:07 am
Dave Ballantyne (9/3/2009)
Note that this will return potentially false positives for end of range..Also it will not detect if the initial receipts are missing.
Do you know the expected ranges to help solve these issues ?
It also will not find consecutive missing receipts. I've come up with a solution that finds all missing receipts within the range from the minimum for each ID and the maximum for each ID. (I've assumed that those define the possible range.)
With ReceiptRanks AS (
SELECT
[ID]
, receiptnumber
, Row_Number() OVER (PARTITION BY [ID] ORDER BY receiptnumber ) as RowNumAsc
, Row_Number() OVER (PARTITION BY [ID] ORDER BY receiptnumber DESC) as RowNumDesc
, Row_Number() OVER ( ORDER BY receiptnumber) - 1 as RowNumOverall
FROM #table
)
, ReceiptRanges AS (
SELECT [ID], Min(ReceiptNumber) AS RMin, Max(ReceiptNumber) AS RMax
FROM ReceiptRanks
GROUP BY [ID]
)
, FullRange AS (
SELECT ReceiptRanges.[ID], RMin + RowNumOverall AS ReceiptNumber
FROM ReceiptRanks
INNER JOIN ReceiptRanges
ON RowNumOverall <= RMax - RMin
)
SELECT FullRange.[ID], FullRange.ReceiptNumber
FROM FullRange
LEFT OUTER JOIN ReceiptRanks
ON FullRange.[ID] = ReceiptRanks.[ID]
AND FullRange.ReceiptNumber = ReceiptRanks.ReceiptNumber
WHERE ReceiptRanks.[ID] IS Null
ORDER BY FullRange.[ID], FullRange.ReceiptNumber
It's longer than I thought it would be, and may not be the most efficient.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply