By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE max_in_row
([hit_finish_dttm] varchar(13), [hid] varchar(13), [agent_login] varchar(57), [flg_no_talk] int)
;
INSERT INTO max_in_row
([hit_finish_dttm], [hid], [agent_login], [flg_no_talk])
VALUES
('2020-03-01', 'EQERR13', 'Dmitrii', 0),
('2020-03-02', 'EQERR13', 'Dmitrii', 1),
('2020-03-03', 'EQERR13', 'Dmitrii', 1),
('2020-03-01', 'RR13EQE', 'Dmitrii', 0),
('2020-03-02', 'RR13EQE', 'Dmitrii', 1),
('2020-03-03', 'RR13EQE', 'Dmitrii', 0),
('2020-03-04', 'RR13EQE', 'Dmitrii', 0),
('2020-03-05', 'RR13EQE', 'Dmitrii', 1),
('2020-03-06', 'RR13EQE', 'Dmitrii', 1),
('2020-03-07', 'RR13EQE', 'Dmitrii', 0),
('2020-03-01', 'EQERR13', 'Alex', 1),
('2020-03-02', 'EQERR13', 'Alex', 1),
('2020-03-03', 'EQERR13', 'Alex', 0),
('2020-03-04', 'EQERR13', 'Alex', 1),
('2020-03-05', 'EQERR13', 'Alex', 1),
('2020-03-06', 'EQERR13', 'Alex', 1),
('2020-03-02', 'RR13EQE', 'Alex', 1),
('2020-03-03', 'RR13EQE', 'Alex', 0),
('2020-03-04', 'RR13EQE', 'Alex', 1)
19 rows affected
select hid, agent_login, max(cnt)
from (select hid, agent_login, count(*) as cnt
from (select mir.*,
row_number() over (partition by hid, agent_login order by hit_finish_dttm) as seqnum
from max_in_row mir
where flg_no_talk = 1
) mir
group by hid, agent_login, dateadd(day, -seqnum, hit_finish_dttm)
) ha
group by hid, agent_login
| hid | agent_login | (No column name) |
|---|---|---|
| EQERR13 | Alex | 3 |
| RR13EQE | Alex | 1 |
| EQERR13 | Dmitrii | 2 |
| RR13EQE | Dmitrii | 2 |