hi all
I am stuck with something that seems easy but im obviously clueless as how to execute the idea.
I have a custom table that houses invoices on the details level. So for example i have:
InvcNo
00000001
00000001
00000001
00000002
00000002
00000003
and so forth
What I am wanting to do in another column is keep track of the sequence number for each distinct invoice like:
SeqNo
1
2
3
1
2
1
I am working in a stored proc and i cant get past adding the numbers up at each line as a whole and not reseting when the next invoice number is present. Any help would be so greatly appreciated.
ThanksWill this help?
http://www.sqlservercentral.com/columnists/mcoles/sequentialordering.asp|||Thanks...but I dont have 2005 (
Is it possible to pull the example that is given in 2005 section in a prior version of SQL?|||Can you uniquely identify each record for a specific InvcNo?|||Yes, I have an InvcKey column set up|||create table #t1 (InvcKey int, InvcNo int)
insert into #t1 select
1,1 union all select
2,1 union all select
3,1 union all select
4,2 union all select
5,2 union all select
6,3
select count(*) 'SeqNo', a.InvcKey, a.InvcNo from #t1 a, #t1 b
where a.InvcNo=b.InvcNo
and a.InvcKey>=b.InvcKey
group by a.InvcKey, a.InvcNo
drop table #t1|||Ok, Ive got it...I should have played around some before asking further
SELECT COUNT(*) AS [SeqNo], c.InvcNo, c.InvcKey
FROM table c, table d
WHERE d.InvcKey <= c.InvcKey AND d.InvcNo = cInvcNo
GROUP BY c.InvcNo, c.InvcKey
ORDER BY c.InvcKey
Thanks for the help!
tibor|||You should probably use a JOIN.
Using
FROM table1, table2, ... , tableN
Is not advised.|||Being a self join, would I really need JOIN?|||Being a self join, would I really need JOIN?It is semantics. The affect it the same but the style you used is considered old fashioned in many quarters. Whether or not it is a self join is irrelevent.
Rather excitingly - you will have created a Theta Join when you have finished :)|||Rather excitingly - you will have created a Theta Join when you have finished :)
neat! normally you'd have to pay $360k to get to that level (http://en.wikipedia.org/wiki/Operating_Thetan)!
Saturday, February 25, 2012
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment