title: Shift a column based on id and time sql server
body: <p>I have a big table like below:</p>
  
  <pre><code>id  date    count
  1   201241  1
  2   201241  2
  3   201241  0
  1   201242  5
  2   201242  3
  4   201242  4
  3   201243  8
  4   201243  2
  ...
  </code></pre>
  
  <p>How can I shift count column based on id and date columns.</p>
  
  <pre><code>id  date    shifted_count
  1   201241  0
  2   201241  0
  3   201241  0
  1   201242  1
  2   201242  2
  4   201242  0
  3   201243  0
  4   201243  4
  ...
  </code></pre>
  
  <p>I had some tries but they are incorrect:    </p>
  
  <pre><code>;WITH CTE AS
  (
      SELECT count OVER(ORDER BY id , date ASC) shcount
      FROM mytable
  )
  UPDATE mytable SET shifted_count = (SELECT shcount from CTE )
  </code></pre>