Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Case, count, having и условие>=2 (хитрый count)  [new]
CheaterX
Member

Откуда:
Сообщений: 61
Добрый день!
Существует таблица

contract	amount	rest	условие	дата
Договор 1	100	30	<40%	Дата 1
Договор 1	100	30	<40%	Дата 2
Договор 1	100	90	>=70%	Дата 3
Договор 1	100	90	>=70%	Дата 4
Договор 1	100	90	>=70%	Дата 5
Договор 1	100	80	>=70%	Дата 6
Договор 2	100	100	>=70%	Дата 1
Договор 2	100	60	x	Дата 2
Договор 2	100	50	x	Дата 3
Договор 2	100	0	<40%	Дата 4
Договор 2	100	0	<40%	Дата 5
Договор 2	100	0	<40%	Дата 6
Договор 3	100	30	<40%	Дата 1
Договор 3	100	20	<40%	Дата 2
Договор 3	100	50	x	Дата 3
Договор 3	100	50	x	Дата 4
Договор 3	100	90	>=70%	Дата 5
Договор 3	100	100	>=70%	Дата 6


условие (сравнение rest<0.4amount или rest>=0.7*amount если иначе то x)


Необходимо написать запрос выбирающий договора
у которых:
1) значения <40% встречаются более или равное 2 раз и >=70 более или равное 2 раз
2) значение поля rest на последнюю дату (Дата 6) не было максимальным по сравнению со всеми значениями данного поля за все отчетные даты, по данному договору

то есть чтобы выводил только Договор 1

У меня пока что пришло на ум отдельно посчитать >=70, отдельно <40, потом скроссить результаты, но мне кажется есть более простой способ (желательно в простом селекте, без использования процедур и переменных, хотябы удовлетворяющий первому требованию)
18 апр 14, 11:02    [15899992]     Ответить | Цитировать Сообщить модератору
 Re: Case, count, having и условие>=2 (хитрый count)  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
select t.* from t
pivot ( count(условие) for [условие] in ( [<40%], [>=70%] ) ) pv
where pv.[<40%] >= 2
  and pv.[>=70%] >=2
18 апр 14, 12:37    [15900607]     Ответить | Цитировать Сообщить модератору
 Re: Case, count, having и условие>=2 (хитрый count)  [new]
китайский сервер
Member [скрыт] [заблокирован]

Откуда:
Сообщений: 3582
CheaterX
Добрый день!
Существует таблица

contract	amount	rest	условие	дата
Договор 1	100	30	<40%	Дата 1
Договор 1	100	30	<40%	Дата 2
Договор 1	100	90	>=70%	Дата 3
Договор 1	100	90	>=70%	Дата 4
Договор 1	100	90	>=70%	Дата 5
Договор 1	100	80	>=70%	Дата 6
Договор 2	100	100	>=70%	Дата 1
Договор 2	100	60	x	Дата 2
Договор 2	100	50	x	Дата 3
Договор 2	100	0	<40%	Дата 4
Договор 2	100	0	<40%	Дата 5
Договор 2	100	0	<40%	Дата 6
Договор 3	100	30	<40%	Дата 1
Договор 3	100	20	<40%	Дата 2
Договор 3	100	50	x	Дата 3
Договор 3	100	50	x	Дата 4
Договор 3	100	90	>=70%	Дата 5
Договор 3	100	100	>=70%	Дата 6


условие (сравнение rest<0.4amount или rest>=0.7*amount если иначе то x)


Необходимо написать запрос выбирающий договора
у которых:
1) значения <40% встречаются более или равное 2 раз и >=70 более или равное 2 раз
2) значение поля rest на последнюю дату (Дата 6) не было максимальным по сравнению со всеми значениями данного поля за все отчетные даты, по данному договору

то есть чтобы выводил только Договор 1

У меня пока что пришло на ум отдельно посчитать >=70, отдельно <40, потом скроссить результаты, но мне кажется есть более простой способ (желательно в простом селекте, без использования процедур и переменных, хотябы удовлетворяющий первому требованию)

шота типа такого
select a.contract	
from tab a
cross apply (select top 1 b.rest from tab b where a.contract=b.contract order by b.дата desc) c
group by a.contract, c.rest	
having sum(case when a.условие<40 then 1 end)>=2
   and sum(case when a.условие>=70 then 1 end)>=2
   and max(a.rest)<c.rest
18 апр 14, 12:44    [15900653]     Ответить | Цитировать Сообщить модератору
 Re: Case, count, having и условие>=2 (хитрый count)  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
Knyazev Alexey
select t.* from t
pivot ( count(условие) for [условие] in ( [<40%], [>=70%] ) ) pv
where pv.[<40%] >= 2
  and pv.[>=70%] >=2


select * from t
pivot ( count(условие) for [условие] in ( [<40%], [>=70%] ) ) pv
where pv.[<40%] >= 2
  and pv.[>=70%] >=2
18 апр 14, 15:28    [15901881]     Ответить | Цитировать Сообщить модератору
 Re: Case, count, having и условие>=2 (хитрый count)  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
select top(1) with ties *
from myTable c
order by 
   iif(max(c.дата)over(partition by c.contract)=c.дата 
   and max(c.rest)over(partition by c.contract)>c.rest
   and sum(iif(c.условие='<40%',1,0))over(partition by c.contract)>1
   and sum(iif(c.условие='>=70%',1,0))over(partition by c.contract)>1
   ,0,1)
18 апр 14, 17:13    [15902535]     Ответить | Цитировать Сообщить модератору
 Re: Case, count, having и условие>=2 (хитрый count)  [new]
CheaterX
Member

Откуда:
Сообщений: 61
Всем спасибо большое. С cross apply виснет правда. 20 тыс записей...
29 апр 14, 13:59    [15952032]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить