Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Запрос с проверкой истории  [new]
RIBor
Member

Откуда:
Сообщений: 43
Коллеги, приветствую и прошу помощи.
Ситуация обычная: доступ к таблице только на чтение, запрет что-либо создавать на сервере, бардак в данных.
Нужно написать отчёт, который бы выдал все закрытые номера за указанный месяц (октябрь).
Вот пример таблицы и данных. В комментах указал, что должно попасть в отчёт
create table #qwe (num int, crt_date date, upd_date date, stat varchar(10))

insert into #qwe values
-- Влючено: создан в сентябре, закрыт в октябре
(1, '2014-09-20', '2014-10-02', 'close'),
-- Влючено: создан в октябре, закрыт в октябре
(2, '2014-10-01', '2014-10-01', 'open'),
(2, '2014-10-01', '2014-10-25', 'close'),
-- Влючено: переоткрыт в сентябре, закрыт в октябре
(3, '2014-09-05', '2014-09-05', 'open'),
(3, '2014-09-05', '2014-09-21', 'close'),
(3, '2014-09-05', '2014-09-22', 'reopen'),
(3, '2014-09-05', '2014-10-03', 'close'),
-- Не влючено: создан и закрыт в сентябре
(4, '2014-09-01', '2014-09-01', 'open'),
(4, '2014-09-01', '2014-09-17', 'close'),
(4, '2014-09-01', '2014-09-29', 'close'),
(4, '2014-09-01', '2014-10-01', 'close'),
(4, '2014-09-01', '2014-10-10', 'close'),
-- Не влючено: создан и закрыт в сентябре
(5, '2014-09-22', '2014-09-22', 'open'),
(5, '2014-09-22', '2014-09-24', 'close'),
-- Влючено: создан в сентябре, закрыт в октябре
(6, '2014-09-29', '2014-09-29', 'open'),
(6, '2014-09-29', '2014-10-05', 'close'),
(6, '2014-09-29', '2014-10-11', 'close'),
-- Не влючено: создан в октябре, закрыт в ноябре
(7, '2014-10-13', '2014-10-13', 'open'),
(7, '2014-10-13', '2014-11-03', 'close'),
-- Влючено: создан и закрыт в октябре, переоткрыт в ноябре
(8, '2014-10-20', '2014-10-20', 'open'),
(8, '2014-10-20', '2014-10-25', 'close'),
(8, '2014-10-20', '2014-11-06', 'reopen'),
-- Не влючено: создан в сентябре, закрыт в ноябре
(9, '2014-09-13', '2014-09-13', 'open'),
(9, '2014-09-13', '2014-11-05', 'close'),
-- Не влючено: переоткрыт в октябре
(10, '2014-10-20', '2014-10-20', 'open'),
(10, '2014-10-20', '2014-10-25', 'close'),
(10, '2014-10-20', '2014-10-28', 'reopen')


В принципе, запрос достаточно прост
select top 1 with ties num from #qwe where month(upd_date) = 10 
	order by row_number() over (partition by num order by upd_date desc), iif(stat = 'close', 1, 2)


Но. Как из него исключить номер 4? И при этом номер 6 должен остаться.
Если бы была возможность написать функцию, то никаких проблем бы не возникло, но как это сделать одним запросом?

Поигрался с созданием различных меток, но не сильно помогло
select 
	num, crt_date, upd_date, datediff(mm, '2014-10-31', upd_date) as dif_date, stat, 
	iif(stat = 'close', 1, 2) as stat_num,
	case 
		when stat = 'close' and month(upd_date) = 10 then 1
		when stat = 'close' and month(upd_date) < 10 then 2
		else 0 
	end as sost1,
	row_number() over (partition by num order by upd_date desc) as rn
from #qwe 
where num in (
	select top 1 with ties num from #qwe where month(upd_date) = 10 
	order by row_number() over (partition by num order by upd_date desc), iif(stat = 'close', 1, 2)
	) 
	and month(upd_date) <= 10
order by num, rn


Подскажите, пожалуйста, решение.
21 ноя 14, 11:52    [16884060]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с проверкой истории  [new]
aleks2
Guest
Фсе ванги форума уже устали полировать хрустальные шары.
21 ноя 14, 12:04    [16884223]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с проверкой истории  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
RIBor
Но. Как из него исключить номер 4? И при этом номер 6 должен остаться.


-- Не влючено: создан и закрыт в сентябре
(4, '2014-09-01', '2014-09-01', 'open'),
(4, '2014-09-01', '2014-09-17', 'close'),
(4, '2014-09-01', '2014-09-29', 'close'),
(4, '2014-09-01', '2014-10-01', 'close'),
(4, '2014-09-01', '2014-10-10', 'close'),
-- Влючено: создан в сентябре, закрыт в октябре
(6, '2014-09-29', '2014-09-29', 'open'),
(6, '2014-09-29', '2014-10-05', 'close'),
(6, '2014-09-29', '2014-10-11', 'close'),

Чем ети 2 запипи отличаються кроме того что вам надо 6 а не 4-ю ?
Обе открыты в сентябре , и обе на отктябрь закрыты....
Исчите первую запись со статусом close и проеряйте , чтоб она была в том же месяце что и отчетный период...Хотя почему тогда в октябрь должна попасть запись нумбер 6 ????
21 ноя 14, 12:08    [16884286]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с проверкой истории  [new]
iiyama
Member

Откуда:
Сообщений: 642
RIBor,
Нужны документы закрытые на сейчас и имеющие операцию закрытия в октябре?
21 ноя 14, 12:12    [16884321]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с проверкой истории  [new]
RIBor
Member

Откуда:
Сообщений: 43
Номер 4 не должен попасть, потому что изначально он был уже закрыт в сентябре (первая запись со статусом "close") и все последующие записи - глюки системы.
Номер 6 должен попасть, так как первая (и последующая) запись со статусом "close" в октябре.
21 ноя 14, 12:16    [16884378]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с проверкой истории  [new]
aleks2
Guest
iiyama
RIBor,
Нужны документы закрытые на сейчас и имеющие операцию закрытия в октябре?

Так ничестно! Ты должен угадать.
21 ноя 14, 12:17    [16884389]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с проверкой истории  [new]
RIBor
Member

Откуда:
Сообщений: 43
Нужны документы, действующие на 1 октября или созданные в октябре, и закрытые в октябре.
21 ноя 14, 12:19    [16884405]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с проверкой истории  [new]
iiyama
Member

Откуда:
Сообщений: 642
RIBor,

А если документ на 1 октября закрыт( например в сентябре) , 2 октября reopen и закрыт 3 октября - он нужен?
21 ноя 14, 12:26    [16884480]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с проверкой истории  [new]
RIBor
Member

Откуда:
Сообщений: 43
iiyama, да нужен (как номер 3).
Ща, сформулирую окончательно:
нужно выбрать документы, которые действовали (открыты или переоткрыты) на 1 октября, или появились (открыты или переоткрыты) в октябре, и которые закрыты в октябре.
По-этому номер 4 не должен попасть, т.к. он уже был не действующим на 1 октября, а номер 6 должен - был действующим на 1 октября.
Номер 10 не попадает, т.к. он, хотя и закрывался в октябре, но потом вновь стал действующим в октябре же.
21 ноя 14, 12:37    [16884586]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с проверкой истории  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
with x as
(
 select
  a.*,
  row_number() over (partition by a.num order by a.upd_date desc) as rn
 from
  #qwe a outer apply
  (select top (1) stat from #qwe where num = a.num and upd_date < a.upd_date order by upd_date desc) b
 where
  isnull(b.stat, '') <> a.stat
)
select
 num, crt_date, upd_date, stat
from
 x
where
 upd_date >= '20141001' and upd_date < '20141101' and
 rn = 1 and stat = 'close'
order by
 num;
21 ноя 14, 12:40    [16884612]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с проверкой истории  [new]
RIBor
Member

Откуда:
Сообщений: 43
invm, чуть-чуть не то. Не попал номер 8, т.к. интересует только октябрь, а то, что это номер переоткрылся в ноябре не важно.
Но идея понятна, попробую докрутить, спасибо!
21 ноя 14, 13:01    [16884772]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с проверкой истории  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
RIBor
Не попал номер 8, т.к. интересует только октябрь, а то, что это номер переоткрылся в ноябре не важно.
Условие
upd_date >= '20141001' and upd_date < '20141101'
перенесите в CTE
21 ноя 14, 13:15    [16884877]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить