OZE - raporty

--##############################################################################
--"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ł sumaryczny czas postoju wszystkich urządzeń w ciągu zadanego okresu czasu nie wliczając w to sobót i niedziel" - , należy zsumować czasy postoju dla wszystkich urządzeń w tabeli Device_Failures i odfiltrować soboty i niedziele.

-------------------------
SELECT SUM(DATEDIFF(HOUR, failure_start, failure_end)) AS TotalDowntimeInHoures
FROM Device_Failures
WHERE DATEPART(WEEKDAY, failure_start) NOT IN (1, 7)  -- 1 - niedziela, 7 - sobota
  AND DATEPART(WEEKDAY, failure_end) NOT IN (1, 7)
  AND DATEDIFF(DAY, failure_start, failure_end) >= 1;  -- Minimalnie jeden dzień trwania awarii
---------------------------

--Zapytanie wybiera sumę czasu postoju wszystkich urządzeń w tabeli Device_Failures, pomijając dni sobotnie i niedzielne oraz awarie trwające mniej niż jeden dzień. Czas postoju jest wyrażony w minutach.
--Dodatkowo, należy upewnić się, że dane w tabeli Device_Failures są poprawne i zawierają pełne informacje o czasie trwania awarii dla wszystkich urządzeń.





--##############################################################################
--"Czas przestoju danego urządzenia w ciągu zadanego okresu czasu, nie wliczając w to sobót i niedziel" - należy zidentyfikować okresy przestoju urządzenia, które nie kolidują z sobotami i niedzielami, a następnie obliczyć sumę czasu przestoju.

-----------------
DECLARE @StartDate DATETIME = '2024-01-01';  -- Data początkowa
DECLARE @EndDate DATETIME = '2024-12-31';    -- Data końcowa

SELECT device_id,
       SUM(DATEDIFF(MINUTE, failure_start, failure_end)) AS TotalDowntimeMinutes
FROM Device_Failures
WHERE failure_start >= @StartDate AND failure_end <= @EndDate
  AND DATEPART(WEEKDAY, failure_start) NOT IN (1, 7)  -- Wyklucz soboty (7) i niedziele (1)
  AND DATEPART(WEEKDAY, failure_end) NOT IN (1, 7)
GROUP BY device_id;
--------------------

--@StartDate i @EndDate określają zakres czasu, dla którego chcemy obliczyć czas przestoju.
--Warunek WHERE ogranicza wyniki do okresów przestoju, które mieszczą się w zadanym zakresie czasu i nie kolidują z sobotami i niedzielami.
--DATEDIFF(MINUTE, failure_start, failure_end) oblicza różnicę czasu między failure_start i failure_end w minutach dla każdego okresu przestoju.
--SUM agreguje te różnice czasu dla każdego urządzenia, aby obliczyć łączny czas przestoju.
--GROUP BY device_id grupuje wyniki według identyfikatora urządzenia.





--##############################################################################
--"Które urządzenia psuły się najczęściej" - należy zliczyc liczbę awarii dla każdego urządzenia i posortować wyniki w kolejności malejącej liczby awarii.

----------
SELECT device_id, COUNT(*) AS liczba_awarii
FROM Device_Failures
GROUP BY device_id
ORDER BY liczba_awarii DESC;
-----------------

--Zapytanie grupuje awarie według identyfikatora urządzenia (device_id), a następnie zlicza liczbę awarii dla każdego urządzenia. Wyniki są sortowane malejąco według liczby awarii, dzięki czemu na początku listy będą znajdować się urządzenia, które uległy największej liczbie awarii.





--##############################################################################
--"Które urządzenia miały najdłuższy czas przestoju" - możemy użyć złączenia między tabelą Renewable_Energy_Devices a Device_Failures, a następnie obliczyć czas trwania każdej awarii. Następnie możemy grupować wyniki według identyfikatora urządzenia i obliczyć sumę czasu trwania awarii dla każdego urządzenia. Na końcu możemy posortować wyniki malejąco i wybrać pierwszy rekord, który będzie miał najdłuższy czas przestoju.

-------------------------------------
SELECT TOP 1
    d.device_id,
    SUM(DATEDIFF(MINUTE, df.failure_start, df.failure_end)) AS total_downtime_minutes
FROM
    Renewable_Energy_Devices d
JOIN
    Device_Failures df ON d.device_id = df.device_id
GROUP BY
    d.device_id
ORDER BY
    total_downtime_minutes DESC;
-----------------------------------------

--Zapytanie wybierze urządzenie z najdłuższym łącznym czasem przestoju w minutach. Jeśli chcemy wyświetlić wynik w innej jednostce czasu, np. godzinach, wystarczy dostosować funkcję DATEDIFF i wynik uzyskany z grupowania.





--##############################################################################
--"Ile jest procent urządzeń w poszczególnych fazach awarii w stosunku do ilości wszystkich urządzeń", możemy użyć złączenia tabel Renewable_Energy_Devices i Device_Failures, a następnie wykorzystać agregację i funkcje analityczne do obliczenia procentowego udziału urządzeń w poszczególnych fazach awarii.

--------------------------------------------------
SELECT
    device_status,
    COUNT(*) AS devices_count,
    CAST(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER () AS DECIMAL(10,2)) AS percentage
FROM
    Renewable_Energy_Devices RED
JOIN
    Device_Failures DF ON RED.device_id = DF.device_id
GROUP BY
    device_status;
--------------------------------------------------

--Zpytanie łączy tabelę Renewable_Energy_Devices z tabelą Device_Failures za pomocą kolumny device_id.
Grupuje wyniki według statusu urządzenia (device_status).
--Oblicza liczbę urządzeń w każdej fazie awarii za pomocą funkcji agregującej COUNT(*).
--Wykorzystuje funkcję analityczną SUM(COUNT(*)) OVER (), aby obliczyć sumę wszystkich urządzeń.
--Oblicza procentowy udział urządzeń w poszczególnych fazach awarii.
--Powyższe zapytanie zwróci wyniki, w których każdy wiersz będzie zawierał status urządzenia, liczbę urządzeń w danej fazie awarii oraz procentowy udział tych urządzeń w stosunku do wszystkich urządzeń.





--##############################################################################
--"Na ilu zmianach nie pracowała maszyna (wliczając to zmianę, na której zgłoszono awarię i na której uruchomiono ją znów produkcyjnie)" - musimy policzyć ilość unikalnych zmian, w których urządzenie znajdowało się w fazie awarii. Wykorzystamy złączenie między tabelą Servicing_Shifts a Device_Failures oraz funkcję COUNT(DISTINCT shift_number) do zliczenia unikalnych numerów zmian.

-----------------------------
SELECT
    COUNT(DISTINCT SS.shift_number) AS number_of_shifts_without_device,
    COUNT(*) AS total_shifts
FROM
    Servicing_Shifts SS
LEFT JOIN
    Device_Failures DF ON SS.shift_number = DF.shift_number
WHERE
    DF.failure_id IS NULL OR DF.failure_end IS NOT NULL;
---------------------------------

--Zapytanie łączy tabelę Servicing_Shifts z tabelą Device_Failures za pomocą kolumny shift_number.
--Wykorzystuje LEFT JOIN, aby uwzględnić wszystkie zmiany, niezależnie od tego, czy były związane z awariami.
--Wykorzystuje warunek DF.failure_id IS NULL OR DF.failure_end IS NOT NULL, aby zliczyć tylko te zmiany, w których urządzenie było sprawne (czyli nie było awarii) lub awaria już się zakończyła.
--Używa funkcji COUNT(DISTINCT SS.shift_number) do zliczenia unikalnych numerów zmian, w których urządzenie nie było w fazie awarii.
--Używa funkcji COUNT(*) do zliczenia wszystkich zmian.
--Powyższe zapytanie zwróci wyniki, w których number_of_shifts_without_device będzie zawierać liczbę zmian, w których urządzenie było sprawne, a total_shifts będzie zawierać liczbę wszystkich zmian. Dzięki temu można obliczyć procent zmian, w których urządzenie nie pracowało.





--##############################################################################
--"Ile jest zamówień do realizacji?" - musimy zliczyć liczbę zamówień, których data zakończenia jest późniejsza niż aktualna data.

------------------------------------
SELECT COUNT(*) AS liczba_zamowien_do_realizacji
FROM Customers_Orders
WHERE customer_order_date_end > GETDATE();
-------------------------------------

--Zapytanie wybiera liczbę wierszy z tabeli Customers_Orders.
--Używa warunku WHERE customer_order_date_end > GETDATE(), aby zliczyć tylko te zamówienia, których data zakończenia jest późniejsza niż aktualna data.
--Zapytanie zwróci liczbę zamówień do realizacji.





--##############################################################################
--"Czy jest możliwe przyjęcie zamówienia - czy uruchomione urządzenia będą wstanie wygenerować oczekiwaną energię" - Aby sprawdzić, czy istnieje możliwość przyjęcia zamówienia dla każdego zamówienia, musimy zsumować moc generowaną przez urządzenia, których status jest równy 0 (czyli działających) i porównać ją z oczekiwaną ilością energii w zamówieniach.

-------------------------------------------------------------
DECLARE @oczekiwana_energia DECIMAL(18,0);
DECLARE @customer_order_id INT;
DECLARE @wygenerowana_energia DECIMAL(18,0);

DECLARE orders_cursor CURSOR FOR
SELECT customer_order_id, customer_order_amount_of_energy
FROM Customers_Orders;

OPEN orders_cursor;
FETCH NEXT FROM orders_cursor INTO @customer_order_id, @oczekiwana_energia;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @wygenerowana_energia = NULL;

    SELECT @wygenerowana_energia = SUM(device_power)
    FROM Renewable_Energy_Devices
    WHERE device_status = 0;

    IF (@wygenerowana_energia >= @oczekiwana_energia)
        PRINT 'Możliwe jest przyjęcie zamówienia o numerze ' + CAST(@customer_order_id AS VARCHAR(50));
    ELSE
        PRINT 'Nie można przyjąć zamówienia o numerze ' + CAST(@customer_order_id AS VARCHAR(50)) + ' - brak wystarczającej ilości energii';

    FETCH NEXT FROM orders_cursor INTO @customer_order_id, @oczekiwana_energia;
END

CLOSE orders_cursor;
DEALLOCATE orders_cursor;
---------------------------------------------------------------------

--Zapytanie deklaruje zmienną @oczekiwana_energia, która przechowuje oczekiwaną ilość energii w zamówieniu.
--Deklaruje zmienną @customer_order_id, która przechowuje identyfikator zamówienia.
--Deklaruje zmienną @wygenerowana_energia, która przechowuje sumaryczną moc urządzeń odnawialnych, które są w stanie wygenerować energię.
--Tworzy kursor, aby iterować przez wszystkie zamówienia w tabeli Customers_Orders.
--Dla każdego zamówienia z tabeli, oblicza sumaryczną moc urządzeń o statusie 0 (działających) z tabeli Renewable_Energy_Devices.
--Porównuje sumaryczną moc urządzeń z oczekiwaną ilością energii w zamówieniu.
--Wyświetla odpowiedni komunikat dla każdego zamówienia w zależności od wyniku porównania.





--##############################################################################
--"Czy awaria danego urządzenia zagraża czasom poprawnej realizacji już zgłoszonych zleceń" - użycie mechanizmu triggera. Trigger pozwala na automatyczne wykonanie określonych działań w odpowiedzi na określone zdarzenia, takie jak dodanie, aktualizacja lub usunięcie danych w tabeli. Trigger ten jest wyzwalany po wstawieniu lub aktualizacji wiersza w tabeli Device_Failures, który wskazuje na awarię urządzenia. Sprawdza on, czy istnieją zlecenia klientów, których daty realizacji kolidują z czasem trwania awarii tego urządzenia. Jeśli takie zlecenia istnieją, trigger wyświetla komunikat o potencjalnym zagrożeniu.

------------------------
CREATE TRIGGER CheckOrderCompletionTime
ON Device_Failures
AFTER INSERT, UPDATE
AS
BEGIN
    DECLARE @device_id NVARCHAR(26);
    DECLARE @failure_start DATETIME;
    DECLARE @failure_end DATETIME;
    DECLARE @order_id INT;

    -- Pobierz dane dotyczące nowej awarii
    SELECT @device_id = device_id, @failure_start = failure_start, @failure_end = failure_end
    FROM inserted;

    -- Sprawdź, czy istnieją zlecenia klientów, których daty realizacji kolidują z czasem trwania awarii urządzenia
    IF EXISTS (
        SELECT 1
        FROM Customers_Orders
        WHERE (customer_order_date_start BETWEEN @failure_start AND @failure_end
            OR customer_order_date_end BETWEEN @failure_start AND @failure_end
            OR @failure_start BETWEEN customer_order_date_start AND customer_order_date_end
            OR @failure_end BETWEEN customer_order_date_start AND customer_order_date_end)
            -- AND device_id = @device_id   -- tu trzeba by jeszcze uzupełnić tabelę zamówień o pole device_id (mapowanie zamówienia do konkretnego urządzenia)
    )
    BEGIN
        PRINT 'Uwaga! Awaria urządzenia ' + @device_id + ' może zagrażać czasom poprawnej realizacji zleceń.';
    END
END;
-------------------------------

--Trigger można dostosować do konkretnych potrzeb i warunków biznesowych, które mają być uwzględnione podczas sprawdzania zagrożenia dla realizacji zleceń.