SQL tree data rebuild

Question

Good morning, I have an issue with a SQL query on hierarchic data. I have to store the position of products on the shelfs in a supermarket. Products can be moved but I have to maintain the history in order to produce sales statistics. Records in the table are hierarchically linked and they can change some of their information and the position on the shelf. Shelf_code: identify the shelf hierarchic position (i.e. aisle, shelf, level, sector…) Shelf_position: details the location on shelf level. parentId is set to null for the first insert occurrence of a record and is set to the ancestor record id for the further versions. I’ve produced a tree structured set of records that can be shown like in the picture. At the beginning of the year the products are positioned as follows

enter image description here

Then a new brand has been inserted between B and C and C has been moved in a different position (with his children C flavour x and C flavour y) In the database new records are created for Brand C items with the new shelf code and position, parent id is now not null and corresponds to the id of the original record. When I rebuild the supermarket shelfs tree, I want to show only the new positions

enter image description here

I tried to get data using partition over Shelf_position but I get twice the records named “Flowour x” and “Flowour y”: both in the correct position but also in the previous one (so they look like being Brand New sons that isn’t correct)

enter image description here

My query:

SELECT Shelfs.*
FROM Shelfs 
WHERE  Shelfs.ID IN(
SELECT id
FROM
(SELECT m.*, ROW_NUMBER() over 
(partition by Shelf_Position order by m.time_stamp desc) as rn 
FROM Shelfs m) m2 
WHERE m2.rn = 1)                     
ORDER BY Shelf_Code, Shelf_Position

To get item genealogy of product named “Flavour x” I’ve used the query listed below. Selecting the most recent item I can get the last one

WITH Anchestors AS (
SELECT Shelfs.*
,0 AS lv
FROM Shelfs
WHERE id = @productId
UNION ALL
SELECT parent.*
,  lv+1 as lv
FROM Shelfs parent
INNER JOIN Anchestors a
ON parent.id = a.parentId
)
SELECT @rootId = ( SELECT top(1) id
FROM Anchestors
order by lv desc);
-- select root descendants
WITH generation AS (
SELECT Shelfs.*
,0 AS lv
FROM Shelfs
WHERE parentId IS NULL
and id = @rootId
UNION ALL
SELECT child.*
,  lv+1 as lv
FROM Shelfs child
INNER JOIN generation g
ON g.id = child.parentId
)
SELECT * 
FROM generation
order by lv

But this query is parametrized for a specific product and I wasn’t able to generalize it in order to get the last version of all the items. Can anyone help me to find out the correct way to get the data? Thanks in advance to everybody.

Database table script:

CREATE TABLE [dbo].[Shelfs](
[id] [uniqueidentifier] NOT NULL,
[Name] [nchar](10) NULL,
[Shelf_Code] [nvarchar](max) NULL,
[Shelf_Position] [nvarchar](max) NULL,
[parentId] [uniqueidentifier] NULL,
[time_stamp] [datetime] NULL,
CONSTRAINT [PK_Shelfs] PRIMARY KEY CLUSTERED 
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Shelfs] ADD  CONSTRAINT [DF_Shelfs_id]  DEFAULT (newid()) FOR [id]


