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

Откуда: г. Наро-Фоминск, МО
Сообщений: 98
Добрый день, коллеги.
Помогите пожалуйста с запросом.
Дано:
--	Справочник товаров
declare @good int
set @good = 1
create table #goods (idGood int)

while @good < 8
begin
	insert into #goods (idGood)
	select @good
	set @good = @good + 1
end

--	Справочник дат
declare @date date
set @date = '20150831'
create table #dates (DayID date)

while @date < '20150908'
begin
	insert into #dates (DayID)
	select @date
	set @date = dateadd(day, 1, @date)
end

--	Таблица с остатками товаров
create table #t (id int, idGood int, sDate date, Qty int)
insert into #t
select 1, 1, '20150831', 2
union
select 2, 1, '20150902', 3
union
select 3, 1, '20150905', 1
union
select 4, 2, '20150902', 0
union
select 5, 2, '20150907', 1
union
select 6, 7, '20150902', 14

Как видно, в исходной таблице остатки хранятся не за все дни и не по всем товарам.
Нужно вывести последний известный остаток по каждому товару.
За определенный период это сделать просто:
select *
from (	select *
			,dense_rank() over (partition by idGood order by sDate desc) ran
		from #t)a
where ran = 1

Но как быть, если нужно вывести такой остаток на каждый день?
Т.е. по товару 1, например, результат должен быть такой:

Дата qty
20150831 2
20150901 2
20150902 3
20150903 3
20150904 3
20150905 1
20150906 1
20150907 1

Пробовала так, но не срабатывает, там где пропуск в данных более одного дня, что и логично:
--	Данные по датам
;with cte
as
(select d.DayID, g.idGood, t.id, t.Qty
from #dates d
	cross join #goods g
	left join #t t on d.DayID = t.sDate and g.idGood = t.idGood)

select c.DayID, c.idGood, isnull(c.Qty, t.Qty) as Qty, *
from cte c
	left join cte t on c.idGood = t.idGood and c.DayID = dateadd(day, 1, t.DayID)
order by c.idGood, c.DayID

Намекните куда копать.
7 сен 15, 14:22    [18118713]     Ответить | Цитировать Сообщить модератору
 Re: Данные из предыдущих дат  [new]
Shakill
Member

Откуда: мск
Сообщений: 1880
Nika1979
Намекните куда копать.

берете опорную таблицу-календарь и уже к ней присоединяете все что надо
7 сен 15, 14:32    [18118797]     Ответить | Цитировать Сообщить модератору
 Re: Данные из предыдущих дат  [new]
Nika1979
Member

Откуда: г. Наро-Фоминск, МО
Сообщений: 98
Shakill,

В моем запросе, используется таблица-календарь:

--	Справочник дат
declare @date date
set @date = '20150831'
create table #dates (DayID date)

while @date < '20150908'
begin
	insert into #dates (DayID)
	select @date
	set @date = dateadd(day, 1, @date)
end


Но не могу понять, как она тут может помочь.
7 сен 15, 15:42    [18119397]     Ответить | Цитировать Сообщить модератору
 Re: Данные из предыдущих дат  [new]
Glory
Member

Откуда:
Сообщений: 104760
Nika1979
Но не могу понять, как она тут может помочь.

Используйте ее в запросе. Вы же сами сказали, что вам нужно " вывести такой остаток на каждый день? "
Вот для "каждого дня" и ищите соотвествующий ему остаток
7 сен 15, 15:45    [18119417]     Ответить | Цитировать Сообщить модератору
 Re: Данные из предыдущих дат  [new]
Nika1979
Member

Откуда: г. Наро-Фоминск, МО
Сообщений: 98
Glory,

Она используется в запросе. В финальном запросе выводятся все даты. Но если остатка нет, то он ищется в предыдущем дне. А вот как смотреть в еще более ранний день, если и в предыдущем нет данных, я не могу сообразить.
7 сен 15, 16:08    [18119622]     Ответить | Цитировать Сообщить модератору
 Re: Данные из предыдущих дат  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10234
Блог
Nika1979
А вот как смотреть в еще более ранний день, если и в предыдущем нет данных, я не могу сообразить.

смотреть в самом близком дне от текущего
7 сен 15, 16:10    [18119639]     Ответить | Цитировать Сообщить модератору
 Re: Данные из предыдущих дат  [new]
Glory
Member

Откуда:
Сообщений: 104760
Nika1979
А вот как смотреть в еще более ранний день, если и в предыдущем нет данных, я не могу сообразить.

Вы знаете, что кроме сравнение на равно(=) можно еще сравнивать на больше(>), меньше(<)б больше-равно ( >=) и меньше-равно(<=) ?
7 сен 15, 16:10    [18119649]     Ответить | Цитировать Сообщить модератору
 Re: Данные из предыдущих дат  [new]
ГуЗы
Guest
Nika1979,
max(RestDate)<=CalendarDate
7 сен 15, 16:12    [18119673]     Ответить | Цитировать Сообщить модератору
 Re: Данные из предыдущих дат  [new]
iap
Member

Откуда: Москва
Сообщений: 47000
Nika1979, может, как-то так?
FROM Calendar C
OUTER APPLY(SELECT TOP(1) ... FROM [Table] T WHERE T.[Date]<=C.[Date] ORDER BY T.[Date] DESC) D
7 сен 15, 16:14    [18119690]     Ответить | Цитировать Сообщить модератору
 Re: Данные из предыдущих дат  [new]
_human
Member

Откуда:
Сообщений: 560
+ офтоп
Nika1979
А вот как смотреть в еще более ранний день

не все могут это делать. вернее смотреть могул не только лишь все, мало кто может (с) моск
7 сен 15, 17:01    [18119968]     Ответить | Цитировать Сообщить модератору
 Re: Данные из предыдущих дат  [new]
Nika1979
Member

Откуда: г. Наро-Фоминск, МО
Сообщений: 98
Нда.., что-то подтупливаю, а ведь еще только понедельник :-(

В итоге сделал так:

--	Данные по всем датам
select d.DayID, g.idGood, t.id, t.Qty
into #ttt
from #dates d
	cross join #goods g
	left join #t t on d.DayID = t.sDate and g.idGood = t.idGood

--	Финальная выборка
select *
from (	select c.DayID, c.idGood
			,isnull(c.Qty, t.Qty) as Qty
			,dense_rank() over (partition by c.idGood, c.DayID order by t.DayID desc) as ran
		from #ttt c
			left join #ttt t on c.idGood = t.idGood and c.DayID > t.DayID
				and t.Qty is not null)a
where ran = 1
order by a.idGood, a.DayID

Всем спасибо!
7 сен 15, 17:11    [18120016]     Ответить | Цитировать Сообщить модератору
 Re: Данные из предыдущих дат  [new]
гр к
Guest
Nika1979
Но как быть, если нужно вывести такой остаток на каждый день?
Т.е. по товару 1, например, результат должен быть такой:

Дата qty
20150831 2
20150901 2
20150902 3
20150903 3
20150904 3
20150905 1
20150906 1
20150907 1

...
Намекните куда копать.

Да?
select q.*,z.qty
from #dates q
cross apply 
(select top (1) qty from  #t a
where a.idGood = 1 and a.sDate <=q.DayID
order by sDate desc) z

ЗЗЫ Нужно использовать в скриптах вар-т для младших версий, у меня 2005-й, там нет типа дэйт. Пишите в следующий раз вар-т скрипта, годный для 2005.
7 сен 15, 17:20    [18120059]     Ответить | Цитировать Сообщить модератору
 Re: Данные из предыдущих дат  [new]
Nika1979
Member

Откуда: г. Наро-Фоминск, МО
Сообщений: 98
гр к,

Товар 1, был только как пример. Вывести нужно все. У меня так и не получилось в этом случае допилить скрипт с apply. Хотя и очень хочется.
7 сен 15, 17:26    [18120083]     Ответить | Цитировать Сообщить модератору
 Re: Данные из предыдущих дат  [new]
iap
Member

Откуда: Москва
Сообщений: 47000
Nika1979
гр к,

Товар 1, был только как пример. Вывести нужно все. У меня так и не получилось в этом случае допилить скрипт с apply. Хотя и очень хочется.
Скроссджойнить #dates с (SELECT DISTINCT idGood FROM #t).
Привязать подзапрос в APPLY() с внешним подзапросом по товарам (a.idGood = 1 убрать).
7 сен 15, 17:34    [18120138]     Ответить | Цитировать Сообщить модератору
 Re: Данные из предыдущих дат  [new]
гр к
Guest
Nika1979
гр к,

Товар 1, был только как пример. Вывести нужно все. У меня так и не получилось в этом случае допилить скрипт с apply. Хотя и очень хочется.

Наверное скроссджойнить сначала справочник дат с дистинктом товаров на остатках.
И скросс эпплаить этот q:
where a.idGood = q.idGood and a.sDate <=q.DayID
order by a.sDate desc)
7 сен 15, 17:40    [18120172]     Ответить | Цитировать Сообщить модератору
 Re: Данные из предыдущих дат  [new]
гр к
Guest
iap
Скроссджойнить #dates с (SELECT DISTINCT idGood FROM #t).
Привязать подзапрос в APPLY() с внешним подзапросом по товарам (a.idGood = 1 убрать).

Опять я выглядит, что я сплагиатил(
7 сен 15, 17:43    [18120192]     Ответить | Цитировать Сообщить модератору
 Re: Данные из предыдущих дат  [new]
iap
Member

Откуда: Москва
Сообщений: 47000
гр к
iap
Скроссджойнить #dates с (SELECT DISTINCT idGood FROM #t).
Привязать подзапрос в APPLY() с внешним подзапросом по товарам (a.idGood = 1 убрать).

Опять я выглядит, что я сплагиатил(
Да нет. Просто всё просто, очевидно, стандартно.
8 сен 15, 09:59    [18121681]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить