Subtracting a row from the next row

• notes4we

  SSCommitted

  Points: 1565

  More actions

  March 26, 2009 at 12:14 pm

  #966934

  Thank you for all your efforts. Next time I shall post my questions in a well organized manner, so that it becomes easier for the one who helps me by working on my question.

  Sorry about it and thank you for your reply.

  I am still verifying the data. I have too many rows and columns so I will be have to check everything minutely.

  Thanks for your help though!

• notes4we

  SSCommitted

  Points: 1565

  More actions

  March 26, 2009 at 12:38 pm

  #966958

  Lynn Pettis (3/26/2009)

  ---

  Changing methods a bit here, so here is some test code for you to play around with:

  create table #TestTab2 (

  Dept char(2),

  ID int,

  X int,

  Y int,

  Z int

  )

  ;

  with DeptID as

  (

  select

  row_number() over (partition by ID order by Dept desc) as RowNum,

  Dept,

  ID,

  X,

  Y,

  Z

  from

  #TestTab1

  )

  Can you please explain me where do you get with DeptID from, as there are two different columns Dept and ID and you have mentioned DeptID both together. I am getting an error here.

• notes4we

  SSCommitted

  Points: 1565

  More actions

  March 26, 2009 at 12:51 pm

  #966970

  I got it. It is the ID number only I guess, but still I do not see the data subtracting one row from the other. I am now making sure, if I have done everything as I was supposed to do!

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  March 26, 2009 at 1:01 pm

  #966975

  Compare the data in #TestTab1 to the data in #TestTab2. You will see that the subtraction is occurring.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  March 26, 2009 at 1:15 pm

  #966993

  notes4we (3/26/2009)

  ---

  Lynn Pettis (3/26/2009)

  ---

  Changing methods a bit here, so here is some test code for you to play around with:

  create table #TestTab2 (

  Dept char(2),

  ID int,

  X int,

  Y int,

  Z int

  )

  ;

  with DeptID as

  (

  select

  row_number() over (partition by ID order by Dept desc) as RowNum,

  Dept,

  ID,

  X,

  Y,

  Z

  from

  #TestTab1

  )

  Can you please explain me where do you get with DeptID from, as there are two different columns Dept and ID and you have mentioned DeptID both together. I am getting an error here.

  DeptID is the name of the CTE created using the WITH (with). You can think of it as a virtual table instantiated for the insert into #TestTab2.