Execution of a "group" ordinary counter based on the "Flip" column

Question

I am usually decent in tsql set-based tasks. But it beats me up. I worked 3 days on converting the while-loop procedure to setbased. I have reached the point below ... but I can not make the final jump.

I have the following lines. MyOrdinal will be "fine" ... and the second column (MyMarker) will alternate between the value and the null value. Whenever this “flip” occurs on MyMarker, I would like to increase the value of “group by” in the order of the counter by one. Whenever flip values ​​are not zero or zero, they are grouped together as a set.

I tried a few things, but it was too ugly to write. This and, by switching to ORM, I no longer spend so much time in tsql.

declare @Holder table (   MyOrdinal int not null , MyMarker int , MyGroupNumber int   )

INSERT INTO @Holder (MyOrdinal, MyMarker)
Select 1 , 1 
union all Select 2, 2
union all Select 3, null
union all Select 4, 3
union all Select 5, 4
union all Select 6, 5
union all Select 7, 6
union all Select 8, 7
union all Select 9, 8
union all Select 10, 9
union all Select 11, 10
union all Select 12, 11
union all Select 13, 12
union all Select 14, 13
union all Select 15, 14
union all Select 16, 15
union all Select 17, null
union all Select 18, null
union all Select 19, null
union all Select 20, 16
union all Select 21, 17
union all Select 22, 18
union all Select 23, null
union all Select 24, null
union all Select 25, 19
union all Select 26, 20
union all Select 27, null
union all Select 28, 21

Select * from @Holder

Desired output

| MyOrdinal | MyMarker | MyGroupNumber |
|-----------|----------|---------------|
|         1 |        1 |             1 |
|         2 |        2 |             1 |
|         3 |    null  |             2 |
|         4 |        3 |             3 |
|         5 |        4 |             3 |
|         6 |        5 |             3 |
|         7 |        6 |             3 |
|         8 |        7 |             3 |
|         9 |        8 |             3 |
|        10 |        9 |             3 |
|        11 |       10 |             3 |
|        12 |       11 |             3 |
|        13 |       12 |             3 |
|        14 |       13 |             3 |
|        15 |       14 |             3 |
|        16 |       15 |             3 |
|        17 |    null  |             4 |
|        18 |    null  |             4 |
|        19 |    null  |             4 |
|        20 |       16 |             5 |
|        21 |       17 |             5 |
|        22 |       18 |             5 |
|        23 |    null  |             6 |
|        24 |    null  |             6 |
|        25 |       19 |             7 |
|        26 |       20 |             7 |
|        27 |    null  |             8 |
|        28 |       21 |             9 |

Answer 1

Try the following:

First, it assigns the same ROW_NUMBERfor continuous Non- NULL MyMarker. ROW_NUMBER NULLfor NULL MyMarkers. After that, you want to add ROW_NUMBERfor NULL MyMarkerso that the value is between the previous NON- NULLand the next NON- NULL. Then use DENSE_RANKto finally assign MyGroupNumber:

SQL Fiddle

;WITH Cte AS(
    SELECT *,
        RN = ROW_NUMBER() OVER(ORDER BY MyOrdinal) - MyMarker + 1
    FROM @Holder
),
CteApply AS(
    SELECT
        t.MyOrdinal,
        t.MyMarker,
        MyGroupNumber = 
            CASE
                WHEN RN IS NULL THEN x.NewRN
                ELSE RN
            END
    FROM Cte t
    OUTER APPLY(
        SELECT TOP 1 RN * 1.1 AS NewRN
        FROM Cte
        WHERE 
            t.MyOrdinal > MyOrdinal
            AND MyMarker IS NOT NULL
        ORDER BY MyOrdinal DESC
    )x
)
SELECT 
    MyOrdinal,
    MyMarker,
    MyGroupNumber = DENSE_RANK() OVER(ORDER BY MyGroupNumber)
FROM CteApply

Answer 2

For Sql Server 2012:

select *, sum(b) over(order by myordinal) 
from(select *,  
            case when (lag(mymarker) over(order by myordinal) is not null 
                   and mymarker is null) or 
                   (lag(mymarker) over(order by myordinal) is null 
                   and mymarker is not null)
            then 1 else 0 end as b
from @Holder) t

1, null not null not null null. 0. . Fiddle http://sqlfiddle.com/#!6/9eecb/5015

Sql Server 2008:

with cte1 as (select *,
case when (select max(enddate) from t ti
           where ti.ruleid = t.ruleid and ti.startdate < t.startdate) = startdate 
           then 0 else 1 end as b
from t),
cte2 as(select *, sum(b) over(partition by ruleid order by startdate) as s
from cte1)
select RuleID, 
       Name, 
       min(startdate), 
       case when count(*) = count(enddate) 
            then max(enddate) else null end from cte2
group by s, ruleid, name

Fiddle http://sqlfiddle.com/#!6/4191d/6