Hello,
I need help with the following, please: our product is reel with stamps on it and the table in SQL looks like this:
DROP TABLE IF EXISTS reel_sheets;
GO
CREATE TABLE reel_sheets(
reel_sheet_id INT IDENTITY(1,1) NOT NULL,
start_stamp BIGINT NOT NULL,
end_stamp BIGINT NOT NULL);
GO
ALTER TABLE reel_sheets
ADD CONSTRAINT reel_sheets_PK PRIMARY KEY (reel_sheet_id)
INSERT INTO reel_sheets
VALUES (1,70000),
(70001,140000)
The reels packed into inner, inners go into outers and the outers are placed onto the pallet.
The pallets are organised in so called consignments.
There is a table already in place (please, do not advise to change table's structure) and it looks almost like this (removed irrelevant columns):
DROP TABLE IF EXISTS consignment_crates;
GO
CREATE TABLE consignment_crates(
consignment_crate_id INT IDENTITY(1,1) NOT NULL,
consignment_id INT NOT NULL,
parent_id INT,
level_type CHAR(10), --PALLET OUTER INNER
pallet_id INT,
case_id INT,
box_id INT,
reel_sheet_id INT);
ALTER TABLE consignment_crates
ADD CONSTRAINT consignment_crates_PK PRIMARY KEY (consignment_crate_id);
INSERT INTO consignment_crates
VALUES (1,NULL,'PALLET',1,NULL,NULL,NULL),
(1,NULL,'PALLET',2,NULL,NULL,NULL),
(1,1,'OUTER',NULL,1,NULL,NULL),
(1,1,'OUTER',NULL,2,NULL,NULL),
(1,2,'OUTER',NULL,3,NULL,NULL),
(1,2,'OUTER',NULL,4,NULL,NULL),
(1,6,'INNER',NULL,NULL,1,NULL),
(1,6,'INNER',NULL,NULL,2,NULL),
(1,8,'REEL',NULL,NULL,NULL,1),
(1,8,'REEL',NULL,NULL,NULL,2);
I can easily produce an hierarchy and can order the objects in correct way using the following:
WITH cte_crates AS(
SELECT cc.*,
1 AS level_id,
CAST(
CAST(cc.consignment_crate_id AS BINARY(4))
AS VARBINARY(4000)) AS filter_path--Up to 1000 levels deep.
FROM consignment_crates cc
LEFT OUTER JOIN reel_sheets rs
ON rs.reel_sheet_id = cc.reel_sheet_id
WHERE cc.parent_id IS NULL
UNION ALL
SELECT cc.*,
cte.level_id +1,
CAST(cte.filter_path + CAST(cc.consignment_crate_id AS BINARY(4))
AS VARBINARY(4000))
FROM consignment_crates cc
JOIN cte_crates cte
ON cte.consignment_crate_id = cc.parent_id)
SELECT ct.*,
(rs.end_stamp - rs.start_stamp)+1 AS volume
-- LEAD(ct.filter_path) OVER (ORDER BY ct.filter_path) AS lead,
-- LAG(ct.filter_path) OVER (ORDER BY ct.filter_path ) AS lag
FROM cte_crates ct
LEFT OUTER JOIN reel_sheets rs
ON rs.reel_sheet_id = ct.reel_sheet_id
ORDER BY ct.filter_path
The new request I am struggling with is to provide accumulated volumes. From the example above you can see two reels belong to inner with consignment_crate_id of 8, so that inner should have summary of both reels, i.e. 140,000. The inner itself together with another one belong to an outer with consignment_crate_id of 6, so that outer should have summary of the two etc.
I tried with LEAD or LAG, thinking of using them in PARTITION BY but got nowhere so far.
Any help will be massively appreciated as always
November 11, 2022 at 1:30 am
Maybe you're looking for the SUM OVER the volume partitioned by the 'parent_id' column but which column is appropriate to ORDER BY? If ORDER BY the 'filter_path' column it causes this error
Msg 8729, Level 16, State 1, Line 16874
ORDER BY list of RANGE window frame has total size of 4000 bytes. Largest size supported is 900 bytes.
The other (not so good) candidate columns for ORDER BY are 'consignment_crate_id' and 'reel_sheet_id'. Id columns in general imo should be atomic and should not be assumed as monotonic (ie always increasing). Anyway, you could try something like this
WITH cte_crates AS(
SELECT cc.*,
1 AS level_id,
CAST(
CAST(cc.consignment_crate_id AS BINARY(4))
AS VARBINARY(4000)) AS filter_path--Up to 1000 levels deep.
FROM #consignment_crates cc
LEFT OUTER JOIN #reel_sheets rs
ON rs.reel_sheet_id = cc.reel_sheet_id
WHERE cc.parent_id IS NULL
UNION ALL
SELECT cc.*,
cte.level_id +1,
CAST(cte.filter_path + CAST(cc.consignment_crate_id AS BINARY(4))
AS VARBINARY(4000))
FROM #consignment_crates cc
JOIN cte_crates cte
ON cte.consignment_crate_id = cc.parent_id)
SELECT ct.*,
(rs.end_stamp - rs.start_stamp)+1 AS volume,
--sum((rs.end_stamp - rs.start_stamp)+1) OVER (partition by ct.parent_id ORDER BY ct.filter_path) AS cum_volume
sum((rs.end_stamp - rs.start_stamp)+1) OVER (partition by ct.parent_id ORDER BY rs.reel_sheet_id) AS cum_volume
FROM cte_crates ct
LEFT OUTER JOIN #reel_sheets rs
ON rs.reel_sheet_id = ct.reel_sheet_id
ORDER BY ct.filter_path;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Please see the following article to easily do exactly what you ask.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 14, 2022 at 4:49 pm
Thank you, Jeff
It was extremely helpful article.
November 14, 2022 at 6:39 pm
Thanks for the feedback, BOR15K.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply