title: ROW_NUMBER Skip Rows Based on Condition
body: <p>I have a calendar table that has a key (integer) column on every date. Valid business dates are assigned keys; they go in ascending order.  Problem is, Saturdays and Sundays are not considered valid business dates, so they have a value of "9999."  What I want to do in this case is use the row that comes after the "9999" as the "Next" value instead of using the row with the "9999."  So, to explain in pseudo code, I'm looking for something like:</p>
  
  <pre><code>Case When nex.KEY = '9999' Then skip it and find the nex.KEY row that is not '9999'
      Else CTE.NEXT_DT
  END AS Next
  </code></pre>
  
  <p>I started with a CTE, but am open to ideas.  Here is an output sample of what I am able to get now.  The '9999' values in column "Next" are what I'm looking to update.  </p>
  
  <p>Current Output:</p>
  
  <pre><code>rowNum  DAY BUSINESS_DT KEY     PREV_DT     NEXT_DT     Previous Next
  3932    Sun 2015-09-13  9999    2015-09-11  2015-09-14  9999    2709
  3933    Mon 2015-09-14  2709    2015-09-11  2015-09-15  9999    2710
  3934    Tue 2015-09-15  2710    2015-09-14  2015-09-16  2709    2711
  3935    Wed 2015-09-16  2711    2015-09-15  2015-09-17  2710    2712
  3936    Thu 2015-09-17  2712    2015-09-16  2015-09-18  2711    2713
  3937    Fri 2015-09-18  2713    2015-09-17  2015-09-21  2712    9999
  3938    Sat 2015-09-19  9999    2015-09-18  2015-09-21  2713    9999
  3939    Sun 2015-09-20  9999    2015-09-18  2015-09-21  9999    2714
  3940    Mon 2015-09-21  2714    2015-09-18  2015-09-22  9999    2715
  </code></pre>
  
  <p>Desired Output:</p>
  
  <pre><code>rowNum  DAY BUSINESS_DT KEY     PREV_DT     NEXT_DT     Previous Next
  3932    Sun 2015-09-13  9999    2015-09-11  2015-09-14  9999    2709
  3933    Mon 2015-09-14  2709    2015-09-11  2015-09-15  9999    2710
  3934    Tue 2015-09-15  2710    2015-09-14  2015-09-16  2709    2711
  3935    Wed 2015-09-16  2711    2015-09-15  2015-09-17  2710    2712
  3936    Thu 2015-09-17  2712    2015-09-16  2015-09-18  2711    2713
  3937    Fri 2015-09-18  2713    2015-09-17  2015-09-21  2712    **2714**
  3938    Sat 2015-09-19  9999    2015-09-18  2015-09-21  2713    **2714**
  3939    Sun 2015-09-20  9999    2015-09-18  2015-09-21  9999    2714
  3940    Mon 2015-09-21  2714    2015-09-18  2015-09-22  9999    2715
  </code></pre>
  
  <p>Below is my code:</p>
  
  <pre><code>WITH CTE AS
  (
  Select 
  rowNum = ROW_NUMBER() OVER(ORDER BY d.CALENDAR_DT_ID),
  d.DAY,
  d.KEY,
  d.BUSINESS_DT,
  d.PREV_DT,
  d.NEXT_DT,
  d.HOLIDAY_IN
  FROM CALENDAR_DT d
  )
  
  SELECT
  CTE.rowNum,
  CTE.DAY,
  CTE.BUSINESS_DT,
  CTE.KEY,
  CTE.PREV_DT,
  CTE.NEXT_DT,
  prev.KEY As Previous,
  nex.KEY As Next
  
  FROM CTE
  LEFT JOIN CTE prev ON prev.rownum = CTE.rownum - 1 
  LEFT JOIN CTE nex ON nex.rownum = CTE.rownum + 1
  ORDER BY BUSINESS_DT
  </code></pre>