title: CASE statement logic in SQL Server
body: <p>I am working on SQL Server 2014 and I am struggling with coming up with CASE statement logic to alter the DisciplinaryActionReasonCode value to the record with a "seq" number of 1 when the permnum, DisciplinaryIncidentNumber and DateofDisciplinaryAction values are duplicated.  What I have in my SQL code below is what I've attempted.  You'll see that in the image I've attached I am needing to change the 41 value to 21 based on what I've stated above.</p>
  
  <p>Any help/direction would be appreciated.  Thank you.</p>
  
  <p>Here is my SQL code:</p>
  
  <pre><code>DECLARE @SchoolYear varchar(4) = '2016'
  DECLARE @Submission int = 3;
  
  SELECT @SchoolYear as SchoolYear, 
      @Submission AS Submission, 
      '220905' AS DistrictID, 
      Row_number() OVER (partition by A.incnum, A.locid ORDER BY A.dispnum DESC) as seq,
      A.permnum, 
      B.txuniqueid, 
      '220905' + A.disschnum AS CampusIDofEnrollment, 
      stuff(A.incnum, 1, 0, replicate('0', 6 - len(A.incnum))) AS DisciplinaryIncidentNumber,
      **CASE 
          WHEN ROW_NUMBER() OVER (PARTITION BY A.incnum, A.locid ORDER BY A.dispnum DESC) &gt; 2     
          AND COUNT(A.PERMNUM) &gt; 1
          AND COUNT(STUFF(A.incnum, 1, 0, REPLICATE('0', 6 - LEN(A.incnum)))) &gt; 1
          AND COUNT(CONVERT(VARCHAR(10), A.dateofdisciplinaryaction, 120) &gt; 1
          THEN 
  
          --WHEN A.desccode LIKE 'XSTA-[0-9][0-9]%' THEN Substring(A.desccode, 6, 2) ELSE Substring(A.desccode, 1, 2) 
      END AS DisciplinaryActionReasonCode,**
      CONVERT(VARCHAR(10), A.dateofdisciplinaryaction, 120) AS DateofDisciplinaryAction 
  FROM [dbo].[XXdiscipline] A 
  INNER JOIN [dbo].[XXstudents] B 
  ON A.permnum = B.permnum 
  WHERE (A.desccode LIKE '[0-9][0-9]%' OR A.desccode LIKE 'XSTA%') 
  AND (A.dispcode LIKE '[0-9][0-9]%' OR A.dispcode LIKE 'XSTA-[0-9][0-9]%') 
  AND A.PERMNUM = 1042003
  ORDER BY permnum asc, DisciplinaryIncidentNumber asc,  a.DISPNUM asc
  </code></pre>
  
  <p>Here is an image of the SQL results:  <a href="https://i.stack.imgur.com/tf79Y.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/tf79Y.png" alt="CaseStatememtLogicQuestion"></a></p>