Query Help

• Nagaram

  SSCrazy

  Points: 2110

  More actions

  June 21, 2012 at 2:48 am

  #260369

  Hi ,

  here is the Table2 and sample data

  col1col2

  11

  22

  3-999

  4-999

  5-999

  66

  77

  88

  99

  10-999

  -- Logic

  Here i need to update if col2 has -999 then I need to col2 value to the previous col2 value .

  from the given input data in row 3,4,5 col2 has -999 -- So here we need to update row 3,4,5 to row2 col2 .

  similarly row 10 col2 value to row9 col2 vlaue ...

  If row1 col2 has -999 then no need to update .

  -- Expected output

  col1col2Updated_col2

  111

  222

  3-9992

  4-9992

  5-9992

  666

  777

  888

  999

  10-9999

  Please help me ..

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  June 21, 2012 at 3:06 am

  #1503822

  DECLARE @t TABLE(col1 INT, col2 INT)

  INSERT INTO @t(col1,col2)

  SELECT 1, 1 UNION ALL

  SELECT 2, 2 UNION ALL

  SELECT 3, -999 UNION ALL

  SELECT 4, -999 UNION ALL

  SELECT 5, -999 UNION ALL

  SELECT 6, 6 UNION ALL

  SELECT 7, 7 UNION ALL

  SELECT 8, 8 UNION ALL

  SELECT 9, 9 UNION ALL

  SELECT 10, -999;

  WITH CTE AS (

  SELECT col1,col2,

  ROW_NUMBER() OVER(PARTITION BY CASE WHEN col2=-999 THEN 1 ELSE 0 END

  ORDER BY col1) AS rn

  FROM @t)

  SELECT a.col1,a.col2,COALESCE(b.col2,a.col2) AS Updated_col2

  FROM CTE a

  LEFT OUTER JOIN CTE b ON a.col2=-999

  AND b.col2<>-999

  AND a.col1-a.rn=b.rn

  ORDER BY a.col1;

  ____________________________________________________

  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

• Nagaram

  SSCrazy

  Points: 2110

  More actions

  June 21, 2012 at 4:17 am

  #1503852

  excellent . Thank you ...!