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

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 |

CodePudding user response：

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