oze3

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


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


CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255),
    phone VARCHAR(20),
    address VARCHAR(255)
);



CREATE TABLE Servicing_Companies (
    company_id INT PRIMARY KEY,
    name VARCHAR(255),
    working_hours VARCHAR(255) -- Można to dostosować w zależności od potrzeb
);


CREATE TABLE Servicing_Shifts (
    shift_number INT PRIMARY KEY,
    start_time TIME,
    end_time TIME
);





INSERT INTO Renewable_Energy_Devices (device_id, device_status)
VALUES ('12345678901234567890123456', 0),
       ('22345678901234567890123456', 1),
       ('32345678901234567890123456', 2),
       ('42345678901234567890123456', 3),
       ('52345678901234567890123456', 4);


INSERT INTO Customers (customer_id, name, email, phone, address)
VALUES (1, 'John Doe', '[email protected]', '123456789', '123 Main St'),
       (2, 'Jane Smith', '[email protected]', '987654321', '456 Elm St');



INSERT INTO Servicing_Companies (company_id, name, working_hours)
VALUES (1, 'Company Y', '6:00-14:00, 14:00-22:00, 22:00-6:00');


DECLARE @i INT = 1
WHILE @i <= 500
BEGIN
    INSERT INTO Device_Failures (device_id, shift_number, failure_start, failure_end)
    VALUES (CONCAT(@i, '2345678901234567890123456'), 1, DATEADD(DAY, -@i, GETDATE()), DATEADD(DAY, -@i+1, GETDATE()));
    SET @i = @i + 1
END



DECLARE @i INT = 1
WHILE @i <= 500
BEGIN
    INSERT INTO Device_Failures (device_id, shift_number, failure_start, failure_end)
    VALUES (CONCAT(@i, '2345678901234567890123456'), 1, DATEADD(DAY, -@i, GETDATE()), DATEADD(DAY, -@i+1, GETDATE()));
    SET @i = @i + 1
END


DECLARE @i INT = 1
WHILE @i <= 500
BEGIN
    INSERT INTO Repairs (device_id, repair_start, repair_end, repair_description)
    VALUES (CONCAT(@i, '2345678901234567890123456'), DATEADD(DAY, -@i, GETDATE()), DATEADD(DAY, -@i+1, GETDATE()), 'Naprawa urządzenia');
    SET @i = @i + 1
END



--Na jakiej zmianie dane urządzenie OZE uległo awarii:

SELECT df.device_id, df.shift_number
FROM Device_Failures df
ORDER BY df.failure_start;


--Na jakiej zmianie urządzenie OZE zostało naprawione:

SELECT r.device_id, df.shift_number
FROM Repairs r
JOIN Device_Failures df ON r.device_id = df.device_id
WHERE r.repair_start BETWEEN df.failure_start AND df.failure_end
ORDER BY r.repair_start;


--Jaki był czas przestoju danego urządzenia OZE w ciągu zadanego okresu czasu:

SELECT df.device_id, SUM(DATEDIFF(HOUR, df.failure_start, df.failure_end)) AS downtime_hours
FROM Device_Failures df
GROUP BY df.device_id;


--Jaki był czas przestoju danego urządzenia OZE w ciągu zadanego okresu czasu nie wliczając w to czasu kiedy firma serwisująca nie pracowała (weekendy):
sql

SELECT df.device_id, SUM(DATEDIFF(HOUR, df.failure_start, df.failure_end)) AS downtime_hours
FROM Device_Failures df
JOIN Servicing_Shifts ss ON df.shift_number = ss.shift_number
WHERE DATENAME(WEEKDAY, df.failure_start) NOT IN ('Saturday', 'Sunday')
GROUP BY df.device_id;


--Jaki był sumaryczny czas przestoju wszystkich urządzeń OZE w ciągu zadanego okresu czasu nie wliczając w to czasu kiedy firma serwisująca nie pracowała (weekendy):

SELECT SUM(downtime_hours) AS total_downtime_hours
FROM (
    SELECT df.device_id, SUM(DATEDIFF(HOUR, df.failure_start, df.failure_end)) AS downtime_hours
    FROM Device_Failures df
    JOIN Servicing_Shifts ss ON df.shift_number = ss.shift_number
    WHERE DATENAME(WEEKDAY, df.failure_start) NOT IN ('Saturday', 'Sunday')
    GROUP BY df.device_id
) AS downtime_per_device;



--Jaki jest procentowy udział urządzeń OZE w poszczególnych fazach awarii w stosunku do ilości wszystkich dostępnych urządzeń OZE:

WITH Device_Failure_Count AS (
    SELECT device_id, COUNT(*) AS failure_count
    FROM Device_Failures
    GROUP BY device_id
)
SELECT df.device_status, COUNT(*) AS device_count, DFC.failure_count, CAST(COUNT(*) AS DECIMAL) / DFC.failure_count * 100 AS percentage
FROM Renewable_Energy_Devices df
JOIN Device_Failure_Count DFC ON df.device_id = DFC.device_id
GROUP BY df.device_status, DFC.failure_count;


--Na ilu zmianach nie pracowało dane urządzenie OZE (wliczając to zmianę na której zgłoszono awarię i na której uruchomiono ja znów produkcyjnie):

SELECT df.device_id, COUNT(DISTINCT df.shift_number) AS downtime_shifts
FROM Device_Failures df
JOIN Servicing_Shifts ss ON df.shift_number = ss.shift_number
GROUP BY df.device_id;