What's the correct syntax to reset count when met value 1 in SQL Server 2008?

Question

How do I reset rank() when meet value 1 in SQL Server 2008 (no Lag function)?

As you can see from the code, column 'Column_Reference' doesn't produce the results I wanted. Any idea how to fix this??

case when 
         (case when Datediff(day, s2.DutyStartDateLocal, s1.DutyStartDateLocal) = 1 then 1
         else 0
         end) = 0 then 1--+ (-1*row_number() OVER (PARTITION BY s1.CREWID  order by s1.CREWID,s1.DutyStartDateLocal))
     ELSE 
      row_number() OVER (PARTITION BY s1.CREWID order by s1.CREWID,s1.DutyStartDateLocal)
         END as Column_Reference

Current result of column_reference    Ideal result
--------------------------------------------------
              1                           1
              2                           2
              3                           3
              4                           4
              1                           1
              6                           2
              7                           3
              8                           4
              9                           5
              1                           1
             11                           2
             12                           3
             13                           4
             14                           5

Answer 1

Upgrade to a more recent version of SQL Server! It will at least be supported!

You want to assign groups to ranges and then use row_number(). Unfortunately, cumulative sums are not allowed, but you can use apply.

I can't follow what columns and tables you care about. It is very important that you have an ordering column -- which I will call DutyStartDateLocal but could be multiple columns.

The structure is:

select t.*,
       row_number() over (partition by t.crewid, g.grp order by DutyStartDateLocal) as seqnum
from t cross apply
     (select count(*) as grp
      from t t2
      where t2.crewid = t.crewid and 
            t2.DutyStartDateLocal <= t.DutyStartDateLocal  and
            t2.colref = 0
     ) g ;