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

Есть таблица t (t1 date, is_weekend tinyint, some_value int) , где есть непрерывная последовательность дней и признак, выходной это или нет, а так же некоторый показатель.

Как одним апдейтом (или несколькими, но без написания хранимых процедур) перенести значения этого показателя с выходного дня на ближайший понедельник, прибавив к имеющимся?

Было

пятница 8
суббота 3
воскресенье 4
понедельник 5

Станет

пятница 8
суббота 0
воскресенье 0
понедельник 12


Спасибо!
2 июн 11, 21:45    [10755884]     Ответить | Цитировать Сообщить модератору
 Re: помогите плиз с update  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
DECLARE	@Table TABLE (
	 [Date]		Date	PRIMARY KEY
	,[WeekEnd]	Bit	NOT NULL
	,[Value]	Int	NOT NULL
)INSERT	@Table VALUES
 ('20110603',0,8)
,('20110604',1,3)
,('20110605',1,4)
,('20110605',0,5)
--------------------------------------------------------------------------------
;WITH Move AS (
	SELECT	U.*
	FROM	     @Table	W
		CROSS APPLY (
			SELECT	Top(1)
			FROM	@Table	D
			WHERE	    D.WeekEnd = 0
				AND D.[Date] > W.[Date]
			ORDER BY D.[Date]
		) D CROSS APPLY (
			SELECT	W.[Date], W.Value AS Old, 0/*W.Value - W.Value*/AS New
		UNION ALL
			SELECT	D.[Date], D.Value AS Old,    D.Value + W.Value  AS New
		) U
	WHERE	    W.WeekEnd  = 1
		AND W.[Value] != 0
)/*	UPDATE	Move
	SET	Old = New
*/	UPDATE	T
	SET	Value = M.New
	FROM	     Move	M
		JOIN @Table	T ON T.ID = M.ID
Это?
Можно упростить.
2 июн 11, 22:34    [10756113]     Ответить | Цитировать Сообщить модератору
 Re: помогите плиз с update  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Mnior
	UPDATE	T
	SET	Value = M.New
	FROM	     Move	M
		JOIN @Table	T ON T.[Date] = M.[Date]
2 июн 11, 22:39    [10756127]     Ответить | Цитировать Сообщить модератору
 Re: помогите плиз с update  [new]
Newi4ok
Guest
Mnior,

спасибо, данные за один выходной пропали в примере, но принцип понятен
3 июн 11, 16:44    [10760510]     Ответить | Цитировать Сообщить модератору
 Re: помогите плиз с update  [new]
korneyr
Member

Откуда:
Сообщений: 6
Newi4ok,
Прошу прощения, но вставлю свои пять копеек. Показалась интересная задачка, попробовал. Вроде работает с переменным числом выходных(MS SQL Server 2008R2), если че не так буду признателен за критику("век живи, век учись")
DECLARE	@Table TABLE (
	 [Date]		Date	PRIMARY KEY
	,[WeekEnd]	Bit	NOT NULL
	,[Value]	Int	NOT NULL
)INSERT	@Table VALUES
 ('20110602',0,8)
,('20110603',1,7) 
,('20110604',1,4)
,('20110605',1,4)
,('20110606',0,10)
,('20110607',0,10)
,('20110608',1,8)
,('20110609',1,9)
,('20110610',0,5)
,('20110611',0,8)
,('20110612',1,9)
,('20110613',0,5)
,('20110614',0,10);
select * from @Table;
With Move1 as(SELECT Old, new, u.Date as date1
	FROM @Table	W
		CROSS APPLY ((
			SELECT Top(1)*
			FROM @Table	D
			WHERE	    D.WeekEnd = 0
				AND D.[Date] > W.[Date] AND w.WeekEnd = 1 AND W.[Value] != 0
			ORDER BY D.[Date]
		) D CROSS APPLY (
			SELECT	W.[Date], W.Value AS Old, 0 AS New
		UNION ALL
			SELECT	D.[Date], D.Value AS Old,    D.Value + W.Value  AS New
		) U )),
move2 as(
		Select date1 as date,COUNT(date1) as countWK,SUM(old)/COUNT(date1) as oldvalue,SUM(new) as newsum  from Move1 group by date1)
/*	Select * from move2 as M JOIN @Table	T ON  T.[Date] = M.[Date];
*/	
UPDATE	T
	SET	Value= M.newsum-M.oldvalue*(countWK-1)  from move2 as M JOIN @Table	T ON  T.[Date] = M.[Date];
	
select * from @Table;	
5 июн 11, 11:38    [10764521]     Ответить | Цитировать Сообщить модератору
 Re: помогите плиз с update  [new]
korneyr
Member

Откуда:
Сообщений: 6
Newi4ok,
Хотя так наверное попроще:
DECLARE	@Table TABLE (
	 [Date]		Date	PRIMARY KEY
	,[WeekEnd]	Bit	NOT NULL
	,[Value]	Int	NOT NULL
)INSERT	@Table VALUES
 ('20110602',0,8)
,('20110603',1,7) 
,('20110604',1,4)
,('20110605',1,4)
,('20110606',0,10)
,('20110607',0,10)
,('20110608',1,8)
,('20110609',1,9)
,('20110610',0,5)
,('20110611',0,8)
,('20110612',1,9)
,('20110613',0,5)
,('20110614',0,10);
SELECT * FROM @Table;
With Move1 AS(SELECT New, u.[Date] as [Date]
	FROM @Table	W
		CROSS APPLY ((
			SELECT Top(1)*
			FROM @Table	D
			WHERE	    D.WeekEnd = 0
				AND D.[Date] > W.[Date] AND w.WeekEnd = 1 AND W.[Value] != 0
			ORDER BY D.[Date]
		) D CROSS APPLY (
			SELECT	W.[Date], W.Value AS Old, -W.Value AS New
		UNION ALL
			SELECT	D.[Date], D.Value AS Old, W.Value  AS New
		) U )),
