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!
Quote from: psrpsrpsr on 10 Apr 2017 08:52:05 AM
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)] )
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
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
Anyone have thoughts on how to achieve this? Thank you
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
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
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