Сравнение количества строк за месяц

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
Теги:
строки, сравнение, количество строк
Добавлено: 27 Апреля 2021 07:32:26 Добавил: Андрей Ковальчук Нравится 0
Добавить
Комментарии:
Нету комментариев для вывода...