test1

-- Tworzenie tabeli urządzeń OZE

CREATE TABLE R_E_D (
    device_id NVARCHAR(26) PRIMARY KEY,
    device_status INT CHECK (device_status >= 0 AND device_status <= 6)
);

-- Wstawianie danych do tabeli urządzeń OZE
INSERT INTO R_E_D (device_id, device_status)
VALUES
('00000000000000000000000010',3),
('00000000000000000000000011',3),
('00000000000000000000000012',3),
('00000000000000000000000013',3),
('00000000000000000000000014',3),
('00000000000000000000000015',3),
('00000000000000000000000016',3),
('00000000000000000000000017',3),
('00000000000000000000000018',3),
('00000000000000000000000019',3),
('00000000000000000000000020',3),
('00000000000000000000000021',3),
('00000000000000000000000022',3),
('00000000000000000000000023',3),
('00000000000000000000000024',3),
('00000000000000000000000025',3),
('00000000000000000000000026',3),
('00000000000000000000000027',3),
('00000000000000000000000028',3),
('00000000000000000000000029',3),
('00000000000000000000000030',3),
('00000000000000000000000031',3),
('00000000000000000000000032',3),
('00000000000000000000000033',3),
('00000000000000000000000034',3),
('00000000000000000000000035',3),
('00000000000000000000000036',3),
('00000000000000000000000037',3),
('00000000000000000000000038',3),
('00000000000000000000000039',3),
('00000000000000000000000040',3),
('00000000000000000000000041',3),
('00000000000000000000000042',3),
('00000000000000000000000043',3),
('00000000000000000000000044',3),
('00000000000000000000000045',3),
('00000000000000000000000046',3),
('00000000000000000000000047',3),
('00000000000000000000000048',3),
('00000000000000000000000049',3),
('00000000000000000000000050',3);





-- Tworzenie tabeli zgłoszeń awarii

CREATE TABLE Dev_Fails (
    failure_id INT PRIMARY KEY IDENTITY,
    device_id NVARCHAR(26),
    shift_number INT,
    failure_start DATETIME,
    failure_end DATETIME,
    FOREIGN KEY (device_id) REFERENCES R_E_D(device_id)
);

-- Wstawianie danych do tabeli zgłoszeń awarii

DECLARE @i INT = 0
WHILE @i <= 9
BEGIN
    INSERT INTO Dev_Fails (device_id, shift_number, failure_start, failure_end)
    VALUES (CONCAT('0000000000000000000000000', @i), round(rand() * 2 + 1,0,0), DATEADD(DAY, -@i, GETDATE()), DATEADD(DAY, -@i+1, GETDATE()));
    SET @i = @i + 1
END

DECLARE @i INT = 10
WHILE @i <= 50
BEGIN
    INSERT INTO Dev_Fails (device_id, shift_number, failure_start, failure_end)
    VALUES (CONCAT('000000000000000000000000', @i), round(rand() * 2 + 1,0,0), DATEADD(DAY, -@i, GETDATE()), DATEADD(DAY, -@i+1, GETDATE()));
    SET @i = @i + 1
END