Add Column - Count - Rows by Order Number See Ex.

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 2000 Forums

SQL Server Development (2000)

Add Column - Count - Rows by Order Number See Ex.

Author

Topic

besadmin
Posting Yak Master

116 Posts

Posted - 2008-12-11 : 16:20:24

ok here are the examples of data. i will exapline as it goes along. we really appreciate any help and responses! thanks so much ahead of time!

we have this table:

tblOrders			
oOrderNo	oAcctID	oDate	…
A20011	Ac123	12/11/2008	…
B20012	F1212	12/11/2008	…
C20013	1Q22Q	12/12/2008	…
D20014	12375	12/12/2008	…
E20015	45788	12/12/2008	…
…	…	…	…
…	…	…	…

and this table


tblOrderLines		
olOrderNo	olItem	olQty
A20011	3340744	1
A20011	3340745	3
A20011	3340717	2
A20011	3040515	1
B20012	2000011	5
C20013	3340738	1
C20013	1140224	1
C20013	2000011	1
D20014	5556877	10
D20014	3340744	2
D20014	3340717	2
D20014	1594562	3
D20014	3340745	2

we use this query


select olOrderNo, olItem, olQty, oAcct, oDate, …				
from tblOrderLines				
join tblOrders				
on olOrderNo = oOrderNo

and get these results


olOrderNo	olItem	olQty	oAcct	oDate
A20011	3340744	1	Ac123	12/11/2008
A20011	3340745	3	Ac123	12/11/2008
A20011	3340717	2	Ac123	12/11/2008
A20011	3040515	1	Ac123	12/11/2008
B20012	2000011	5	F1212	12/11/2008
C20013	3340738	1	1Q22Q	12/12/2008
C20013	1140224	1	1Q22Q	12/12/2008
C20013	2000011	1	1Q22Q	12/12/2008
D20014	5556877	10	12375	12/12/2008
D20014	3340744	2	12375	12/12/2008
D20014	3340717	2	12375	12/12/2008
D20014	1594562	3	12375	12/12/2008
D20014	3340745	2	12375	12/12/2008

What we want to do is add some sort of count in their to give us results like the following. Where it adds a column called 'Orderlinenumber' where it will count the rows like 1, 2, 3... for each order number, and thene start over for the next order number and so on...se below. Like this:


I want to add an OrderLineNumber (OLN) column such that the desired result yields						
						
olOrderNoOLN	olItem	olQty	oAcct	oDate	
A20011	1	3340744	1	Ac123	12/11/2008	…
A20011	2	3340745	3	Ac123	12/11/2008	…
A20011	3	3340717	2	Ac123	12/11/2008	…
A20011	4	3040515	1	Ac123	12/11/2008	…
B20012	1	2000011	5	F1212	12/11/2008	…
C20013	1	3340738	1	1Q22Q	12/12/2008	…
C20013	2	1140224	1	1Q22Q	12/12/2008	…
C20013	3	2000011	1	1Q22Q	12/12/2008	…
D20014	1	5556877	10	12375	12/12/2008	…
D20014	2	3340744	2	12375	12/12/2008	…
D20014	3	3340717	2	12375	12/12/2008	…
D20014	4	1594562	3	12375	12/12/2008	…
D20014	5	3340745	2	12375	12/12/2008	…

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-11 : 21:02:00

quote:

Originally posted by besadmin

ok here are the examples of data. i will exapline as it goes along. we really appreciate any help and responses! thanks so much ahead of time!

we have this table:

tblOrders			
oOrderNo	oAcctID	oDate	…
A20011	Ac123	12/11/2008	…
B20012	F1212	12/11/2008	…
C20013	1Q22Q	12/12/2008	…
D20014	12375	12/12/2008	…
E20015	45788	12/12/2008	…
…	…	…	…
…	…	…	…

and this table


tblOrderLines		
olOrderNo	olItem	olQty
A20011	3340744	1
A20011	3340745	3
A20011	3340717	2
A20011	3040515	1
B20012	2000011	5
C20013	3340738	1
C20013	1140224	1
C20013	2000011	1
D20014	5556877	10
D20014	3340744	2
D20014	3340717	2
D20014	1594562	3
D20014	3340745	2

we use this query


select s.olOrderNo, olItem, olQty, oAcctID, oDate,OLN=(select count(*) from tblOrderLines ss where ss.olOrderNo = s.olOrderNo and ss.olItem <= s.olItem )		
from tblOrders	t	
inner join tblOrderLines s		
on t.oOrderNo = s.olOrderNo 
order by s.olOrderNo, olItem, olQty, oAcctID, oDate

and get these results


olOrderNo	olItem	olQty	oAcct	oDate
A20011	3340744	1	Ac123	12/11/2008
A20011	3340745	3	Ac123	12/11/2008
A20011	3340717	2	Ac123	12/11/2008
A20011	3040515	1	Ac123	12/11/2008
B20012	2000011	5	F1212	12/11/2008
C20013	3340738	1	1Q22Q	12/12/2008
C20013	1140224	1	1Q22Q	12/12/2008
C20013	2000011	1	1Q22Q	12/12/2008
D20014	5556877	10	12375	12/12/2008
D20014	3340744	2	12375	12/12/2008
D20014	3340717	2	12375	12/12/2008
D20014	1594562	3	12375	12/12/2008
D20014	3340745	2	12375	12/12/2008


I want to add an OrderLineNumber (OLN) column such that the desired result yields						
						
olOrderNoOLN	olItem	olQty	oAcct	oDate	
A20011	1	3340744	1	Ac123	12/11/2008	…
A20011	2	3340745	3	Ac123	12/11/2008	…
A20011	3	3340717	2	Ac123	12/11/2008	…
A20011	4	3040515	1	Ac123	12/11/2008	…
B20012	1	2000011	5	F1212	12/11/2008	…
C20013	1	3340738	1	1Q22Q	12/12/2008	…
C20013	2	1140224	1	1Q22Q	12/12/2008	…
C20013	3	2000011	1	1Q22Q	12/12/2008	…
D20014	1	5556877	10	12375	12/12/2008	…
D20014	2	3340744	2	12375	12/12/2008	…
D20014	3	3340717	2	12375	12/12/2008	…
D20014	4	1594562	3	12375	12/12/2008	…
D20014	5	3340745	2	12375	12/12/2008	…

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources