SQLize | PHPize | SQLtest

-- Создание таблицы SHOPS CREATE TABLE IF NOT EXISTS SHOPS ( SHOP VARCHAR(30), DOC_NUMBER VARCHAR(30), DAY DATE, SHIPMENT VARCHAR(30), PRIMARY KEY (SHOP) ); -- Вставка данных в таблицу SHOPS 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'); -- Создание таблицы WAREHOUSE CREATE TABLE IF NOT EXISTS WAREHOUSE ( WAREHOUSE VARCHAR(30), ID VARCHAR(30), DOCUMENT VARCHAR(30), MONTH INT, PRIMARY KEY (ID) ); -- Вставка данных в таблицу WAREHOUSE 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) ELSE WAREHOUSE.WAREHOUSE END; WITH T1 AS ( SELECT SHOPS.SHOP FROM SHOPS JOIN WAREHOUSE ON SUBSTRING(WAREHOUSE.ID, 5, 4) = SHOPS.SHOP ) UPDATE WAREHOUSE SET 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) ELSE WAREHOUSE.DOCUMENT END; WITH T1 AS ( SELECT SHOPS.SHOP FROM SHOPS JOIN WAREHOUSE ON SUBSTRING(WAREHOUSE.ID, 5, 4) = SHOPS.SHOP ) UPDATE WAREHOUSE SET 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) ELSE WAREHOUSE.MONTH END; SELECT COUNT(WAREHOUSE) FROM WAREHOUSE WHERE WAREHOUSE IS NOT NULL AND SUBSTRING(WAREHOUSE.DOCUMENT, 12, 2) >= 24;