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

Откуда:
Сообщений: 14
select ProductID, StandardCost from Production.ProductCostHistory
inner join ProductId on Production.ProductCostHistory.p = ProductID.p
join ( select p.ProductID, max(StartDate) as StartDate from Production.ProductCostHistory
where (EndDate is NULL or EndDate > getdate()) group by ProductID ) t on t.ProductID = p.ProductID
and t.StartDate = p.StartDate
order by p.ProductID
14 ноя 17, 10:17    [20951572]     Ответить | Цитировать Сообщить модератору
 Re: выявить товары которые снизились, как сделать ее правильно ?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 5214
belu4ano-kg,

запросы пишутся на основании:
1. данных
2. ожидаемого результата

где это?
14 ноя 17, 11:29    [20951879]     Ответить | Цитировать Сообщить модератору
 Re: выявить товары которые снизились, как сделать ее правильно ?  [new]
Andy_OLAP
Member

Откуда: я знаю, что Зенит - чемпион
Сообщений: 1190
belu4ano-kg
select ProductID, StandardCost from Production.ProductCostHistory
inner join ProductId on Production.ProductCostHistory.p = ProductID.p
join ( select p.ProductID, max(StartDate) as StartDate from Production.ProductCostHistory
where (EndDate is NULL or EndDate > getdate()) group by ProductID ) t on t.ProductID = p.ProductID
and t.StartDate = p.StartDate
order by p.ProductID

Для начала наверное так.
select
t1.ProductID
,t1.StandardCost
from Production.ProductCostHistory t1 -- история ценников на номенклатуру
inner join ProductId t2
on t1.p = t2.p -- странная привязка по столбцу p, хотя ID товара в столбце ProductID
inner join (
select p.ProductID, max(StartDate) as StartDate
from Production.ProductCostHistory
where (EndDate is NULL or EndDate > getdate()) 
group by ProductID -- здесь ищем последнюю дату для открытого ценника, у него диапазон или открыт, или в будущем
) t 
on t.ProductID = p.ProductID and t.StartDate = p.StartDate
order by p.ProductID

Теперь по задаче. Перевожу с русского на программистский :)
Есть 2 последних диапазона, которых можно получить оконными фунциями. Последняя StartDate и предпоследняя StartDate. Если ценник для товара в предпоследней больше, чем в последней - значит, цена товара снизилась.
И нужно вывести перечень таких товаров, последний ценник и предпоследний ценник.
14 ноя 17, 12:06    [20952102]     Ответить | Цитировать Сообщить модератору
 Re: выявить товары которые снизились, как сделать ее правильно ?  [new]
Andy_OLAP
Member

Откуда: я знаю, что Зенит - чемпион
Сообщений: 1190
belu4ano-kg,

Теперь уточняющий вопрос. У Вас два пересекающихся диапазона, одинаковая максимальная дата StartDate. У одной записи например пустой EndDate, у другой EndDate на 31-12-2017.

"select p.ProductID, max(StartDate) as StartDate from Production.ProductCostHistory
where (EndDate is NULL or EndDate > getdate()) group by ProductID" - вот этот подзапрос вернет 2 записи по одному ProductID.
Что делать будете, коллега? Есть однозначный признак того, какая цена для 2-3 строк с одинаковой StartDate считается самой-самой-самой последней?
14 ноя 17, 12:22    [20952165]     Ответить | Цитировать Сообщить модератору
 Re: выявить товары которые снизились, как сделать ее правильно ?  [new]
belu4ano-kg
Member

Откуда:
Сообщений: 14
Andy_OLAP спасибо
14 ноя 17, 12:36    [20952222]     Ответить | Цитировать Сообщить модератору
 Re: выявить товары которые снизились, как сделать ее правильно ?  [new]
belu4ano-kg
Member

Откуда:
Сообщений: 14
Владислав Колосов,
на основании данных
14 ноя 17, 12:38    [20952226]     Ответить | Цитировать Сообщить модератору
 Re: выявить товары которые снизились, как сделать ее правильно ?  [new]
Andy_OLAP
Member

Откуда: я знаю, что Зенит - чемпион
Сообщений: 1190
belu4ano-kg
Andy_OLAP спасибо

Знаете, вот очень коряво и медленно, просто чтобы подтолкнуть Вас в сторону правильного решения.
Без оконных функций, универсальный запрос. С правилом, что любой ценник за нужную дату для товара нас устроит.
select
normal.[ProductID]
,normal.[last_StandardCost] -- ценник открытого диапазона
,normal.[prev_StandardCost] -- ценник закрытого диапазона, который обязательно больше
from
(
	select
	tt1.[ProductID]
	,tt1.[last_date]
	,(select top 1 t3.[StandardCost] from Production.ProductCostHistory t3
	where convert(date,t3.[StartDate] = tt1.[last_date] and t3.[ProductID] = tt1.[ProductID]
	) as [last_StandardCost] -- а вот тут берем первый попавшийся ценник для товара в дату
	,(select top 1 t4.[StandardCost] from Production.ProductCostHistory t4
	where (convert(date,t4.[StartDate] < tt1.[last_date] and t4.[ProductID] = tt1.[ProductID]
	and t4.[EndDate] is NOT NULL and t4.[EndDate] <= getdate()))
	order by t4.[StartDate] DESC -- а вот тут идем вниз к ближайшей предыдущей дате
	) as [prev_StandardCost] -- а вот тут берем первый попавшийся ценник для товара в дату
	from
	(
	-- дата последнего назначения цены для товара
	select t1.[ProductID]
	,max(convert(date,t1.[StartDate])) as [last_date]
	-- обязательно конвертируем в дату
	-- потому что могут быть часы и минуты с секундами в столбце
	from Production.ProductCostHistory t1
	where ([EndDate] is NULL or [EndDate] > getdate()) 
	group by t1.[ProductID]
	) tt1
	inner join (select t2.[ProductID]
	from Production.ProductCostHistory t2
	where (t2.[EndDate] is NOT NULL and t2.[EndDate] <= getdate()) 
	-- сразу ограничиваем товарами, где есть закрытые диапазоны
	-- то есть предыдущие ценники
	group by t2.[ProductID]
	) tt2 on tt1.[ProductID] = tt2.[ProductID]
) as normal
where normal.[last_StandardCost] < normal.[prev_StandardCost]
14 ноя 17, 14:40    [20952844]     Ответить | Цитировать Сообщить модератору
 Re: выявить товары которые снизились, как сделать ее правильно ?  [new]
Andy_OLAP
Member

Откуда: я знаю, что Зенит - чемпион
Сообщений: 1190
belu4ano-kg,

И заметьте - я не учитывал вполне реальные ситуации с мусорными (логически) строками, когда сначала идет StartDate (например, 1 января) при пустой EndDate, а затем StartDate (например, 2 января) при закрытой EndDate (3 января).
То есть по сути сейчас действует ценник 1 января, а не 2-го, потому что более поздний уже закрыт.
Но это на совести тех, кто загружает ценники в таблицу ProductCostHistory.
14 ноя 17, 14:47    [20952867]     Ответить | Цитировать Сообщить модератору
 Re: выявить товары которые снизились, как сделать ее правильно ?  [new]
Andy_OLAP
Member

Откуда: я знаю, что Зенит - чемпион
Сообщений: 1190
Andy_OLAP,

Это я к тому, что тогда в строке 1 января должно быть EndDate 2 января, а существовать открытая строка (пустой EndDate) для StartDate 4 января. Это в теории, а на практике любую таблицу нужно рассматривать как кучу мусорных строк.
И еще нюанс, автор темы - у Вас в таблице может быть свалка ценников на магазины, тогда для любой даты из двух Вы берете не первый попавшийся top 1, а максимальный ценник, а далее сравниваете 2 максимальных и думаете - если он снизился, значит, по всей сети магазинов пошло снижение цены, нужно такие товары выводить в список.
14 ноя 17, 14:51    [20952883]     Ответить | Цитировать Сообщить модератору
 Re: выявить товары которые снизились, как сделать ее правильно ?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 5214
belu4ano-kg,

каких данных? Телепатией тут, видимо, только Andy_OLAP страдает.
14 ноя 17, 15:13    [20952987]     Ответить | Цитировать Сообщить модератору
 Re: выявить товары которые снизились, как сделать ее правильно ?  [new]
Andy_OLAP
Member

Откуда: я знаю, что Зенит - чемпион
Сообщений: 1190
Владислав Колосов
belu4ano-kg,

каких данных? Телепатией тут, видимо, только Andy_OLAP страдает.

Не страдаю, а наслаждаюсь.
Да и потом я не могу сказать, что лежит у автора темы в таблице ProductId и что за столбец p.
И почему столбец p в таблице ProductCostHistory отличается от столбца ProductId в ней же, если внимательно посмотреть на самое первое сообщение в этой теме.
14 ноя 17, 15:59    [20953216]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить