3

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 :/

SqlZim
  • 37,248
  • 6
  • 41
  • 59
Newbie Coder
  • 85
  • 3
  • 11

1 Answers1

2

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 |
+------------+---------+------+--------+----------+-----------+----+
SqlZim
  • 37,248
  • 6
  • 41
  • 59
  • Thanks a lot! This works though if you can explain to me what grp is doing that would be very helpful – Newbie Coder Apr 10 '17 at 23:24
  • @NewbieCoder `grp` is a unique number for each logical group that is calculated using two running numbers (`row_number()`). Both share the same `order by`, but with different partitions (in this case, one without a partition and one with a partition). This might make things a bit clearer: http://rextester.com/KFJHA53489 – SqlZim Apr 11 '17 at 13:16