Move2 AS(
		SELECT [Date],SUM(New) AS NewSum  FROM Move1 GROUP BY [Date])
/*	
	SELECT * FROM Move2 AS M JOIN @Table T ON  T.[Date] = M.[Date];
*/UPDATE	T
	SET	Value= Value+M.NewSum  FROM move2 AS M JOIN @Table	T ON  T.[Date] = M.[Date];
	
SELECT * FROM @Table;
5 июн 11, 14:24    [10764829]     Ответить | Цитировать Сообщить модератору
 Re: помогите плиз с update  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Newi4ok
спасибо, данные за один выходной пропали в примере, но принцип понятен
Да, чёрт возми, уже 2-й раз подряд наступаю на вем известные грабли. Не к добру это.
Естественно надо было сгруппировать.
DECLARE	@Table TABLE (
	 [Date]		Date	PRIMARY KEY
	,[WeekEnd]	Bit	NOT NULL
	,[Value]	Int	NOT NULL
)INSERT	@Table VALUES
 ('20110603',0,8)
,('20110604',1,3)
,('20110605',1,4)
,('20110605',0,5)
--------------------------------------------------------------------------------
;WITH Move AS (
	SELECT	 U.[Date]
		,Sum(U.Value)	AS Value
	FROM	     @Table	W
		CROSS APPLY (
			SELECT	Top(1)
			FROM	@Table	D
			WHERE	    D.WeekEnd = 0
				AND D.[Date] > W.[Date]
			ORDER BY D.[Date]
		) D CROSS APPLY (
			SELECT	W.[Date], -W.Value
		UNION ALL
			SELECT	D.[Date], +W.Value
		) U ([Date],Value)
	WHERE	    W.WeekEnd  = 1
		AND W.[Value] != 0
	GROUP BY U.[Date]
)	UPDATE	T
	SET	Value += M.Value
	FROM	     Move	M
		JOIN @Table	T ON T.ID = M.ID
И всё равно можно упростить.
5 июн 11, 16:13    [10765091]     Ответить | Цитировать Сообщить модератору
 Re: помогите плиз с update  [new]
bilov
Member

Откуда: Санкт-Петербург
Сообщений: 498
Mnior, точнее наверное

DECLARE	@Table TABLE (
	 [Date]		Date	PRIMARY KEY
	,[WeekEnd]	Bit	NOT NULL
	,[Value]	Int	NOT NULL
)INSERT	@Table VALUES
 ('20110603',0,8)
,('20110604',1,3)
,('20110605',1,4)
,('20110605',0,5)
--------------------------------------------------------------------------------
;WITH Move([Date],Value) AS (
	SELECT	 U.[Date]
		,Sum(U.Value)	AS Value
	FROM	     @Table	W
		CROSS APPLY (
			SELECT	Top(1)
			FROM	@Table	D
			WHERE	    D.WeekEnd = 0
				AND D.[Date] > W.[Date]
			ORDER BY D.[Date]
		) D CROSS APPLY (
			SELECT	W.[Date], -W.Value
		UNION ALL
			SELECT	D.[Date], +W.Value
		) U 
	WHERE	    W.WeekEnd  = 1
		AND W.[Value] != 0
	GROUP BY U.[Date]
)	UPDATE	T
	SET	Value += M.Value
	FROM	     Move	M
		JOIN @Table	T ON T.[Date] = M.[Date]
5 июн 11, 18:16    [10765367]     Ответить | Цитировать Сообщить модератору
 Re: помогите плиз с update  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
DECLARE	@Table TABLE
(
	 [Date]		Date	PRIMARY KEY
	,[WeekEnd]	Bit	NOT NULL
	,[Value]	Int	NOT NULL
)

INSERT	@Table VALUES
 ('20110602',0,8)
,('20110603',1,7) 
,('20110604',1,4)
,('20110605',1,4)
,('20110606',0,10)
,('20110607',0,10)
,('20110608',1,8)
,('20110609',1,9)
,('20110610',0,5)
,('20110611',0,8)
,('20110612',1,9)
,('20110613',0,5)
,('20110614',0,10)

;with x as
(
 select
  d.[Date], sum(t.Value) as v
 from
  @Table t cross apply
  (select top (1) [Date] from @Table where WeekEnd = 0 and [Date] > t.[Date] order by [Date]) as d
 where
  t.WeekEnd = 1
 group by
  d.[Date]
 
 union all
 
 select
  [Date], -Value
 from
  @Table
 where
  WeekEnd = 1
)
update t
 set
  Value += x.v
from
 x join
 @Table t on t.[Date] = x.[Date]

select * from @Table
5 июн 11, 21:01    [10765821]     Ответить | Цитировать Сообщить модератору
 Re: помогите плиз с update  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
invm, надо ещё добавить:
AND [Value] != 0

Можно ещё фильтрованные индекс повесить с условием:
CREATE UNIQUE INDEX ... WHERE (WeekEnd = 1 AND Value != 0)
Чтоб не сканить всю таблицу.

Ещё вариант - повесить триггер который автоматически распределяет данные. Но лучше всего сразу же данные ложить в нужную строку и не играться.

А если меняется статус WeekEnd, то ... хе-хе-хе, всё идёт к чертям. Надо заново брать первичные данные с нуля и перераспределять.
6 июн 11, 01:09    [10766664]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить