with a as
(
select
c.id_Seller, count(*) as count_s, lag(count(*), 1, count(*) - 1) over (partition by c.id_Seller order by month(c.DateSell)) as prev_count_s
from
Clients c
where
c.DateSell >= datefromparts(year(getdate()), 1, 1) and c.DateSell < datefromparts(year(getdate()) + 1, 1, 1)
group by
c.id_Seller, month(c.DateSell)
)
select
a.id_Seller, e.fName, e.lName
from
a join
Empoyees e on e.id = a.id_Seller
group by
a.id_Seller, e.fName, e.lName
having
count(*) = sum(case when a.count_s > a.count_s_prev then 1 else 0 end);
На основе сообщения invim'а сделал следующее.
select
e.fName, e.lName
from Employees e
join ( select c.id_Seller, COUNT(*) K from Clients c where
YEAR(c.DateSell) = YEAR(getdate()) and c.DateSell >= datefromparts(year(getdate()), 1, 1)
group by c.id_Seller, MONTH(c.DateSell)
) count_s on count_s.id_Seller = e.id
join ( select c.id_Seller, COUNT(*) K from Clients c where
YEAR(c.DateSell) = YEAR(getdate()) and c.DateSell < datefromparts(year(getdate()) + 1, 1, 1)
group by c.id_Seller, MONTH(c.DateSell)
) prev_count_s on count_s.id_Seller = e.id
where count_s.K > prev_count_s.K
group by
e.fName, e.lName