December 29, 2010 at 12:26 am
Hello everyone,
I have a table like below. Invoice_Code is auto-increment, but sometimes due to transaction or system error, Invoice_Code is not generated correctly. For example: record - record[i-1] != 1.
Here's my query to show out the results:
DECLARE @tblTemp TABLE
(
Patient_code int,
Patient_name varchar(10),
Invoice_code int,
Date_Visit date,
Date_Created date
);
INSERT @tblTemp
SELECT 20, 'David', 2, '2010-11-15', '2010-12-15' UNION ALL
SELECT 21, 'Anna', 3, '2010-12-15', '2010-12-15' UNION ALL
SELECT 21, 'Anna', 4, '2010-12-15', '2010-12-15' UNION ALL
SELECT 21, 'Anna', 5, '2010-12-15', '2010-12-16' UNION ALL
SELECT 22, 'Peter', 6, '2010-12-20', '2010-12-20' UNION ALL
SELECT 23, 'Ryan', 8, '2010-12-24', '2010-12-24' UNION ALL
SELECT 23, 'Ryan', 9, '2010-12-24', '2010-12-24' UNION ALL
SELECT 24, 'Tim', 10, '2010-12-29', '2010-12-29' UNION ALL
SELECT 25, 'Lee', 12, '2010-12-29', '2010-12-29' UNION ALL
SELECT 25, 'Mike', 13, '2010-12-29', '2010-12-29' UNION ALL
SELECT 26, 'Mike', 16, '2010-12-29', '2010-12-29';
SELECT Patient_Code,
Patient_Name,
Date_Created,
Date_Visit,
Invoice_Code,
[Result]=CASE WHEN EXISTS
(
SELECT *
FROM @tblTemp subTmp
WHERE (Tmp.Invoice_Code-subTmp.Invoice_Code)=1
)
THEN 'RIGHT' ELSE 'WRONG'
END
FROM @tblTemp tmp
But you can see, with my way, the first record's always wrong.
(because Tmp.Invoice_Code[1] - subTmp.Invoice_Code[1] = 0)
Patient_Code Patient_Name Date_Created Date_Visit Invoice_Code Result
------------ ------------ ------------ ---------- ------------ ------
20 David 2010-12-15 2010-11-15 2 WRONG
21 Anna 2010-12-15 2010-12-15 3 RIGHT
21 Anna 2010-12-15 2010-12-15 4 RIGHT
21 Anna 2010-12-16 2010-12-15 5 RIGHT
22 Peter 2010-12-20 2010-12-20 6 RIGHT
23 Ryan 2010-12-24 2010-12-24 8 WRONG
23 Ryan 2010-12-24 2010-12-24 9 RIGHT
24 Tim 2010-12-29 2010-12-29 10 RIGHT
25 Lee 2010-12-29 2010-12-29 12 WRONG
25 Mike 2010-12-29 2010-12-29 13 RIGHT
26 Mike 2010-12-29 2010-12-29 16 WRONG
Otherwise, how can I create a new column that show the missed invoice number (the invoice number that should be generated). For example, in the final invoice code, it should be 14 instead of 16.
Plz help me!
December 29, 2010 at 3:39 am
I'm not really sure what your question is... What do you mean by "the first record's always wrong"? You have gaps within your sequence. The question is: what is the cause for it? You mention transaction or system errors. But how about a delete statement?
Do you log the transaction and system errors using TRY ... CATCH blocks so you know why those happen and you can take appropriate action? To find the cause of the missing invoice numbers should be step #1.
Other than that: if you need to kee the sequence you might want to search this site for "sequence gaps". There are several articles, threads and code samples covering this subject.
December 29, 2010 at 3:51 am
Try this
DECLARE @tblTemp TABLE
(
Patient_code int,
Patient_name varchar(10),
Invoice_code int,
Date_Visit datetime,
Date_Created datetime
);
INSERT @tblTemp
SELECT 20, 'David', 2, '2010-11-15', '2010-12-15' UNION ALL
SELECT 21, 'Anna', 3, '2010-12-15', '2010-12-15' UNION ALL
SELECT 21, 'Anna', 4, '2010-12-15', '2010-12-15' UNION ALL
SELECT 21, 'Anna', 5, '2010-12-15', '2010-12-16' UNION ALL
SELECT 22, 'Peter', 6, '2010-12-20', '2010-12-20' UNION ALL
SELECT 23, 'Ryan', 8, '2010-12-24', '2010-12-24' UNION ALL
SELECT 23, 'Ryan', 9, '2010-12-24', '2010-12-24' UNION ALL
SELECT 24, 'Tim', 10, '2010-12-29', '2010-12-29' UNION ALL
SELECT 25, 'Lee', 12, '2010-12-29', '2010-12-29' UNION ALL
SELECT 25, 'Mike', 13, '2010-12-29', '2010-12-29' UNION ALL
SELECT 26, 'Mike', 16, '2010-12-29', '2010-12-29';
with cnumbers as (
select 0 as a union all
select 1 as a union all
select 2 as a union all
select 3 as a union all
select 4 as a union all
select 5 as a union all
select 6 as a union all
select 7 as a union all
select 8 as a union all
select 9 as a
)
select seqno as Invoice_code
from
(select hundthous.a*100000+tenthous.a*10000+thous.a*1000+hunds.a*100+tens.a*10+ones.a seqno
from cnumbers ones,
cnumbers tens,
cnumbers hunds,
cnumbers thous,
cnumbers tenthous,
cnumbers hundthous where hundthous.a*100000+tenthous.a*10000+thous.a*1000+hunds.a*100+tens.a*10+ones.a >= (select min(Invoice_Code) FROM @tblTemp) and
hundthous.a*100000+tenthous.a*10000+thous.a*1000+hunds.a*100+tens.a*10+ones.a <= (select max(Invoice_Code) FROM @tblTemp)) as SeqNumbers left outer join @tblTemp
on seqno = Invoice_code
where Invoice_code is null
December 29, 2010 at 3:52 am
The above query will help you to findout missing invoice code.
December 30, 2010 at 2:12 am
Thank LutzM, your keyword "sequence gaps" is really useful for me.
But how can I have the result like this:
Invoice_Code | Missing_Code
1 NULL
2 NULL
4 3
5 NULL
8 6
...
It can be written in C like this:
for (int i=0; i < Invoice_Code.length; i++)
{
if (Invoice_Code - Invoice_Code[i-1] != 1)
{
Missing_Code = Invoice_Code[i-1] + 1;
}
else
Missing_Code = 'NULL';
}
PS: thank Harin also, your solution shows the exact result and I'm reading it 🙂
December 30, 2010 at 3:02 am
Ok, based on harinerella great setup for the sample data here's my approach (based on ROW_NUMBER to get numbered list without gaps and a self join to that list):
WITH cte AS
(
SELECT *, ROW_NUMBER() OVER(ORDER BY Invoice_code) AS ROW
FROM @tblTemp
)
SELECT
c1.*,
CASE WHEN c1.Invoice_code-c2.Invoice_code = 1 THEN NULL ELSE c2.Invoice_code + 1 END AS missing
FROM cte c1
LEFT OUTER JOIN cte c2 ON c1.row=c2.row+1;
There are three differences between harinerellas solution and the one I used:
1) harinerellas solution will find all the missing numbers, wherreas mine will just assign the next missing number (same like your C code)
2) in my solution the missing numbers are assigned to the row where the gap occured instead of a plain list and
3) Performance. Harinerella uses a triangular join which most probably will kill performance on a larger data volumne. Just run both and compare... 😉
January 3, 2011 at 10:33 am
First, I wish Lutz, Harin and other members a new year full of health and joy 🙂
Thank Lutz again, with your solution, now I know how to refer to other row's value (before your post, I'm new to row_number function). In fact, of course this solution can't be the perfect answer for which missing codes.
Harin's solution uses a triangular join is a matter with large database. But with me, before thinking about that, I really dont understand the code. Can Harin (or Lutz) make more comments or a detailed breakdown?
What cnumbers used for and the meaning of alias names such as ones, tens, ..., hundthous?
Thanks sincerely!
January 3, 2011 at 11:23 am
hoanglong88i (1/3/2011)
First, I wish Lutz, Harin and other members a new year full of health and joy 🙂Thank Lutz again, with your solution, now I know how to reference to other row's value (before your post, I'm new to row_number function). In fact, of course this solution can't be the perfect answer for which missing codes.
Harin's solution uses a triangular join is a matter with large database. But with me, before thinking about that, I really dont understand the code. Can Harin (or Lutz) make more comments or a detailed breakdown?
What cnumbers used for and the meaning of alias names such as ones, tens, ..., hundthous?
Thanks sincerely!
A Happy New Year to you,too!!!
Regarding your "can't be the perfect answer": How would a perfect solution look like from your point of view? What would be your perfect result?
January 3, 2011 at 8:34 pm
In my view, the perfect answer here is based on most requests that want to know all the missing codes. Like Harin's solution or this article:
http://www.sqlservercentral.com/articles/SQL+Puzzles/findinggapsinasequentialnumbersequence/2336/
But Lutz, your solution is perfect based on my C code because at first, I just thought about finding out where the gap occured and the missing code which that row should be.
However, the gap sometimes is not 1, and if someone wants a list of all missing codes, so I think about Harin's solution.
January 4, 2011 at 12:15 am
Ouch, I think I understand something from Harin's code. He lists all the number available from 1 to 999999, then puts them in a range between min and max Invoice code and uses left outer join to find out which missing codes, right?
January 4, 2011 at 4:14 am
Yes, You got it right.
January 4, 2011 at 6:10 am
hoanglong88i (1/3/2011)
In my view, the perfect answer here is based on most requests that want to know all the missing codes. Like Harin's solution or this article:http://www.sqlservercentral.com/articles/SQL+Puzzles/findinggapsinasequentialnumbersequence/2336/
But Lutz, your solution is perfect based on my C code because at first, I just thought about finding out where the gap occured and the missing code which that row should be.
However, the gap sometimes is not 1, and if someone wants a list of all missing codes, so I think about Harin's solution.
Maybe the following hybrid solution will do the trick.
Please note that I used a slightly different approach to build a list of numbers. Just run the code inside the pseudo_tally subquery separately and without the WHERE clause to see what it does.
You might also want to have a look at the TallyTable article referenced in my signature about what a tally table is, how to create it and whre it can be of a big help.
Furthermore I used the CROSS APPLY to mach the missing numbers to each row. There are two great articles[/url] by Paul White I strongly recommend to read if you want to learn more about it and how to use.
And finally, here's the code:
WITH cte AS
(
SELECT *, ROW_NUMBER() OVER(ORDER BY Invoice_code) AS ROW
FROM @tblTemp
)
SELECT
c1.*,
c2.Invoice_code+number+1 AS missing
FROM cte c1
LEFT OUTER JOIN cte c2 ON c1.row=c2.row+1 AND c1.Invoice_code<>c2.Invoice_code +1
CROSS APPLY
(
SELECT number
FROM master..spt_values
WHERE TYPE='P'
AND number < c1.Invoice_code -c2.Invoice_code-1
) pseudo_tally
ORDER BY Invoice_code
;
January 6, 2011 at 1:58 am
Oh, Lutz, you're master. So many things to learn, I'm taking time on them. By the way, how to get the exact time when executing a query (to compare the performance) ?
January 6, 2011 at 2:04 am
hoanglong88i (1/6/2011)
Oh, Lutz, you're master. So many things to learn, I'm taking time on them. By the way, how to get the exact time when executing a query (to compare the performance) ?
Three options: either use SET STATISTICS TIME ON before running the two queries and SET STATISTICS TIME OFF afterwards or assign getdate() to a variable before the queries and SELECT DATEDIFF(ms,yourvariable,getdate()) and reassign getdate() to your variable between the two queries. Or start a profiler trace...
January 6, 2011 at 2:48 am
OMG, sorry for this silly question, I just ran small database, so all results returned 00:00:00.
Using datediff is easy.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply