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

Откуда:
Сообщений: 26
Добрый день!
Владею некоторыми основами программирования на t-sql, но знаний и практики пока не хватает, подскажите, пожалуйста как реализовать пример:
Есть 5 товаров из table1,остатки из table2,есть промежуток времени от сегодня до -5 дней назад, соответственно нужно вывести информацию об остатках на эти даты, например:
товар1 20150802 1шт
товар1 20150802 2шт
товар2 20150802 1 шт
и так далее.
Насколько я понимаю, нужно создать две переменные, первой(per1) присвоить значение сегодня, второй(per2) за вычетом 5 дней, и цикл
автор
WHILE per2 <= per1
DO BEGIN
{
SELECT
s.nn,
s.maincode,
(SELECT SUM(kolp) FROM table2 WHERE nn = s.nn AND priz = '+' AND date = per2 ) - (SELECT SUM(kolp) FROM table2 WHERE nn = s.nn AND priz = '-' AND date = per2) AS Ostatok
FROM table2 s;
per2=per2+ 1 день;
}

Подскажите правильный ли ход моих мыслей и как реализовать присвоение переменным сегодняшней даты и даты -5 дней?
Спасибо!
3 авг 15, 23:50    [17971578]     Ответить | Цитировать Сообщить модератору
 Re: Сформировать таблицу  [new]
Jaffar
Member

Откуда:
Сообщений: 633
Интересно,

Вот так надо, никаких циклов.
select t.ID, t.Name, o0.Rest, o1.Rest, o2.Rest, o3.Rest, o4.Rest, o5.Rest 
from Tovar t with(nolock)
outer apply(select top 1 o.Rest
			from Ostatki o with(nolock)
			where
					o.IDTovar = t.ID
			and		o.Date <= cast(getdate() as date)
			order by o.Date desc /**/) o0 -- сегдня
outer apply(select top 1 o.Rest
			from Ostatki o with(nolock)
			where
					o.IDTovar = t.ID
			and		o.Date <= cast(getdate() as date)-1
			order by o.Date desc /**/) o1 -- вчера
outer apply(select top 1 o.Rest
			from Ostatki o with(nolock)
			where
					o.IDTovar = t.ID
			and		o.Date <= cast(getdate() as date)-2
			order by o.Date desc /**/) o2 -- и т.д.
outer apply(select top 1 o.Rest
			from Ostatki o with(nolock)
			where
					o.IDTovar = t.ID
			and		o.Date <= cast(getdate() as date)-3
			order by o.Date desc /**/) o3
outer apply(select top 1 o.Rest
			from Ostatki o with(nolock)
			where
					o.IDTovar = t.ID
			and		o.Date <= cast(getdate() as date)-4
			order by o.Date desc /**/) o4
outer apply(select top 1 o.Rest
			from Ostatki o with(nolock)
			where
					o.IDTovar = t.ID
			and		o.Date <= cast(getdate() as date)-5
			order by o.Date desc /**/) o5
4 авг 15, 06:33    [17971788]     Ответить | Цитировать Сообщить модератору
 Re: Сформировать таблицу  [new]
o-o
Guest
declare @t table (p_id int, dt date, qty int, primary key(p_id, dt));
insert into @t values
(1, '20150801', 15),
(1, '20150802', 17),
(1, '20150803', 11),
(1, '20150804', 14),
(1, '20150805', 15),
(1, '20150806', 16),
(1, '20150807', 17),
(1, '20150808', 18),

(2, '20150802', 27),
(2, '20150803', 21),
(2, '20150804', 24)

declare @start_dt date = dateadd(dd, -5, cast(getdate() as date));

select *
from
	(
	select *
	from @t
	where dt >= @start_dt
	)d 
	pivot (min(qty) for dt in ([2015-08-04], [2015-08-05], [2015-08-06], [2015-08-07], [2015-08-08]))p

p_id 2015-08-04 2015-08-05 2015-08-06 2015-08-07 2015-08-08
1 14 15 16 17 18
2 24 NULL NULL NULL NULL
4 авг 15, 10:48    [17972520]     Ответить | Цитировать Сообщить модератору
 Re: Сформировать таблицу  [new]
Интересно
Member

Откуда:
Сообщений: 26
o-o,
автор
Сообщение 325, уровень 15, состояние 1, строка 25
Неправильный синтаксис около "pivot". Возможно, следует установить более высокий уровень совместимости для текущей базы данных, чтобы включить эту функцию. См. справку по параметру SET COMPATIBILITY_LEVEL инструкции ALTER DATABASE.
4 авг 15, 17:11    [17974815]     Ответить | Цитировать Сообщить модератору
 Re: Сформировать таблицу  [new]
Интересно
Member

Откуда:
Сообщений: 26
Jaffar, по сути одно и то же действие написано несколько раз, а если взять 10-15-30 дней, то такой код писать получится долго. Может все таки как то через цикл?
4 авг 15, 17:13    [17974826]     Ответить | Цитировать Сообщить модератору
 Re: Сформировать таблицу  [new]
o-o
Guest
Интересно
o-o,
автор
Сообщение 325, уровень 15, состояние 1, строка 25
Неправильный синтаксис около "pivot". Возможно, следует установить более высокий уровень совместимости для текущей базы данных, чтобы включить эту функцию. См. справку по параметру SET COMPATIBILITY_LEVEL инструкции ALTER DATABASE.

возможно, огласите версию сервера и базы?
хотя и так ясно, что у вас там COMPATIBILITY_LEVEL 80.
и в сообщении даже написано, как поменять.
но можно и в гуи сменить, если оно еще пока работает
4 авг 15, 17:37    [17974963]     Ответить | Цитировать Сообщить модератору
 Re: Сформировать таблицу  [new]
o-o
Guest
Интересно
Jaffar, по сути одно и то же действие написано несколько раз, а если взять 10-15-30 дней, то такой код писать получится долго. Может все таки как то через цикл?

вам чтоб "долго не писать" или чтоб "долго не работало"?
с пивотом, кстати, будет "2 в 1".
да еще и безвозмездно т.е. даром
4 авг 15, 17:39    [17974977]     Ответить | Цитировать Сообщить модератору
 Re: Сформировать таблицу  [new]
Интересно
Member

Откуда:
Сообщений: 26
o-o, под "долго" имел ввиду рационально ли так писать, если взять период больше. Не все понял и знаю, посижу, поразбираюсь. Возможно, ПО, для которого мне нужен код, не примет именно ваш пример, но в любом случае большое спасибо!
4 авг 15, 19:32    [17975423]     Ответить | Цитировать Сообщить модератору
 Re: Сформировать таблицу  [new]
Интересно
Member

Откуда:
Сообщений: 26
пока получилось только расположить даты
автор
DECLARE @a int, @b int
SET @a = 0
SET @b = 5;
DECLARE @tb table (fdays int,fdays2 int, fdays3 date);
declare @st date;
WHILE @a < @b
BEGIN
SET @st = dateadd(dd, -@a, cast(getdate() as date));
INSERT INTO @tb VALUES (@a,@b,@st);
SET @a = @a + 1;
END
SELECT fdays3 FROM @tb

Бьюсь над остатками
4 авг 15, 21:46    [17975868]     Ответить | Цитировать Сообщить модератору
 Re: Сформировать таблицу  [new]
o-o
Guest
да поднимите уже уровень совместимости базы,
ну не 2000-ый же сервер у вас, а то бы он и на date отреагировал.
выполните
select name, compatibility_level
from sys.databases

посмотрите на мастер, у него же не 80, а зачем 80 вашей базе?

Интересно
Возможно, ПО, для которого мне нужен код, не примет именно ваш пример

выполняет код не ПО, а сервер. в вашей базе.
к-ой надо поднять compatibility_level

но вот и вариант для динозавров:
declare @t table (p_id int, dt date, qty int, primary key(p_id, dt));
insert into @t values
(1, '20150801', 15),
(1, '20150802', 17),
(1, '20150803', 11),
(1, '20150804', 14),
(1, '20150805', 15),
(1, '20150806', 16),
(1, '20150807', 17),
(1, '20150808', 18),

(2, '20150802', 27),
(2, '20150803', 21),
(2, '20150804', 24)

declare @start_dt date = dateadd(dd, -5, cast(getdate() as date));

select p_id,
       sum(case when dt = '20150804' then qty end) as [2015-08-04],
       sum(case when dt = '20150805' then qty end) as [2015-08-05],
       sum(case when dt = '20150806' then qty end) as [2015-08-06],
       sum(case when dt = '20150807' then qty end) as [2015-08-07],
       sum(case when dt = '20150808' then qty end) as [2015-08-08]
from @t
where dt >= @start_dt
group by p_id
4 авг 15, 23:34    [17976241]     Ответить | Цитировать Сообщить модератору
 Re: Сформировать таблицу  [new]
Интересно
Member

Откуда:
Сообщений: 26
o-o, поменял, запрос сработал, но повторюсь, что мне нужно менять количество дней, возможно, на 15, возможно на 60
DECLARE @a int, @b int, @nn int
SET @a = 0 --идентификатор сегодня
SET @b = 5 -- количество дней выборки
SET @nn = (SELECT nn FROM sprnn WHERE maincode = 572); - берем пример для кода
DECLARE @tb table (fdays int,fdays2 int, fdays3 date, fdays4 int);
declare @st date;
WHILE @a < @b
BEGIN
SET @st = dateadd(dd, -@a, cast(getdate() as date));
INSERT INTO @tb VALUES (@a,@b,@st,@nn);
SET @a = @a + 1;
END
SELECT REPLACE(STR(s.fdays4, 10), ' ', '0') AS Код_товара,
s.fdays3 AS Дата, 
((SELECT (CASE WHEN SUM(kolp) IS NULL THEN '0' ELSE SUM(kolp) END) FROM reg001 WHERE priz = '+' AND nn = s.fdays4 AND DATEDIFF( day, cast(date AS date), '2014-01-01' ) <= 0 AND DATEDIFF( day, cast(date AS date),  s.fdays3 ) >= 0)-(SELECT (CASE WHEN SUM(kolp) IS NULL THEN '0' ELSE SUM(kolp) END) FROM reg001 WHERE priz = '-' AND nn = s.fdays4 AND DATEDIFF( day, cast(date AS date), '2014-01-01' ) <= 0 AND DATEDIFF( day, cast(date AS date),  s.fdays3 ) >= 0)) AS остаток_на_дату 
FROM @tb s ORDER BY fdays3

Получается примерно то, что нужно
0000000558	2015-08-01	40
0000000558	2015-08-02	40
0000000558	2015-08-03	40
0000000558	2015-08-04	40
0000000558	2015-08-05	40

Осталось реализовать цикл перебора нескольких кодов товара. В 4 строчке брал один для примера, а их количество n.
5 авг 15, 15:20    [17979032]     Ответить | Цитировать Сообщить модератору
 Re: Сформировать таблицу  [new]
o-o
Guest
Интересно,

вам что, не надо разворачивать, а просто отфильтровать по дате что ли?
и пускай так все и валится в 1 столбец?
5 авг 15, 15:36    [17979150]     Ответить | Цитировать Сообщить модератору
 Re: Сформировать таблицу  [new]
Интересно
Member

Откуда:
Сообщений: 26
o-o,

ну да, в первом посте написано как нужно, то есть
первый_товар_код начальная_дата остаток
первый_товар_код .... остаток
первый_товар_код конечная_дата остаток
второй_товар_код  - то же самое по всем датам
третий_товар и так далее


как то так...осталось только цикл по товарам...
5 авг 15, 15:44    [17979212]     Ответить | Цитировать Сообщить модератору
 Re: Сформировать таблицу  [new]
o-o
Guest
мама дорогая...
ну так фильтруйте по нужным датам и нужным товарам, в чем проблема???
declare @t table (p_id int, dt date, qty int, primary key(p_id, dt));
declare @i int = 1, @max_n int = 20; -- max product id

while @i <= @max_n
begin
	;with nums as
	(
	select number as n
	from master..spt_values
	where type = 'p' and number < 50
	)

	insert into @t (p_id, dt, qty)
	select @i, DATEADD(DD, -n, CAST(getdate() as date)),
	       abs(CHECKSUM(newid()))
	from nums
	set @i = @i + 1;
end;

select *
from @t; -- досюда мы просто генерили данные. любуемся на содеянное
---- здесь условия на фильтры:
declare @start_dt date = '2015-06-21', 
        @end_dt date = 	'2015-07-27',
        @p_id_min int = 6, @p_id_max int = 14;

select *
from  @t
where dt between @start_dt and  @end_dt
      and p_id between @p_id_min and @p_id_max
order by p_id, dt; -- отфильтровли по заказанным товарам и датам     
5 авг 15, 15:56    [17979343]     Ответить | Цитировать Сообщить модератору
 Re: Сформировать таблицу  [new]
Интересно
Member

Откуда:
Сообщений: 26
o-o,
o-o, ваш пример очень хорош!
Только ньюанс есть такой:
id_min товара я могу узнать из запроса SELECT min(nn) From..
id_max так же
но прирост кода идет не +1, в выборке может быть, например, три кода 3..65..98 и количество заранее неизвестно
соответственно, в результатах он выведет все значения от 3 до 98, а нужно брать только эти определенные кода.
6 авг 15, 14:14    [17984115]     Ответить | Цитировать Сообщить модератору
 Re: Сформировать таблицу  [new]
Интересно
Member

Откуда:
Сообщений: 26
o-o,
вот таким способом получилось. но запрос выполнялся очень долго
declare @t table (p_id int, dt date, qty int, primary key(p_id, dt));
declare @i int = (SELECT MIN(nn) FROM sprnn), @max_n int = (SELECT MAX(nn) FROM sprnn); -- max product id

while @i <= @max_n
begin
	;with nums as
	(
	select number as n
	from master..spt_values
	where type = 'p' and number < 45
	)

	insert into @t (p_id, dt, qty)
	select @i, DATEADD(DD, -n, CAST(getdate() as date)),
	       abs(CHECKSUM(newid()))
	from nums
	set @i = @i + 1;
end;

declare  @p_id_min int = (SELECT MIN(nn) FROM sprnn WHERE ex_code = 1), @p_id_max int = (SELECT MAX(nn) FROM sprnn WHERE ex_code = 1);
SELECT nn FROM sprnn WHERE ex_code = 1
select *
from @t WHERE p_id in(SELECT nn FROM sprnn WHERE ex_code = 1);
6 авг 15, 14:23    [17984180]     Ответить | Цитировать Сообщить модератору
 Re: Сформировать таблицу  [new]
o-o
Guest
Интересно,
не надо мины/максы считать, у меня там @max_n int = 20; -- max product id
не имеет отношения к запросу, это просто ограничитель, сколько продуктов я хочу нагенерить.
вас вообще не должно интересовать ничего из того, что идет до строки
---- здесь условия на фильтры:
решение оттуда начинается, а до этого я просто тестовые данные готовлю
declare @t table (p_id int, dt date, qty int, primary key(p_id, dt));
declare @i int = 1, @max_n int = 20; 

while @i <= @max_n
begin
	;with nums as
	(
	select number as n
	from master..spt_values
	where type = 'p' and number < 50
	)

	insert into @t (p_id, dt, qty)
	select @i, DATEADD(DD, -n, CAST(getdate() as date)),
	       abs(CHECKSUM(newid()))
	from nums
	set @i = @i + 1;
end;

--select *
--from @t; 
---- здесь условия на фильтры:
declare @start_dt date = '2015-06-21', 
        @end_dt date = 	'2015-07-27';

select *
from  @t
where dt between @start_dt and  @end_dt
      and p_id in (3, 65, 98)
order by p_id, dt; 


т.е вам надо @tзаменить на свою таблицу table2 и все:
declare @start_dt date = '2015-06-21', 
        @end_dt date = 	'2015-07-27';

select *
from  table2
where dt between @start_dt and  @end_dt
      and p_id in (3, 65, 98)
order by p_id, dt; 

а мне медаль за терпение
6 авг 15, 14:41    [17984304]     Ответить | Цитировать Сообщить модератору
 Re: Сформировать таблицу  [new]
Интересно
Member

Откуда:
Сообщений: 26
o-o, пошел все равно своим путем, может, конечно не совсем оптимальный код, вам большое спасибо за помощь и терпение!
declare @id int,@id_max int,@nn int,@nn0 int,@a int,@b int
declare @tbnn table (id int,nn int,date date, ost int)
declare @tbrez table (code int, date date, ost int)
declare @curs cursor
declare @dt date
declare @ost1 int, @ost2 int, @ost int

set @id = 1
set @id_max = (select COUNT(nn) from sprnn where ex_code = 1)
set @curs  = CURSOR SCROLL FOR SELECT nn FROM sprnn WHERE ex_code = 1
set @a = 0
set @b = 45
open @curs
FETCH NEXT FROM @curs INTO @nn
while @id <= @id_max
	begin
		while @a<@b
			begin
				set @dt = dateadd(dd, -@a, cast(getdate() as date))
				set @ost1 = (select SUM(kolp) from reg001 where priz = '+' and nn = @nn and DATEDIFF( day, cast(date AS date), '2014-01-01' ) <= 0 AND DATEDIFF( day, cast(date AS date),  @dt ) >= 0) 
				set @ost2 = (select SUM(kolp) from reg001 where priz = '-' and nn = @nn and DATEDIFF( day, cast(date AS date), '2014-01-01' ) <= 0 AND DATEDIFF( day, cast(date AS date),  @dt ) >= 0) 		
				set @ost = @ost1 - @ost2;				
				insert into @tbnn values (@id,@nn,@dt,@ost)
				set @a = @a + 1;
			end
		set @a = 0
		insert into @tbnn values (@id,@nn,@dt,@ost)
		set @id = @id + 1
		FETCH NEXT FROM @curs INTO @nn
	end
select * from @tbnn
6 авг 15, 18:25    [17985529]     Ответить | Цитировать Сообщить модератору
 Re: Сформировать таблицу  [new]
o-o
Guest
Картинка с другого сайта.
6 авг 15, 19:10    [17985676]     Ответить | Цитировать Сообщить модератору
 Re: Сформировать таблицу  [new]
Интересно
Member

Откуда:
Сообщений: 26
o-o, Картинка с другого сайта.
6 авг 15, 19:30    [17985728]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить