How do I create a conditional RUNNING-COUNT?

[psrpsrpsr]

My business requirement is to show the highest number of days where a specific value (zero) occurs in sequence. I have used RUNNING-COUNT with no success.

Sample data:
LocId   Date         FactCount    DESIRED RUNNING-COUNT
1234    3/1/17     7                  null
1234    3/2/17     12                null
1234    3/3/17     0                  1
1234    3/4/17     0                  2
1234    3/5/17     14                null
1234    3/6/17     0                  1
1234    3/7/17     0                  2
1234    3/8/17     0                  3
1234    3/9/17     0                  4
1234    3/10/17   5                  null

Here's what I've tried so far:
1.) Using the FOR LocId context in the running-count function. It's erroneously NOT RESETTING the running-count function at the start of each row where FactCount = 0.
Input:
CASE  WHEN [FactCount] = 0 THEN running-count ( [FactCount] FOR [LocId)]  )
ELSE NULL
END

Output:
LocId   Date       FactCount    DESIRED RUNNING-COUNT
1234    3/1/17   7                  null
1234    3/2/17   12                null
1234    3/3/17   0                  2
1234    3/4/17   0                  3
1234    3/5/17   14                null
1234    3/6/17   0                  4
1234    3/7/17   0                  5
1234    3/8/17   0                  6
1234    3/9/17   0                  7
1234    3/10/17 5                  null

2.) Using the FOR LocId and Date contexts in the running-count function.
Input:
CASE  WHEN [FactCount] = 0 THEN running-count ( [FactCount] FOR [LocId)] , [Date]  )
ELSE NULL
END

Output:
LocId   Date       FactCount    DESIRED RUNNING-COUNT
1234    3/1/17   7                  null
1234    3/2/17   12                null
1234    3/3/17   0                  1
1234    3/4/17   0                  1
1234    3/5/17   14                null
1234    3/6/17   0                  1
1234    3/7/17   0                  1
1234    3/8/17   0                  1
1234    3/9/17   0                  1
1234    3/10/17 5                  null

Please let me know how to approach this. Thank you!

[Lynn]

My business requirement is to show the highest number of days where a specific value (zero) occurs in sequence. I have used RUNNING-COUNT with no success.

Sample data:
LocId   Date         FactCount    DESIRED RUNNING-COUNT
1234    3/1/17     7                  null
1234    3/2/17     12                null
1234    3/3/17     0                  1
1234    3/4/17     0                  2
1234    3/5/17     14                null
1234    3/6/17     0                  1
1234    3/7/17     0                  2
1234    3/8/17     0                  3
1234    3/9/17     0                  4
1234    3/10/17   5                  null

Here's what I've tried so far:
1.) Using the FOR LocId context in the running-count function. It's erroneously NOT RESETTING the running-count function at the start of each row where FactCount = 0.
Input:
CASE  WHEN [FactCount] = 0 THEN running-count ( [FactCount] FOR [LocId)]  )
ELSE NULL
END

Output:
LocId   Date       FactCount    DESIRED RUNNING-COUNT
1234    3/1/17   7                  null
1234    3/2/17   12                null
1234    3/3/17   0                  2
1234    3/4/17   0                  3
1234    3/5/17   14                null
1234    3/6/17   0                  4
1234    3/7/17   0                  5
1234    3/8/17   0                  6
1234    3/9/17   0                  7
1234    3/10/17 5                  null

2.) Using the FOR LocId and Date contexts in the running-count function.
Input:
CASE  WHEN [FactCount] = 0 THEN running-count ( [FactCount] FOR [LocId)] , [Date]  )
ELSE NULL
END

Output:
LocId   Date       FactCount    DESIRED RUNNING-COUNT
1234    3/1/17   7                  null
1234    3/2/17   12                null
1234    3/3/17   0                  1
1234    3/4/17   0                  1
1234    3/5/17   14                null
1234    3/6/17   0                  1
1234    3/7/17   0                  1
1234    3/8/17   0                  1
1234    3/9/17   0                  1
1234    3/10/17 5                  null

Please let me know how to approach this. Thank you!

You can try flipping your logic inside-out. Not sure if that will solve your problem but that is usually how I would wrap a conditional summary.

running-count (
  CASE  WHEN [FactCount] = 0 THEN [FactCount] ELSE NULL END
FOR [LocId)]  )

[psrpsrpsr]

