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

Откуда:
Сообщений: 112
добрый день.

Прошу помощи в составлении запроса по истории перемещения документа

есть выборка документа нахождения на определенном складе в определенный момент времени
CREATE TABLE #sklad
(
	[ID] [uniqueidentifier] NOT NULL,
	ChangeDate NVARCHAR(100),
	NameSklad NVARCHAR(100)

)
 
INSERT INTO #sklad (ID,ChangeDate,NameSklad) VALUES 
('D4311972-3115-4D51-9D18-07D1C60E2D4C','2019-07-31 13:26:40.490','Склад 1'), 
('D4311972-3115-4D51-9D18-07D1C60E2D4C','2019-07-31 13:27:39.467','Склад 1'), 
('D4311972-3115-4D51-9D18-07D1C60E2D4C','2019-07-31 13:28:07.720','Склад 1'), 
('D4311972-3115-4D51-9D18-07D1C60E2D4C','2019-07-31 13:28:33.247','Склад 1'),
('D4311972-3115-4D51-9D18-07D1C60E2D4C','2019-07-31 13:37:05.857','Склад 1'),
('D4311972-3115-4D51-9D18-07D1C60E2D4C','2019-08-09 16:43:32.917','Склад 2'),
('D4311972-3115-4D51-9D18-07D1C60E2D4C','2019-08-14 10:03:45.410','Склад 2'),
('D4311972-3115-4D51-9D18-07D1C60E2D4C','2019-08-21 16:56:59.107','Склад 2'),
('D4311972-3115-4D51-9D18-07D1C60E2D4C','2019-08-22 13:40:19.577','Склад 3'),
('D4311972-3115-4D51-9D18-07D1C60E2D4C','2019-08-22 13:54:58.457','Склад 3'),
('D4311972-3115-4D51-9D18-07D1C60E2D4C','2019-08-22 14:05:39.293','Склад 2'),
('D4311972-3115-4D51-9D18-07D1C60E2D4C','2019-08-22 14:05:50.903','Склад 2'),
('D4311972-3115-4D51-9D18-07D1C60E2D4C','2019-08-22 14:09:00.213','Склад 2'),
('D4311972-3115-4D51-9D18-07D1C60E2D4C','2019-08-22 14:26:07.417','Склад 2'),
('D4311972-3115-4D51-9D18-07D1C60E2D4C','2019-08-23 11:07:32.107','Склад 3')

SELECT * FROM #sklad

DROP TABLE #sklad


Требуется получить выборку с движением документа по складам
Делаю так

SELECT *
,ROW_NUMBER() over (partition by ID order by ChangeDate) as [ROW_NUMBER] 
INTO #temp_sklad
FROM #sklad

SELECT * FROM #temp_sklad

SELECT *

FROM
(
	SELECT 
	ds_next.*
	,prev.[NameSklad] as PrevOwner
	,prev2.[NameSklad] as PrevPrevOwner
	,row_number() over (partition by ds_next.ID, ds_next.[NameSklad] order by ds_next.[ChangeDate] asc) rnum
	FROM  #temp_sklad ds_next
	left JOIN #temp_sklad prev ON prev.ID = ds_next.ID AND ds_next.ROW_NUMBER - 1 = prev.ROW_NUMBER AND prev.[NameSklad] != ds_next.[NameSklad]
	left JOIN #temp_sklad prev2 ON prev2.ID = prev.ID AND prev.ROW_NUMBER - 2 = prev2.ROW_NUMBER AND prev2.[NameSklad] != prev.[NameSklad]
	
)s
WHERE  s.rnum = 1 

DROP TABLE #sklad
DROP TABLE #temp_sklad 


но пропадает первый склад 1 в из истории, когда документ находиться на склад 3
ID	ChangeDate	NameSklad	ROW_NUMBER	PrevOwner	PrevPrevOwner	rnum
D4311972-3115-4D51-9D18-07D1C60E2D4C	2019-07-31 13:26:40.490	Склад 1	1	NULL	NULL	1
D4311972-3115-4D51-9D18-07D1C60E2D4C	2019-08-09 16:43:32.917	Склад 2	6	Склад 1	NULL	1
D4311972-3115-4D51-9D18-07D1C60E2D4C	2019-08-22 13:40:19.577	Склад 3	9	Склад 2	NULL	1


т.е последняя строка должна иметь вид
D4311972-3115-4D51-9D18-07D1C60E2D4C	2019-08-22 13:40:19.577	Склад 3	9	Склад 2	Склад 1	1


Спасибо.
Возможно есть другие способы ?
1 ноя 19, 13:00    [22008045]     Ответить | Цитировать Сообщить модератору
 Re: история перемещения документа  [new]
court
Member

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

;with cte as (
	select
		[ID]
		,ChangeDate
		,NameSklad
		,row_number()over(partition by [ID] order by ChangeDate) - row_number()over(partition by [ID], NameSklad order by ChangeDate) as inv
	from #sklad	 
),
cte1 as (
	select
		[ID]
		,min(ChangeDate) as ChangeDate
		,NameSklad
		,rn		=row_number()over(partition by [ID] order by min(ChangeDate))	 
	from cte
	group by
		[ID]
		,NameSklad
		,inv
)
select 
	 t1.*
	 ,t2.NameSklad as PrevOwner	
	 ,t3.NameSklad as PrevPrevOwner	

from cte1 t1
left join cte1 t2 on t1.ID = t2.ID and t1.rn = t2.rn + 1
left join cte1 t3 on t1.ID = t3.ID and t1.rn = t3.rn + 2
order by 2


IDChangeDateNameSkladrnPrevOwnerPrevPrevOwner
D4311972-3115-4D51-9D18-07D1C60E2D4C2019-07-31 13:26:40.490Склад 11NULLNULL
D4311972-3115-4D51-9D18-07D1C60E2D4C2019-08-09 16:43:32.917Склад 22Склад 1NULL
D4311972-3115-4D51-9D18-07D1C60E2D4C2019-08-22 13:40:19.577Склад 33Склад 2Склад 1
D4311972-3115-4D51-9D18-07D1C60E2D4C2019-08-22 14:05:39.293Склад 24Склад 3Склад 2
D4311972-3115-4D51-9D18-07D1C60E2D4C2019-08-23 11:07:32.107Склад 35Склад 2Склад 3
1 ноя 19, 13:55    [22008116]     Ответить | Цитировать Сообщить модератору
 Re: история перемещения документа  [new]
Remind
Member

Откуда: UK
Сообщений: 457
;WITH CTE AS
(
  SELECT 
    ID,
    NameSklad,
    ChangeDate, 
    ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ChangeDate) - ROW_NUMBER() OVER (PARTITION BY ID, NameSklad ORDER BY ChangeDate) AS GRP
  FROM #sklad
)
SELECT
  ID,
  NameSklad,
  MIN(ChangeDate) AS ChangeDate, 
  LAG(NameSklad)    OVER (PARTITION BY ID ORDER BY MIN(ChangeDate)) AS PrevSklad,
  LAG(NameSklad, 2) OVER (PARTITION BY ID ORDER BY MIN(ChangeDate)) AS PrevPrevSklad
FROM cte
GROUP BY ID, NameSklad, GRP
1 ноя 19, 20:09    [22008461]     Ответить | Цитировать Сообщить модератору
 Re: история перемещения документа  [new]
Владислав Колосов
Member

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

создайте таблицу движения документов и операцию перемещения документа. У вас изначально кривая архитектура, вы так сверку толком никогда не сделаете. Данные надо сравнивать по явным признакам, а не косвенным.
4 ноя 19, 17:16    [22009445]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить