unresolved sequence - 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

unresolved sequence

Author

Topic

ben_53
Yak Posting Veteran

67 Posts

Posted - 2011-08-24 : 15:41:41

posing again for the second time with a new logic.

#seq with 5 columns.
col1 = name(6values) col2 = action(2 values) col3= batchid (x values) col4 = refnum(3 values) col5 = sequence

LOGIC:
now i added col6 and assign values 1-12 (col1*col2) so that I can assign sequence according to business logic.
lets say col1 values = A B C D E F and col2 = X & Y

so
A X goes first
A Y goes second
C Y goes third
E Y goes next and so on...
I called this new col = 'refcol'

sequence:
First look for name = 'A'

action = 'X' then sort according to batchid with possible values of 'batch01'...'batch0n' then by refnum with possible values of 1,2,3
then for action = 'Y' then batchID then ref num.

After name 'A' is sorted then logic should sequence according to batchid in increasing order.
then should look for col6 coz that takes cares of col1 & col2 and then it should look for refnum.

Its confusing but the sample result set might clear it up a little bit:
expected result:
name action batch refnum seq refcol
A X 01 1 1 1
A X 03 1 2 1
A Y 06 2 3 2
-------------------------------------
C Y 01 1 4 3
................

notice after A is sorted, it should look for batchID then for refco.

my code:

UPDATE #seq
SET Sequence = NewSeq

FROM
( SELECT
Sequence,

NewSeq =

ROW_NUMBER () over(PARTITION by id order by refcol,batchID,RefNum )

FROM #Seq )
#Seq

now this seq is sorting A correctly but after A is sorted, I want to add other logic to look for batchID>refcol>refNum.

please help !

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-08-25 : 06:56:01

Not sure if this is what you mean.



create table #yak ([Name] varchar(10), [action] varchar(10), batch varchar(2), refnum int, seq int, refcol int)

insert into #yak ([Name],[action],batch,refnum,seq,refcol)
values 
	('C','Y','01',1,12,3)
	,('A','Y','06',2,3,2)	
	,('A','X','01',1,55,1)
	,('A','X','03',1,0,1)


select * from #yak 

update
	 y
set 
	seq = x.Seq 
from
	#yak y
INNER JOIN
	(select 
		Name,action,batch,refnum,refcol ,ROW_NUMBER()over(order by	Name,action,batch,refnum,refcol ) as Seq
	from
		#yak 	
	) x
ON
	y.Name = x.Name 
AND
	y.action = x.action
AND
	y.batch = x.batch
AND
	y.refcol = x.refcol
AND
	y.refnum = x.refnum 
	
select * from #yak 
order by seq 

drop table #yak

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources