CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012gaps-and-islands
Newbie Coder
Newbie Coder

Reputation: 85

SQL Server Row_Count with Reset

I have 6 columns:

Date, Account, Side, Symbol, Currency, Reset Flag (0 Yes, 1 No)

I want to row count over partition by but reset the row count whenever a 0 appears in Reset Flag column. The first 5 columns are not unique but their combination forms a unique set of columns.

Please help me with this !

Every other solution I've researched doesn't work for some reason :/

Upvotes: 3

Views: 1659

Answers (1)

SqlZim
SqlZim

Reputation: 38033

This is a gaps and islands style problem. Without any sample data or desired results...

Using two row_number() to identify groups by reset flag, and another in the outer query to number the rows by ResetFlag and the grp created in the inner query.

Change the order of date, Account, Side, Symbol, Currency to whichever order of those columns you want to number the rows by; keep them in the same order for each of the three row_number()s.

/* ----- */ 
select 
    date
  , Account
  , Side
  , Symbol
  , Currency
  , ResetFlag
  , rn = case when ResetFlag = 0 then 0
         else row_number() over (
            partition by ResetFlag, grp
            order by date, Account, Side, Symbol, Currency)
         end
from (
  select *
    , grp = row_number() over (order by date, Account, Side, Symbol, Currency) 
          - row_number() over (
              partition by ResetFlag 
              order by date, Account, Side, Symbol, Currency)
  from t
  ) s
order by date, Account, Side, Symbol, Currency

rextester demo: http://rextester.com/VLCO32635

returns:

+------------+---------+------+--------+----------+-----------+----+
|    date    | Account | Side | Symbol | Currency | ResetFlag | rn |
+------------+---------+------+--------+----------+-----------+----+
| 2017-01-01 |       7 |    2 |      3 | 7,0000   |         1 |  1 |
| 2017-01-02 |       8 |    9 |      9 | 6,0000   |         1 |  2 |
| 2017-01-03 |       4 |    1 |      5 | 6,0000   |         1 |  3 |
| 2017-01-04 |       5 |    4 |      8 | 5,0000   |         0 |  0 |
| 2017-01-05 |       2 |    1 |      3 | 1,0000   |         1 |  1 |
| 2017-01-06 |       8 |    0 |      2 | 0,0000   |         0 |  0 |
| 2017-01-07 |       0 |    3 |      8 | 9,0000   |         1 |  1 |
| 2017-01-08 |       0 |    3 |      1 | 3,0000   |         1 |  2 |
+------------+---------+------+--------+----------+-----------+----+

Upvotes: 2

Related Questions