Thank you for the suggestion - see below for the input and output. Is there a way to reset the running-count at each non-contiguous instance of a zero (such that in the example, 3/6 would restart from the prior 'run' and be 1, 3/7 would be 2, etc.)?

Input:
running-count ( CASE  WHEN [FactCount] = 0 THEN [FactCount] ELSE NULL END FOR [LocId)] )

Output:
LocId   Date         FactCount    RUNNING-COUNT
1234    3/1/17     7                  0
1234    3/2/17     12                0
1234    3/3/17     0                  1
1234    3/4/17     0                  2
1234    3/5/17     14                2
1234    3/6/17     0                  3
1234    3/7/17     0                  4
1234    3/8/17     0                  5
1234    3/9/17     0                  6
1234    3/10/17   5                  6

I also tried to create a dummy field to give more context to the running-count FOR clause:

dummy_field = CASE WHEN [FactCount] = 0 THEN 1 ELSE NULL END
...and used the dummy_field in the FOR clause as below:
running-count ( CASE  WHEN [FactCount] = 0 THEN [FactCount] ELSE NULL END FOR [LocId)] , [dummy_field] )

...this also did not work, because the values of LocId and dummy_field are the same:
LocId   Date         FactCount    dummy_field     RUNNING-COUNT
1234    3/1/17     7                  0                       0
1234    3/2/17     12                0                       0
1234    3/3/17     0                  1                       1
1234    3/4/17     0                  1                       2
1234    3/5/17     14                0                       0
1234    3/6/17     0                  1                       3
1234    3/7/17     0                  1                       4
1234    3/8/17     0                  1                       5
1234    3/9/17     0                  1                       6
1234    3/10/17   5                  0                       0
1234    3/11/17   10                0                       6

[psrpsrpsr]

I think if I can number each 'island' as it's own iterative number, that would give me the proper context to restart the running-count, for example the island_num field below would allow me to use the running-count ( CASE  WHEN [FactCount] = 0 THEN [FactCount] ELSE NULL END FOR [LocId)] , [island_num] ):

...but I'm not sure how I would get this field!

LocId   Date         FactCount    island_num       RUNNING-COUNT
1234    3/1/17     7                  0                       0
1234    3/2/17     12                0                       0
1234    3/3/17     0                  1                       1
1234    3/4/17     0                  1                       2
1234    3/5/17     14                0                       0
1234    3/6/17     0                  2                       1
1234    3/7/17     0                  2                       2
1234    3/8/17     0                  2                       3
1234    3/9/17     0                  2                       4
1234    3/10/17   5                  0                       0
1234    3/11/17   10                0                       0

[psrpsrpsr]

Anyone have thoughts on how to achieve this? Thank you

[hespora]

No matter how you approach this issue, at some point you're going to need an iterative approach to compare a row's value to the previous row's value, compared to the previous row's value, compared to the previous row's value, and so forth (or even just an aggregate function of a value, but as of the previous row). That's fairly easy to do in a spreadsheet, but not at all trivial in a relational query.

I've tried around alot, but I don't see this working, sry

[zvi_weinstock]

You can use the DENSE_RANK function this will give the next value whenever the record value is changed then on top of that use the running count


Sent from my iPhone using Tapatalk

[psrpsrpsr]

Hi all, I was able to achieve this by applying an 'islands' solution popularized by Itzik Ben Gan, using two queries. I will try to update this post with a more concrete example in the future, but the long and short of it is:

Query 1: use RUNNING-COUNT() to create a row count for the desired context (in this case, it was LocationID)

Query 2:
a.) Create a reference query of Query 1
b.) filter it on the desired 'target' rows (in this case, they were FactCount =0)
c.) Apply another RUNNING-COUNT() to the query in the same context as query 1
d.) Create another field with the result of Query1.runningcount MINUS Query2.runningcount. This is your 'group' that indicates whether Query 1 and Query 2 were iterating together by 1 row, and creates a distinct value for each group of 'islands'. I called the field 'island'.
e.) Use the group in the context of yet ANOTHER RUNNING-COUNT() to apply new row numbers to each new 'island'. In my case, it was:

RUNNING-COUNT([FactCount] FOR [LocationId] , [island] )

See this link for the chapter of the book from which I took this strategy:
https://manning-content.s3.amazonaws.com/download/3/e589652-7171-4310-a714-e84dd0f14090/SampleChapter5.pdf