clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1130747 fiddles created (16582 in the last week).

select @@version;
(No column name)
Microsoft SQL Server 2012 (SP4) (KB4018073) - 11.0.7001.0 (X64) Aug 15 2017 10:23:29 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
 hidden batch(es)


create table t(id varchar(10),time1 varchar(10),main int, lag_1 int, lag_2 int);
 hidden batch(es)


insert into t select * from (values('A','01',0,0,1) ,('A','03',0,0,1) ,('A','04',0,0,0) ,('A','10',1,0,0) ,('A','11',1,0,0) ,('A','12',1,0,0) ,('B','02',1,1,1) ,('B','04',0,1,1) ,('B','07',0,0,1) ,('B','10',1,0,0) ,('B','11',1,0,0) ,('B','12',1,0,0) )t(id,time1,main,lag_1,lag_2)
12 rows affected
 hidden batch(es)


select sum(main) as tot_cnt ,sum(case when grp_1='grp_lag_1' then main end) as tot_1 ,sum(case when grp_2='grp_lag_2' then main end) as tot_2 from( select * ,max(case when lag_1 > main then 'grp_lag_1' end) over(partition by id order by id,time1) as grp_1 ,max(case when lag_2 > main then 'grp_lag_2' end) over(partition by id order by id,time1) as grp_2 from t )x
tot_cnt tot_1 tot_2
7 3 6
Warning: Null value is eliminated by an aggregate or other SET operation.
 hidden batch(es)