INSERT [dbo].[Shelfs] ([id], [Name], [Shelf_Code], [Shelf_Position], [parentId], [time_stamp]) VALUES (N'043be568-8cc6-4624-9373-64d31a032745', N'Level 1   ', N'0101', N'010101', NULL, CAST(N'2022-01-01T08:00:00.000' AS DateTime))
INSERT [dbo].[Shelfs] ([id], [Name], [Shelf_Code], [Shelf_Position], [parentId], [time_stamp]) VALUES (N'27ccf84c-5a08-425b-a355-6fb1f99eda3a', N'Flavour x ', N'0101010103', N'010101010301', NULL, CAST(N'2022-01-01T08:00:00.000' AS DateTime))
INSERT [dbo].[Shelfs] ([id], [Name], [Shelf_Code], [Shelf_Position], [parentId], [time_stamp]) VALUES (N'9e841e78-571a-4009-8d40-81fc9045b99e', N'Brand C   ', N'01010101', N'0101010104', N'37035b46-6a1a-47b6-8bdc-4a6ecfd68b9e', CAST(N'2022-02-01T08:00:00.000' AS DateTime))
INSERT [dbo].[Shelfs] ([id], [Name], [Shelf_Code], [Shelf_Position], [parentId], [time_stamp]) VALUES (N'da5119e1-c327-463a-b8e9-84453a1495cc', N'2nd Aisle ', N'00', N'02', NULL, CAST(N'2022-01-01T08:00:00.000' AS DateTime))
INSERT [dbo].[Shelfs] ([id], [Name], [Shelf_Code], [Shelf_Position], [parentId], [time_stamp]) VALUES (N'b3e67cd1-c726-4bcf-a30c-9b7945a2ea9f', N'Left      ', N'01', N'0101', NULL, CAST(N'2022-01-01T08:00:00.000' AS DateTime))
INSERT [dbo].[Shelfs] ([id], [Name], [Shelf_Code], [Shelf_Position], [parentId], [time_stamp]) VALUES (N'08c2d2bb-0980-4d0d-899d-a4d23c0c7878', N'Flavour x ', N'0101010104', N'010101010401', N'27ccf84c-5a08-425b-a355-6fb1f99eda3a', CAST(N'2022-02-01T08:00:00.000' AS DateTime))
INSERT [dbo].[Shelfs] ([id], [Name], [Shelf_Code], [Shelf_Position], [parentId], [time_stamp]) VALUES (N'56825bd1-55ab-4c65-86c0-aff40550b785', N'Flavour y ', N'0101010103', N'010101010302', NULL, CAST(N'2022-01-01T08:00:00.000' AS DateTime))
INSERT [dbo].[Shelfs] ([id], [Name], [Shelf_Code], [Shelf_Position], [parentId], [time_stamp]) VALUES (N'4a8e4ef7-926c-4eca-a520-b580f7035f44', N'Right     ', N'01', N'0102', NULL, CAST(N'2022-01-01T08:00:00.000' AS DateTime))
INSERT [dbo].[Shelfs] ([id], [Name], [Shelf_Code], [Shelf_Position], [parentId], [time_stamp]) VALUES (N'c53d35bc-f31b-4a67-a9e7-b82fbae5f7dc', N'Sector 1  ', N'010101', N'01010101', NULL, CAST(N'2022-01-01T08:00:00.000' AS DateTime))
INSERT [dbo].[Shelfs] ([id], [Name], [Shelf_Code], [Shelf_Position], [parentId], [time_stamp]) VALUES (N'c70a6b81-8967-4ae8-a16c-bc6a925e1f61', N'Flavour y ', N'0101010104', N'010101010402', N'56825bd1-55ab-4c65-86c0-aff40550b785', CAST(N'2022-02-01T08:00:00.000' AS DateTime))
INSERT [dbo].[Shelfs] ([id], [Name], [Shelf_Code], [Shelf_Position], [parentId], [time_stamp]) VALUES (N'c46948f0-2d5e-48cf-a773-dcc44a434779', N'Brand B   ', N'01010101', N'0101010102', NULL, CAST(N'2022-01-01T08:00:00.000' AS DateTime))
INSERT [dbo].[Shelfs] ([id], [Name], [Shelf_Code], [Shelf_Position], [parentId], [time_stamp]) VALUES (N'63123c3e-36bf-477e-919b-f245f0ff80c2', N'Sector 2  ', N'010101', N'01010102', NULL, CAST(N'2022-01-01T08:00:00.000' AS DateTime))

Answer 1

I fancy I've found the solution for my issue. For who has interest in it, here's the query:

WITH ShelfsTree([id]
,[Name]
,[Shelf_Code]
,[Shelf_Position]
,[parentId]
,[Time_stamp]
, lv)
AS (SELECT a.[id], 
a.[Name], 
a.[Shelf_Code], 
a.[Shelf_Position], 
a.[parentId] Parent, 
a.[Time_stamp],
0 AS lv
FROM Shelfs a
WHERE a.[parentId] IS NULL
AND a.[parentId] IS NULL
UNION ALL
SELECT a2.[id], 
a2.[Name],
a2.[Shelf_Code], 
a2.[Shelf_Position], 
a2.[parentId] Parent, 
a2.[Time_stamp],
ShelfsTree.lv + 1 lv
FROM Shelfs a2
INNER JOIN ShelfsTree ON ShelfsTree.[id] = a2.[parentId]
WHERE a2.[parentId] IS NOT NULL
)
select *
from ShelfsTree
where id not in (select [parentId] from Shelfs where [parentId] is not null)
order by Shelf_Code, Shelf_Position, Time_stamp desc

Thanks.