Don't Want to Change Consecutive Records
-
Using this table:
--===== If the test table already exists, drop itIF OBJECT_ID('TempDB..#JobName','U') IS NOT NULL DROP TABLE #DateTest
GO
--===== Create the test table
CREATE TABLE #JobSample
( job NVARCHAR(20)
, oper_num FLOAT
, wc NVARCHAR(20)
, move_hrs FLOAT
)
--===== Insert the test data into the test table
INSERT INTO #JobSample (job, oper_num, wc, move_hrs)
SELECT '1112VAS414', 5, 'Header', 12.00 UNION ALL
SELECT '1112VAS414', 10, 'FWHI', 12.00 UNION ALL
SELECT '1112VAS414', 20, 'MAGG', 12.00 UNION ALL
SELECT '1112VAS414', 30, 'PIP', 12.00 UNION ALL
SELECT '1112VAS414', 40, 'FINSP', 12.00 UNION ALL
SELECT '1112VAS414', 50, 'PIP', 12.00 UNION ALL
SELECT '1112VAS414', 60, 'PIP', 12.00 UNION ALL
SELECT '1112VAS414', 70, 'PIP', 12.00 UNION ALL
SELECT '1112VAS414', 80, 'FINSP', 12.00 UNION ALL
SELECT '1112VAS414', 90, 'FWHR', 12.00 UNION ALL
SELECT '1112VAS415', 5, 'Header', 12.00 UNION ALL
SELECT '1112VAS415', 10, 'FWHI', 12.00 UNION ALL
SELECT '1112VAS415', 15, 'FID', 12.00 UNION ALL
SELECT '1112VAS415', 18, 'FID', 12.00 UNION ALL
SELECT '1112VAS415', 20, 'VAS', 12.00 UNION ALL
SELECT '1112VAS415', 30, 'VAS', 12.00 UNION ALL
SELECT '1112VAS415', 40, 'FID', 12.00 UNION ALL
SELECT '1112VAS415', 50, 'FINSP', 12.00 UNION 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:
job oper_num wc move_hrs
1112VAS414 5 Header 4
1112VAS414 10 FWHI 4
1112VAS414 20 MAGG 4
1112VAS414 30 PIP 4
1112VAS414 40 FINSP 4
1112VAS414 50 PIP 4
1112VAS414 60 PIP 0
1112VAS414 70 PIP 0
1112VAS414 80 FINSP 4
1112VAS414 90 FWHR 4
1112VAS415 5 Header 4
1112VAS415 10 FWHI 4
1112VAS415 15 FID 4
1112VAS415 18 FID 0
1112VAS415 20 VAS 4
1112VAS415 30 VAS 0
1112VAS415 40 FID 4
1112VAS415 50 FINSP 4
1112VAS415 60 VAS 4
Is there a way to do this?
Thank you.
Steve
-
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
-
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.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
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? -
Thank you all for your help. I was able to get what I needed from your answers.
Steve
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply