new order - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

new order

Author

Topic

jeff06
Posting Yak Master

166 Posts

Posted - 2007-03-19 : 12:37:18

I hav sql query analyser 2002,I have a table
t1:
mechant order type
a 1 new
a 2 renewal
a 3 renewal
a 4 renewal
a 5 renewal
a 6 new
a 7 new
a 8 renewal
a 9 renewal
a 10 renewal
a 11 renewal
……
b 1 new
b 2 new
b 3 new
c 1 new
c 2 renewal
c 3 renewal
c 4 renewal
…
d 1 new
d 2 new
d 3 renewal
d 4 renewal
d 5 renewal

Based on t1, I want to add a new colunm [new order]

mechant order type new order
a 1 new 1
a 2 renewal 2
a 3 renewal 3
a 4 renewal 4
a 5 renewal 5
a 6 new 1
a 7 new 1
a 8 renewal 2
a 9 renewal 3
a 10 renewal 4
a 11 renewal 5
……
b 1 new 1
b 2 new 1
b 3 new 1
c 1 new 1
c 2 renewal 2
c 3 renewal 3
c 4 renewal 4
…
d 1 new 1
d 2 new 1
d 3 renewal 2
d 4 renewal 3
d 5 renewal 4

How can i acheive that?
Thank you very much.

Jeff

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-19 : 12:55:19

This is how you can do BOTH sequence numbering at once!
With reference to this topic http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80817[code] color="green">-- Stage the data
DECLARE @Stage TABLE (RecID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED, Merchant VARCHAR(1), OldOrder INT, OrderType VARCHAR(20), NewOrder INT)

INSERT @Stage (Merchant, OrderType)
SELECT Merchant,
ContractBr
FROM Table1
ORDER BY Merchant,
[Date]

-- Initialize ORDERING
DECLARE @PrevMerchant VARCHAR(1),
@PrevOrderType VARCHAR(20),
@OldOrder INT,
@NewOrder INT

SELECT @PrevMerchant = CHAR(9),
@PrevOrderType = CHAR(9)

UPDATE s
SET @NewOrder = NewOrder = CASE WHEN OrderType = @PrevOrderType THEN @NewOrder + 1 ELSE 1 END,
@PrevOrderType = OrderType,
@OldOrder = OldOrder = CASE WHEN Merchant = @PrevMerchant THEN @OldOrder + 1 ELSE 1 END,
@PrevMerchant = Merchant
FROM @Stage AS s

-- Show the expected output
SELECT Merchant,
OldOrder,
OrderType,
NewOrder
FROM @Stage
ORDER BY RecID
Peter Larsson
Helsingborg, Sweden

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-19 : 12:55:34

What part?

ALTER TABLE T1 ADD COLUMN New_Order int

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

jeff06
Posting Yak Master

166 Posts

Posted - 2007-03-19 : 14:08:19

Thank you very much for your help.
unfortunately, I do not have update rights to the database.
how to fulfill it with only select?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-19 : 14:10:36

You always have update right to table variables and temp tables.

Did you try the code? Which error did you get?
Or is this some kind of home work?

Peter Larsson
Helsingborg, Sweden

jeff06
Posting Yak Master

166 Posts

Posted - 2007-03-19 : 14:36:25

Thank you very much.
It almost works.
but the result is a little different from what I want
I want to start with 1 with every new contracts and if the new contract has subsequent renewals the sequence will be number 2,3,4....

Here is the result i get:

1 New 1 1
2 Renewal 1 1
3 Renewal 2 1
4 Renewal 3 1
5 Renewal 4 1
6 Renewal 5 1
7 Renewal 6 1
8 Renewal 7 1
9 Renewal 8 1
10 Renewal 9 1
11 Renewal 10 1
12 Renewal 11 1
13 Renewal 12 1
14 Renewal 13 1
15 Renewal 14 1
16 Renewal 15 1
17 Renewal 16 1
18 Renewal 17 1
19 Renewal 18 10
1 New 1 10
2 Renewal 1 10
3 Renewal 2 10
4 Renewal 3 10
5 Renewal 4 10
6 Renewal 5
1 New 1 1
2 Renewal 2 1
3 Renewal 3 1
4 Renewal 4 1
1 New 1 10
new 1
2 Renewal 2 10
3 Renewal 3 10
4 Renewal 4 10
5 Renewal 5 10
6 Renewal 6

jeff06
Posting Yak Master

166 Posts

Posted - 2007-03-19 : 14:39:58

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-19 : 14:45:34

Look at this code

-- Prepare sample data
DECLARE	@Sample TABLE (Merchant VARCHAR(1), OldOrder INT, OrderType VARCHAR(20))

INSERT	@Sample
SELECT	'a', 1, 'new' UNION ALL
SELECT	'a', 3, 'renewal' UNION ALL
SELECT	'a', 4, 'renewal' UNION ALL
SELECT	'a', 90, 'renewal' UNION ALL
SELECT	'a', 101, 'renewal' UNION ALL
SELECT	'a', 102, 'new' UNION ALL
SELECT	'a', 103, 'new' UNION ALL
SELECT	'a', 200, 'renewal' UNION ALL
SELECT	'a', 201, 'renewal' UNION ALL
SELECT	'a', 300, 'renewal' UNION ALL
SELECT	'a', 999, 'renewal' UNION ALL
SELECT	'b', 1, 'new' UNION ALL
SELECT	'b', 2, 'new' UNION ALL
SELECT	'b', 4, 'new' UNION ALL
SELECT	'c', 7, 'new' UNION ALL
SELECT	'c', 9, 'renewal' UNION ALL
SELECT	'c', 10, 'renewal' UNION ALL
SELECT	'c', 11, 'renewal' UNION ALL
SELECT	'd', -4, 'new' UNION ALL
SELECT	'd', 5, 'new' UNION ALL
SELECT	'd', 10, 'renewal' UNION ALL
SELECT	'd', 100, 'renewal' UNION ALL
SELECT	'd', 9999, 'renewal'

SELECT * FROM @Sample

-- Stage the data
DECLARE	@Stage TABLE (RecID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED, Merchant VARCHAR(1), OldOrder INT, OrderType VARCHAR(20), NewOrder INT)

INSERT		@Stage (Merchant, OldOrder, OrderType)
SELECT		Merchant,
		OldOrder,
		OrderType
FROM		@Sample
ORDER BY	Merchant,
		OldOrder

-- Initialize ORDERING
DECLARE @PrevMerchant VARCHAR(1),
	@PrevOrderType VARCHAR(20),
	@OldOrder INT,
	@NewOrder INT

SELECT	@PrevMerchant = CHAR(9),
	@PrevOrderType = CHAR(9)

UPDATE	s
SET	@NewOrder = NewOrder = CASE WHEN OrderType = @PrevOrderType THEN @NewOrder + 1 ELSE 1 END,
        @PrevOrderType = OrderType,
        @OldOrder = OldOrder = CASE WHEN Merchant = @PrevMerchant THEN @OldOrder + 1 ELSE 1 END,
        @PrevMerchant = Merchant
FROM	@Stage AS s

-- Show the expected output
SELECT		Merchant,
		OldOrder,
		OrderType,
		NewOrder
FROM		@Stage
ORDER BY	RecID

It produces the result you want.
If you still don't think so, PLEASE post some proper sample data as I have in this post.
Also post the expected output based on the posted sample data.

Peter Larsson
Helsingborg, Sweden

jeff06
Posting Yak Master

166 Posts

Posted - 2007-03-19 : 14:54:25

Thank you very much for your help, Peter.
The result from your sql code is:
Merchant OldOrder OrderType NewOrder
a 1 new 1
a 2 renewal 1
a 3 renewal 2
a 4 renewal 3
a 5 renewal 4
a 6 new 1
a 7 new 2
a 8 renewal 1
a 9 renewal 2
a 10 renewal 3
a 11 renewal 4
b 1 new 1
b 2 new 2
b 3 new 3
c 1 new 4
c 2 renewal 1
c 3 renewal 2
c 4 renewal 3
d 1 new 1
d 2 new 2
d 3 renewal 1
d 4 renewal 2
d 5 renewal 3

The result i want is:
Merchant OldOrder OrderType NewOrder
a 1 new 1
a 2 renewal 2
a 3 renewal 3
a 4 renewal 4
a 5 renewal 5
a 6 new 1
a 7 new 1
a 8 renewal 2
a 9 renewal 3
a 10 renewal 4
a 11 renewal 5
b 1 new 1
b 2 new 1
b 3 new 1
c 1 new 1
c 2 renewal 2
c 3 renewal 3
c 4 renewal 4
d 1 new 1
d 2 new 1
d 3 renewal 2
d 4 renewal 3
d 5 renewal 4

Is it possible to get this result?
Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-19 : 15:00:17

Here! This code fixes both numbering sequences for you.
You should really use this technique instead of the previous with SUBQUERY because it is very costly.

-- Prepare sample data
DECLARE	@Sample TABLE (Merchant VARCHAR(1), OldOrder INT, OrderType VARCHAR(20))

INSERT	@Sample
SELECT	'a', 1, 'new' UNION ALL
SELECT	'a', 3, 'renewal' UNION ALL
SELECT	'a', 4, 'renewal' UNION ALL
SELECT	'a', 90, 'renewal' UNION ALL
SELECT	'a', 101, 'renewal' UNION ALL
SELECT	'a', 102, 'new' UNION ALL
SELECT	'a', 103, 'new' UNION ALL
SELECT	'a', 200, 'renewal' UNION ALL
SELECT	'a', 201, 'renewal' UNION ALL
SELECT	'a', 300, 'renewal' UNION ALL
SELECT	'a', 999, 'renewal' UNION ALL
SELECT	'b', 1, 'new' UNION ALL
SELECT	'b', 2, 'new' UNION ALL
SELECT	'b', 4, 'new' UNION ALL
SELECT	'c', 7, 'new' UNION ALL
SELECT	'c', 9, 'renewal' UNION ALL
SELECT	'c', 10, 'renewal' UNION ALL
SELECT	'c', 11, 'renewal' UNION ALL
SELECT	'd', -4, 'new' UNION ALL
SELECT	'd', 5, 'new' UNION ALL
SELECT	'd', 10, 'renewal' UNION ALL
SELECT	'd', 100, 'renewal' UNION ALL
SELECT	'd', 9999, 'renewal'

SELECT * FROM @Sample

-- Stage the data
DECLARE	@Stage TABLE (RecID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED, Merchant VARCHAR(1), OldOrder INT, OrderType VARCHAR(20), NewOrder INT)

INSERT		@Stage (Merchant, OldOrder, OrderType)
SELECT		Merchant,
		OldOrder,
		OrderType
FROM		@Sample
ORDER BY	Merchant,
		OldOrder

-- Initialize ORDERING
DECLARE @PrevMerchant VARCHAR(1),
	@OldOrder INT,
	@NewOrder INT

SELECT	@PrevMerchant = CHAR(9)

UPDATE	s
SET	@NewOrder = NewOrder = CASE WHEN OrderType = 'new' THEN 1 ELSE @NewOrder + 1 END,
        @OldOrder = OldOrder = CASE WHEN Merchant = @PrevMerchant THEN @OldOrder + 1 ELSE 1 END,
        @PrevMerchant = Merchant
FROM	@Stage AS s

-- Show the expected output
SELECT		Merchant,
		OldOrder,
		OrderType,
		NewOrder
FROM		@Stage
ORDER BY	RecID

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-19 : 15:01:41

This is the code snippet you should concentrate on.

-- Stage the data
DECLARE	@Stage TABLE (RecID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED, Merchant VARCHAR(1), OldOrder INT, OrderType VARCHAR(20), NewOrder INT)

INSERT		@Stage (Merchant, OrderType)
SELECT		Merchant,
		ContractBr
FROM		Table1
ORDER BY	Merchant,
		[Date]

-- Initialize ORDERING
DECLARE @PrevMerchant VARCHAR(1),
	@OldOrder INT,
	@NewOrder INT

SELECT	@PrevMerchant = CHAR(9)

UPDATE	s
SET	@NewOrder = NewOrder = CASE WHEN OrderType = 'new' THEN 1 ELSE @NewOrder + 1 END,
        @OldOrder = OldOrder = CASE WHEN Merchant = @PrevMerchant THEN @OldOrder + 1 ELSE 1 END,
        @PrevMerchant = Merchant
FROM	@Stage AS s

-- Show the expected output
SELECT		Merchant,
		OldOrder,
		OrderType,
		NewOrder
FROM		@Stage
ORDER BY	RecID

Peter Larsson
Helsingborg, Sweden

jeff06
Posting Yak Master

166 Posts

Posted - 2007-03-19 : 15:03:31

I it works perfectly.
Thank you very much, Peter, It is magic. I will look into it carefully to see how it work.
I apprecicate your help.

Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources