SQl: find out the number of last consecutive numbers from the table

Question

See above data.

For each student, I need to get a bill for the last days in a row when the student was present.

Can someone please give me an idea about this?

;with CTE As
(select 1 as stdId, 1 as dt
union
select 1 as stdId, 2 as dt
union
select 1 as stdId, 3 as dt
union
select 1 as stdId, 5 as dt
union
select 1 as stdId, 6 as dt
union
select 1 as stdId, 7 as dt
union
select 1 as stdId, 9 as dt
union
select 2 as stdId, 2 as dt
union
select 2 as stdId, 4 as dt
union
select 2 as stdId, 7 as dt
union
select 2 as stdId, 8 as dt
)
select stdId, dt
FROM 
CTE

---

Answer 1

1) Classify the groups based on the next days in a row, using the difference between dt and journal number.

2) Then get the last group for each student and a counter for each group.

3) Finally, select a counter for the last group for each student.

select distinct stdid,cnt 
from (select stdid,grp,count(*) over(partition by stdid,grp) cnt,
      max(grp) over(partition by stdid) lastgrp
      from (select stdId, dt,dt-row_number() over(partition by stdid order by dt) grp
            FROM CTE) x
) y where lastgrp=grp

Sample Demo

Answer 2

One approach is to use recursive cte .

, 1, .

;with CTE As
(
    select 1 as stdId, 1 as dt
        union
    select 1 as stdId, 2 as dt
        union
    select 1 as stdId, 3 as dt
        union
    select 1 as stdId, 5 as dt
        union
    select 1 as stdId, 6 as dt
        union
    select 1 as stdId, 7 as dt
        union
    select 1 as stdId, 9 as dt
        union
    select 2 as stdId, 2 as dt
        union
    select 2 as stdId, 4 as dt
        union
    select 2 as stdId, 7 as dt
        union
    select 2 as stdId, 8 as dt
),
CTEr AS 
(
    -- Use recursiion, starting at the top and working down by one.

        -- Anchor part.
        SELECT
            stdId,
            MAX(dt) AS dt
        FROM
            CTE
        GROUP BY 
            stdId

    UNION ALL

        -- Recursive part.
        SELECT
            r.stdId,
            r.dt -1 AS dt
        FROM
            CTEr AS r
                INNER JOIN CTE AS c     ON  c.stdId = r.stdId
                                        AND c.dt    = r.dt -1
)
select 
    stdId, 
    COUNT(dt) AS Result
FROM 
    CTEr
GROUP BY
    stdId
;

, . , @vkp.

Answer 3

1 1 ( "9" ) "2" 2 ( "7" "8" ), :

select top (1) with ties t.*
from (select cte.*,
             row_number() over (partition by stdid order by date) as seqnum
      from cte
      where student = @student
     ) t
order by (dt - seqnum) desc;

? -, . dt "dt".

. .

:

• , dt, - .
• .

These are reasonable assumptions based on the question and the data in the question. There are fairly simple alternatives to handle other situations.

Finally, let me note that if your data is more like an image than a CTE, an easy way to convert it is:

select top (1) with ties t.*
from (select cte.*,
             row_number() over (partition by stdid order by date) as seqnum
      from t cross apply
           (values (t.[1]), (t.[2]), . . . ) v(dt)
      where student = @student and v.dt = 'yes'
     ) t
order by (dt - seqnum) desc;