Need help to create a script to set article positions

• markuss21

  Old Hand

  Points: 340

  More actions

  June 21, 2006 at 2:18 pm

  #97180

  Hi, I have the following table:

  nr     pos       article   count

  1                   1001       2

  1                   1005       4

  1                  1000        1

  2                  1005        1

  2                 1004          3

  Now I need a script which I can use in a DTS package which set me the pos number like this:

   

  nr   pos    article         count

  1     1         1001           2

  1     2         1005           4

  1      3       1000            1

  2     1         1005            1

  ...

   

  Thanks in advance !

   

  Regards, Markus

• stax68

  SSChampion

  Points: 11711

  More actions

  June 21, 2006 at 2:38 pm

  #645148

  I can't see what ordering algorithm you want to use. Position in what sequence?

  Tim Wilkinson

  "If it doesn't work in practice, you're using the wrong theory"
  - Immanuel Kant

• markuss21

  Old Hand

  Points: 340

  More actions

  June 21, 2006 at 3:36 pm

  #645156

  oh, sorry. every articel have a date !

  have you a idea ?

• stax68

  SSChampion

  Points: 11711

  More actions

  June 21, 2006 at 4:16 pm

  #645160

  I'm not sure whether a stored proc would be approriate for your situation, but if so:

  --This is based on AdventureWorks but can easily be adapted.

  --A caveat:

  --strictly speaking, this depends on the update scrolling

  --through the clustered index from beginning to end.

  --I don't think MS officially guarantees that this will happen

  --but it always does happen

  --(it always has happened as far as I have been able to find out,

  --including in my own experience)

  --

  --unfortunately ORDER BY is not allowed for updates.

  --

  --probably a bit naughty

  --you could use use a...wait for it...cursor!!!!

   

  declare

  @ord int, @emp int, @lastemp int

   

  create table #ohnonotanothertemptable(

  rowid int identity(1,1) primary key clustered,

  EmployeeID int, RateChangeDate datetime, rate money, ord int null)

   

  insert

  #ohnonotanothertemptable(EmployeeID, RateChangeDate, rate)

  select

  e.EmployeeID, e.RateChangeDate, e.rate

  from

  HumanResources.EmployeePayHistory e

  order

  by e.EmployeeID, e.RateChangeDate

   

  update

  #ohnonotanothertemptable

  set

  @emp = EmployeeID

  ,

  @ord = case @emp when @lastemp then @ord + 1 else 1 end

  ,

  @lastemp = @emp

  ,

  ord = @ord

   

  select

  EmployeeID, RateChangeDate, ord, rate

  from

  #ohnonotanothertemptable

  go

  drop

  table #ohnonotanothertemptable

   

   

  Tim Wilkinson

  "If it doesn't work in practice, you're using the wrong theory"
  - Immanuel Kant

• markuss21

  Old Hand

  Points: 340

  More actions

  June 22, 2006 at 1:30 am

  #645195

  Thanks in advance. Now My table name is

  Article. Can you help me to adapt the code for me (table,columns) ? I can't it. sorry.

   

  Regards,

  Markus