CREATE TABLE SHOPS (
SHOP INT,
DOC_NUMBER VARCHAR(100),
DAY DATE,
SHIPMENT VARCHAR(4)
);
INSERT INTO SHOPS (SHOP, DOC_NUMBER, DAY, SHIPMENT)
VALUES
(1220, '1123-4553-08', '2021-12-27', 'COU'),
(1221, '0811-4553-08', '2021-11-24', 'PNT'),
(1222, '0043-4553-14', '2021-12-25', 'LCTN'),
(1320, '2333-4553-16', '2021-10-20', 'PNT'),
(1330, '1000-4553-10', '2021-12-27', 'COU'),
(1450, '0999-4553-14', '2021-09-26', 'PNT'),
(1520, '1499-4553-08', '2021-12-22', 'COU');
CREATE TABLE WAREHOUSE (
WAREHOUSE VARCHAR(40),
ID VARCHAR(40),
DOCUMENT varchar(40),
MONTH INT
);
INSERT INTO WAREHOUSE (WAREHOUSE, ID, DOCUMENT, MONTH)
VALUES
('', '11231521', '', 0),
('', '08111221', '', 0),
('', '00431022', '', 0),
('', '23331320', '', 0),
('', '10001330', '', 0),
('', '09991450', '', 0),
('', '14991520', '', 0);
WITH T1 AS (SELECT SHOPS.SHOP FROM SHOPS JOIN WAREHOUSE
ON SUBSTRING(WAREHOUSE.ID, 5, 4) = SHOPS.SHOP)
UPDATE WAREHOUSE
SET WAREHOUSE =
CASE
WHEN WAREHOUSE.WAREHOUSE = '' AND SUBSTRING(WAREHOUSE.ID, 5, 4) IN (SELECT SHOP FROM T1)
THEN (SELECT SUBSTRING(SHOPS.DOC_NUMBER, 11, 2) FROM SHOPS WHERE SUBSTRING(WAREHOUSE.ID, 5, 4) =
SHOPS.SHOP)
END,
DOCUMENT =
CASE
WHEN WAREHOUSE.DOCUMENT = '' AND SUBSTRING(WAREHOUSE.ID, 5, 4) IN (SELECT SHOP FROM T1)
THEN (SELECT (SUBSTRING(SHOPS.DOC_NUMBER, 6, 7) || '-' || SUBSTRING(CAST(SHOPS.DAY AS CHAR), 6, 5)) FROM SHOPS WHERE SUBSTRING(WAREHOUSE.ID, 5, 4) = SHOPS.SHOP)
END,
MONTH =
CASE
WHEN WAREHOUSE.MONTH = 0 AND SUBSTRING(WAREHOUSE.ID, 5, 4) IN (SELECT SHOP FROM T1)
THEN (SELECT (SUBSTRING(CAST(SHOPS.DAY AS CHAR), 6, 2)) FROM SHOPS WHERE SUBSTRING(WAREHOUSE.ID, 5, 4)= SHOPS.SHOP)
END;
SELECT COUNT(WAREHOUSE) FROM WAREHOUSE
WHERE WAREHOUSE IS NOT NULL AND SUBSTRING(WAREHOUSE.DOCUMENT, 12, 2) >= 24;
Telegram: DB developers channel