Don't Want to Change Consecutive Records

• ducksql@gmail.com

  SSCrazy

  Points: 2826

  More actions

  January 17, 2012 at 9:16 am

  #253616

  Using this table:

  --===== If the test table already exists, drop it

  IF OBJECT_ID('TempDB..#JobName','U') IS NOT NULL DROP TABLE #DateTest

  GO

  --===== Create the test table

  CREATE TABLE #JobSample

  ( jobNVARCHAR(20)

  , oper_numFLOAT

  , wcNVARCHAR(20)

  , move_hrsFLOAT

  )

  --===== Insert the test data into the test table

  INSERT INTO #JobSample (job, oper_num, wc, move_hrs)

  SELECT '1112VAS414', 5, 'Header', 12.00UNION ALL

  SELECT '1112VAS414', 10, 'FWHI', 12.00UNION ALL

  SELECT '1112VAS414', 20, 'MAGG', 12.00UNION ALL

  SELECT '1112VAS414', 30, 'PIP', 12.00UNION ALL

  SELECT '1112VAS414', 40, 'FINSP', 12.00UNION ALL

  SELECT '1112VAS414', 50, 'PIP', 12.00UNION ALL

  SELECT '1112VAS414', 60, 'PIP', 12.00UNION ALL

  SELECT '1112VAS414', 70, 'PIP', 12.00UNION ALL

  SELECT '1112VAS414', 80, 'FINSP', 12.00UNION ALL

  SELECT '1112VAS414', 90, 'FWHR', 12.00UNION ALL

  SELECT '1112VAS415', 5, 'Header', 12.00UNION ALL

  SELECT '1112VAS415', 10, 'FWHI', 12.00UNION ALL

  SELECT '1112VAS415', 15, 'FID', 12.00UNION ALL

  SELECT '1112VAS415', 18, 'FID', 12.00UNION ALL

  SELECT '1112VAS415', 20, 'VAS', 12.00UNION ALL

  SELECT '1112VAS415', 30, 'VAS', 12.00UNION ALL

  SELECT '1112VAS415', 40, 'FID', 12.00UNION ALL

  SELECT '1112VAS415', 50, 'FINSP', 12.00UNION ALL

  SELECT '1112VAS415', 60, 'VAS', 12.00

  --==== SELECT the records

  SELECT * FROM #JobSample

  I want to change all move_hrs to 4, EXCEPT when the the values in the wc field are consecutive, then the move_hrs should be 0. Here is the expected result:

  joboper_numwcmove_hrs

  1112VAS4145Header4

  1112VAS41410FWHI4

  1112VAS41420MAGG4

  1112VAS41430PIP4

  1112VAS41440FINSP4

  1112VAS41450PIP4

  1112VAS41460PIP0

  1112VAS41470PIP0

  1112VAS41480FINSP4

  1112VAS41490FWHR4

  1112VAS4155Header4

  1112VAS41510FWHI4

  1112VAS41515FID4

  1112VAS41518FID0

  1112VAS41520VAS4

  1112VAS41530VAS0

  1112VAS41540FID4

  1112VAS41550FINSP4

  1112VAS41560VAS4

  Is there a way to do this?

  Thank you.

  Steve

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  January 17, 2012 at 9:45 am

  #1434701

  Try this

  WITH CTE1 AS (

  SELECT job, oper_num, wc, move_hrs,

  ROW_NUMBER() OVER(PARTITION BY job ORDER BY oper_num) -

  ROW_NUMBER() OVER(PARTITION BY job,wc ORDER BY oper_num) AS rnDiff

  FROM #JobSample),

  CTE2 AS (

  SELECT job, oper_num, wc, move_hrs,

  ROW_NUMBER() OVER(PARTITION BY job,wc,rnDiff ORDER BY oper_num) AS rn

  FROM CTE1)

  SELECT job, oper_num, wc,

  CASE WHEN rn=1 THEN 4 ELSE 0 END AS move_hrs

  FROM CTE2

  ORDER BY job,oper_num;

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736212

  More actions

  January 17, 2012 at 9:49 am

  #1434704

  Mark has an ORDER BY, but you need to be sure one is included. Without an ORDER BY, there is no such thing as a consecutive record. The rows have no ordering in SQL Server.

• Matt Miller (4)

  SSC Guru

  Points: 124210

  More actions

  January 17, 2012 at 9:53 am

  #1434708

  Here's a CTE version that should do what you need to see:

  ;with OrderingCTE as (

  SELECT ROW_NUMBER() over (PARTITION by [job] order by oper_num) RN,

  * from #JobSample)

  select

  currentrow.*,nextrow.wc,nextrow.oper_num,

  case when currentrow.RN>1 and

  currentrow.wc=nextrow.wc then 0 else 4 end updatedMove_hrs

  from OrderingCTE currentrow

  left join orderingCTE nextrow on currentrow.job=nextrow.job

  and currentrow.RN=nextrow.RN-1

  The presumes that operation number unique identifies the ordering within a given job: you oculd end up with some non-deterministic results if that is NOT true.

  The CTE is there primarily to normalize the ordering to an unbroken sequence of steps so you can link to the next step.

  ----------------------------------------------------------------------------------
  Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

• ducksql@gmail.com

  SSCrazy

  Points: 2826

  More actions

  January 17, 2012 at 2:36 pm

  #1434891

  Thank you all for your help. I was able to get what I needed from your answers.

  Steve