How to get the first occurrence of a group of values with T-SQL

Question

Having a SQL Server DB table below how can I get the data related to the first occurrence of a series of values (TableA and TableB_S have UserId linked) - I need to get the first occurrence of the last series of IsActive = 1 value (the 5th row in the table, 1000 |1 | 2022-02-18 10:23:01):

TableB_S

UserId	IsActive	Date
1000	0	2021-10-11 13:23:00
1000	0	2021-11-11 15:23:12
1000	1	2021-11-10 12:23:32
1000	0	2022-01-02 09:23:56
1000	1	2022-02-18 10:23:01
1000	1	2022-02-22 13:23:12
1000	1	2022-03-23 18:23:13

The query below returns the data related to the last occorrence of the value IsActive = 1 (1000 | 1 | 2022-03-23 18:23:13)

select a.*, ca.UserId, ca.Date
from TableA a

cross apply (select top 1 s.UserId, s.Date 
            from TableB_S s where s.UserId = a.UserId
        order by s.Date desc) ca

How to get this data ?

| 1000   | 1         |  2022-02-18 10:23:01 |

Could you define "series". What happens if there is 0,0,0,111111 as the data of isActive, or only zeros, or only 1s? — Paul Maxwell, Jun 28 '22 at 00:59
Sorry, I had to be more specific. For series I mean a group of 1s (```IsActive =1```) values which usually occur continuously for a period of time. As per your example, 0,0,0,111111, if we assume they occurred in chronological order query should return the 4th and for only 1s the first ever occurrence. For all zeros it should not return any values as we need only the active users. — Damian2310, Jun 28 '22 at 09:04

allmhuran · Accepted Answer · 2022-06-28T03:50:53.343

The logic here is "for each user get the set of rows which start a series of 1's (ie, the previous row in date order for the user is a zero, or doesn't exist), then of that set get the highest date".

select   UserId, IsActive, max([Date])
from     (
            select   UserId, 
                     IsActive, 
                     [Date],
                     PriorActive =  lag(IsActive, 1, 0) 
                                    over 
                                    (
                                       partition by UserId 
                                       order by [Date] asc
                                    )
            from     #TableB_S
         ) t
where    t.IsActive = 1 
         and t.PriorActive = 0
group by UserId, IsActive

How to get the first occurrence of a group of values with T-SQL

1 Answers1