Lets say we have an table where each row is a day, and it is ordered by this day column. Then we have left joined a membership data set showing which day the members were active (and not).
Lets say our current data set looks like this... Membership was active from day 3-5, inactive from 5-8, and active from day 9 onward etc.
DAY DATE MEMBER ACTIVE
1 2017-01-01 123 null
2 2017-01-02 123 null
3 2017-01-03 123 2017-01-03
4 2017-01-04 123 2017-01-04
5 2017-01-05 123 2017-01-05
6 2017-01-06 123 null
7 2017-01-07 123 null
8 2017-01-08 123 null
9 2017-01-09 123 2017-01-09
10 2017-01-10 123 2017-01-10
...so ACTIVE=null
means membership was not active on those days.
With this data structure, I would like to get to a "collapsed" set, showing "spans" of time inactive/active:
MEMBER MIN(DATE) MAX(DATE) STATUS
123, 2017-01-01, 2017-01-02 INACTIVE
123, 2017-01-03, 2017-01-05 ACTIVE
123, 2017-01-06, 2017-01-08 INACTIVE
123, 2017-01-09, 2017-01-10 ACTIVE
I have tried using row_number() to somehow partition out the subsets of a certain status, but in this case, using min()
/max()
over the rows where ACTIVE is null, treats those as a single group, when in reality, there are several distinct spans of "inactive membership".
How can I distinguish the spans of inactive membership from one-another for grouping purposes? What technique can I use to achieve that output above?
Here is the script to generate the dummy source data:
CREATE TABLE ##SRC (ID INT, D DATE, MEMBER INT, ACTIVE DATE);
INSERT INTO ##SRC (ID, D, MEMBER, ACTIVE)
SELECT 1, '2017-01-01', 123, NULL UNION
SELECT 2, '2017-01-02', 123, NULL UNION
SELECT 3, '2017-01-03', 123, '2017-01-03' UNION
SELECT 4, '2017-01-04', 123, '2017-01-04' UNION
SELECT 5, '2017-01-05', 123, '2017-01-05' UNION
SELECT 6, '2017-01-06', 123, NULL UNION
SELECT 7, '2017-01-07', 123, NULL UNION
SELECT 8, '2017-01-08', 123, NULL UNION
SELECT 9, '2017-01-09', 123, '2017-01-09' UNION
SELECT 10, '2017-01-10', 123, '2017-01-10'
;