View difference between Paste ID: j3E7eJPM and TtL05teE

 -- Tworzenie tabeli urządzeń OZE
 CREATE TABLE Renewable_Energy_Devices (
     device_id NVARCHAR(26) PRIMARY KEY,
     device_status INT CHECK (device_status >= 0 AND device_status <= 6),
     device_power DECIMAL(18,0),
-	device_power DECIMAL(18,0),
+    company_id INT,
-	company_id INT
+    device_location GEOGRAPHY, -- Nowa kolumna przechowująca współrzędne geograficzne urządzenia
     device_location_geom GEOMETRY, -- Nowa kolumna przechowująca współrzędne geometryczne urządzenia
     device_hierarchy HIERARCHYID, -- Nowa kolumna przechowująca hierarchię urządzeń
--- Wstawianie danych do tabeli urządzeń OZE
+    device_description XML -- Nowa kolumna opisująca urządzenia
-INSERT INTO Renewable_Energy_Devices (device_id, device_status, device_power, company_id)
+
 
-('00000000000000000000000000',0,	1000,1),
+-- Tworzenie zmiennych dla współrzędnych geograficznych i geometrycznych
-('00000000000000000000000001',0,	1000,1),
+DECLARE @latitude DECIMAL(9, 6), @longitude DECIMAL(9, 6);
-('00000000000000000000000002',0,	1000,1),
+DECLARE @point GEOMETRY;
-('00000000000000000000000003',1,	2000,1),
+DECLARE @hierarchy HIERARCHYID;
-('00000000000000000000000004',0,	1000,1),
+DECLARE @device_description XML;
-('00000000000000000000000005',0,	1000,1),
+
-('00000000000000000000000006',0,	1000,1),
+-- Pętla wypełniająca tabelę
-('00000000000000000000000007',3,	1000,1),
+DECLARE @i INT = 1;
-('00000000000000000000000008',0,	5000,1),
+WHILE @i <= 50
-('00000000000000000000000009',0,	1000,1),
+
-('00000000000000000000000010',2,	1000,1),
+    -- Generowanie losowych danych dla każdej kolumny
-('00000000000000000000000011',0,	1000,1),
+    SET @latitude = RAND() * 180 - 90;
-('00000000000000000000000012',4,	1000,1),
+    SET @longitude = RAND() * 360 - 180;
-('00000000000000000000000013',0,	1000,1),
+    SET @point = GEOMETRY::Point(@latitude, @longitude, 4326);
-('00000000000000000000000014',0,	1000,1),
+    SET @hierarchy = HIERARCHYID::GetRoot();
-('00000000000000000000000015',2,	4000,1),
+    SET @device_description = '<description>Device ' + CAST(@i AS NVARCHAR(5)) + '</description>';
-('00000000000000000000000016',1,	1000,1),
+
-('00000000000000000000000017',0,	1000,1),
+    -- Wstawianie danych do tabeli
-('00000000000000000000000018',0,	1000,1),
+    INSERT INTO Renewable_Energy_Devices (device_id, device_status, device_power, company_id, device_location, device_location_geom, device_hierarchy, device_description)
-('00000000000000000000000019',0,	1000,1),
+    VALUES (
-('00000000000000000000000020',6,	7000,1),
+        'Device_' + CAST(@i AS NVARCHAR(5)), -- device_id
-('00000000000000000000000021',0,	1000,1),
+        CAST(RAND() * 6 AS INT), -- device_status
-('00000000000000000000000022',0,	1000,1),
+        CAST(RAND() * 1000 AS DECIMAL(18, 0)), -- device_power
-('00000000000000000000000023',0,	1000,1),
+        CAST(RAND() * 10 AS INT) + 1, -- company_id
-('00000000000000000000000024',4,	1000,1),
+        GEOGRAPHY::Point(@latitude, @longitude, 4326), -- device_location
-('00000000000000000000000025',0,	1000,1),
+        @point, -- device_location_geom
-('00000000000000000000000026',0,	1000,1),
+        @hierarchy.GetDescendant(NULL, NULL), -- device_hierarchy
-('00000000000000000000000027',0,	1000,1),
+        @device_description -- device_description
-('00000000000000000000000028',0,	1000,1),
+    );
-('00000000000000000000000029',0,	1000,1),
+
-('00000000000000000000000030',0,	1000,1),
+    SET @i = @i + 1;
-('00000000000000000000000031',2,	8000,1),
+END;
-('00000000000000000000000032',0,	1000,1),
+
-('00000000000000000000000033',3,	1000,1),
+
-('00000000000000000000000034',0,	1000,1),
+
-('00000000000000000000000035',0,	1000,1),
+
-('00000000000000000000000036',4,	1000,1),
+
-('00000000000000000000000037',0,	1000,1),
+
-('00000000000000000000000038',0,	1000,1),
+
-('00000000000000000000000039',0,	1000,1),
+
-('00000000000000000000000040',0,	1000,1),
+
-('00000000000000000000000041',5,	1000,1),
+
-('00000000000000000000000042',0,	1000,1),
+
-('00000000000000000000000043',0,	1000,1),
+
-('00000000000000000000000044',4,	1000,1),
+
-('00000000000000000000000045',0,	1000,1),
+
-('00000000000000000000000046',3,	9000,1),
+
-('00000000000000000000000047',0,	1000,1),
+
-('00000000000000000000000048',2,	1000,1),
+-- Zmienna do przechowywania liczby zgłoszeń awarii
-('00000000000000000000000049',0,	1000,1),
+DECLARE @numFailures INT = 50;
-('00000000000000000000000050',3,	7000,1);
+
 -- Pętla wstawiająca przykładowe zgłoszenia awarii
 DECLARE @j INT = 1;
 WHILE @j <= @numFailures
 BEGIN
     -- Losowanie daty początkowej i końcowej awarii w zakresie ostatniego miesiąca
     DECLARE @startDate DATETIME = DATEADD(DAY, -RAND()*30, GETDATE());
     DECLARE @endDate DATETIME = DATEADD(HOUR, RAND()*24, @startDate);
 
     -- Losowanie numeru zmiany
     DECLARE @shiftNumber INT = ROUND(RAND() * 2 + 1, 0, 0);
 
     -- Losowanie ID urządzenia
     DECLARE @deviceID NVARCHAR(26) = 'Device_' + CAST(ROUND(RAND()*50 + 1, 0, 0) AS NVARCHAR(2));
 
     -- Wstawienie danych do tabeli zgłoszeń awarii
     INSERT INTO Device_Failures (device_id, shift_number, failure_start, failure_end)
     VALUES (@deviceID, @shiftNumber, @startDate, @endDate);
 
     -- Inkrementacja licznika
-    VALUES (CONCAT('0000000000000000000000000', @i), round(rand() * 2 + 1,0,0), DATEADD(DAY, -@i, GETDATE()), DATEADD(DAY, -@i+1, GETDATE()));
+    SET @j = @j + 1;
 END;
 
 
 -- Tworzenie tabeli klientów
 
 CREATE TABLE Customers (
     customer_id INT PRIMARY KEY,
-    VALUES (CONCAT('000000000000000000000000', @j), round(rand() * 2 + 1,0,0), DATEADD(DAY, -@j, GETDATE()), DATEADD(DAY, -@j+1, GETDATE()));
+
     email VARCHAR(255),
     phone VARCHAR(20),
     address VARCHAR(255)
 );
 
 -- Wstawianie danych do tabeli klientów
 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'),
 (3, 'Michael Johnson', '[email protected]', '555123456', '789 Oak St'),
 (4, 'Emily Brown', '[email protected]', '444987654', '321 Pine St'),
 (5, 'William Wilson', '[email protected]', '789456123', '654 Birch St'),
 (6, 'Olivia Taylor', '[email protected]', '987123456', '987 Maple St'),
 (7, 'James Martinez', '[email protected]', '321654987', '159 Cedar St'),
 (8, 'Emma Anderson', '[email protected]', '666333999', '753 Walnut St'),
 (9, 'Noah Thomas', '[email protected]', '222333444', '852 Spruce St'),
 (10, 'Ava Hernandez', '[email protected]', '111222333', '369 Cherry St'),
 (11, 'Sophia Lopez', '[email protected]', '777888999', '741 Elm St'),
 (12, 'Matthew Gonzalez', '[email protected]', '222111333', '258 Oak St'),
 (13, 'Isabella Perez', '[email protected]', '666777888', '951 Pine St'),
 (14, 'Jacob Robinson', '[email protected]', '999888777', '753 Maple St'),
 (15, 'Ethan Lee', '[email protected]', '111222333', '852 Cedar St'),
 (16, 'Oliver Walker', '[email protected]', '444555666', '963 Walnut St'),
 (17, 'Daniel Hall', '[email protected]', '888777666', '357 Birch St'),
 (18, 'Liam Young', '[email protected]', '999888777', '159 Spruce St'),
 (19, 'Charlotte Hernandez', '[email protected]', '333222111', '456 Cherry St'),
 (20, 'Amelia Moore', '[email protected]', '222333444', '789 Elm St'),
 (21, 'Benjamin Nelson', '[email protected]', '444555666', '357 Pine St'),
 (22, 'Lucas Hill', '[email protected]', '111222333', '852 Maple St'),
 (23, 'Mia King', '[email protected]', '555666777', '963 Cedar St'),
 (24, 'Harper Baker', '[email protected]', '777888999', '357 Walnut St'),
 (25, 'Evelyn Adams', '[email protected]', '999888777', '456 Birch St');
 
 INSERT INTO Customers (customer_id, name, email, phone, address)
 VALUES (26, 'John Doe', '[email protected]', '123456789', '556 Main St'),
        (27, 'Jane Smith', '[email protected]', '987654321', '431 Elm St');
 
 
 -- Tworzenie tabeli zamówień
 
 CREATE TABLE Customers_Orders (
     customer_order_id INT PRIMARY KEY,
 	customer_id INT,
 	customer_order_date_start DATETIME,
 	customer_order_date_end DATETIME,
 	customer_order_amount_of_energy DECIMAL(18,0),
 	FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
 );
 
 -- Wstawianie danych do tabeli zamówień
 
 INSERT INTO Customers_Orders (customer_order_id, customer_id, customer_order_date_start, customer_order_date_end, customer_order_amount_of_energy)
 VALUES
 (1, 1, '2024-04-01 08:00:00', '2024-04-01 12:00:00', 100),
 (2, 1, '2024-04-02 10:00:00', '2024-04-02 14:00:00', 120),
 (3, 2, '2024-04-03 12:00:00', '2024-04-03 16:00:00', 150),
 (4, 2, '2024-04-04 09:00:00', '2024-04-04 13:00:00', 130),
 (5, 3, '2024-04-05 11:00:00', '2024-04-05 15:00:00', 110),
 (6, 3, '2024-04-06 08:00:00', '2024-04-06 12:00:00', 140),
 (7, 4, '2024-04-07 10:00:00', '2024-04-07 14:00:00', 125),
 (8, 4, '2024-04-08 12:00:00', '2024-04-08 16:00:00', 105),
 (9, 5, '2024-04-09 07:00:00', '2024-04-09 11:00:00', 115),
 (10, 5, '2024-04-10 11:00:00', '2024-04-10 15:00:00', 135),
 (11, 6, '2024-04-11 09:00:00', '2024-04-11 13:00:00', 155),
 (12, 6, '2024-04-12 08:00:00', '2024-04-12 12:00:00', 125),
 (13, 7, '2024-04-13 10:00:00', '2024-04-13 14:00:00', 145),
 (14, 7, '2024-04-14 11:00:00', '2024-04-14 15:00:00', 120),
 (15, 8, '2024-04-15 12:00:00', '2024-04-15 16:00:00', 130),
 (16, 8, '2024-04-16 08:00:00', '2024-04-16 12:00:00', 110),
 (17, 9, '2024-04-17 09:00:00', '2024-04-17 13:00:00', 140),
 (18, 9, '2024-04-18 10:00:00', '2024-04-18 14:00:00', 150),
 (19, 10, '2024-04-19 08:00:00', '2024-04-19 12:00:00', 120),
 (20, 10, '2024-04-20 11:00:00', '2024-04-20 15:00:00', 130),
 (21, 11, '2024-04-21 10:00:00', '2024-04-21 14:00:00', 140),
 (22, 11, '2024-04-22 12:00:00', '2024-04-22 16:00:00', 160),
 (23, 12, '2024-04-23 09:00:00', '2024-04-23 13:00:00', 125),
 (24, 12, '2024-04-24 07:00:00', '2024-04-24 11:00:00', 135),
 (25, 13, '2024-04-25 11:00:00', '2024-04-25 15:00:00', 115),
 (26, 13, '2024-04-26 12:00:00', '2024-04-26 16:00:00', 105),
 (27, 14, '2024-04-27 10:00:00', '2024-04-27 14:00:00', 145),
 (28, 14, '2024-04-28 08:00:00', '2024-04-28 12:00:00', 125),
 (29, 15, '2024-04-29 11:00:00', '2024-04-29 15:00:00', 135),
 (30, 15, '2024-04-30 09:00:00', '2024-04-30 13:00:00', 155),
 (31, 16, '2024-05-01 08:00:00', '2024-05-01 12:00:00', 125),
 (32, 16, '2024-05-02 10:00:00', '2024-05-02 14:00:00', 145),
 (33, 17, '2024-05-03 12:00:00', '2024-05-03 16:00:00', 130),
 (34, 17, '2024-05-04 09:00:00', '2024-05-04 13:00:00', 140),
 (35, 18, '2024-05-05 11:00:00', '2024-05-05 15:00:00', 150),
 (36, 18, '2024-05-06 08:00:00', '2024-05-06 12:00:00', 120),
 (37, 19, '2024-05-07 10:00:00', '2024-05-07 14:00:00', 110),
 (38, 19, '2024-05-08 12:00:00', '2024-05-08 16:00:00', 130),
 (39, 20, '2024-05-09 07:00:00', '2024-05-09 11:00:00', 140),
 (40, 20, '2024-05-10 11:00:00', '2024-05-10 15:00:00', 150),
 (41, 21, '2024-05-11 09:00:00', '2024-05-11 13:00:00', 125),
 (42, 21, '2024-05-12 08:00:00', '2024-05-12 12:00:00', 135),
 (43, 22, '2024-05-13 10:00:00', '2024-05-13 14:00:00', 115),
 (44, 22, '2024-05-14 11:00:00', '2024-05-14 15:00:00', 105),
 (45, 23, '2024-05-15 12:00:00', '2024-05-15 16:00:00', 145),
 (46, 23, '2024-05-16 08:00:00', '2024-05-16 12:00:00', 125),
 (47, 24, '2024-05-17 09:00:00', '2024-05-17 13:00:00', 135),
 (48, 24, '2024-05-18 10:00:00', '2024-05-18 14:00:00', 145),
 (49, 25, '2024-05-19 08:00:00', '2024-05-19 12:00:00', 155),
 (50, 25, '2024-05-20 11:00:00', '2024-05-20 15:00:00', 125);
 
 
 
 -- Tworzenie tabeli firm serwisowych
 
 CREATE TABLE Servicing_Companies (
     company_id INT PRIMARY KEY,
     name VARCHAR(255),
     working_hours VARCHAR(255)
 );
 
 -- Wstawianie danych do tabeli firm serwisowych
 
 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');
 
 
 ----------
 ALTER TABLE dbo.Renewable_Energy_Devices
 ADD CONSTRAINT FK_OZE_SC FOREIGN KEY (company_id) REFERENCES Servicing_Companies(company_id);
 ----------
 
 -- Tworzenie tabeli zmian serwisowych
 
 CREATE TABLE Servicing_Shifts (
     shift_number INT PRIMARY KEY IDENTITY,
     shift_date DATE,
     shift_type INT CHECK (shift_type >= 1 AND shift_type <= 3),
     start_time TIME,
     end_time TIME
 );
 
 -- Wstawianie danych do tabeli zmian serwisowych (poniedziałek - sobota)
 INSERT INTO Servicing_Shifts (shift_date, shift_type, start_time, end_time) VALUES
 ('2024-04-01', 1, '06:00:00', '14:00:00'),
 ('2024-04-01', 2, '14:00:00', '22:00:00'),
 ('2024-04-01', 3, '22:00:00', '06:00:00'),
 ('2024-04-02', 1, '06:00:00', '14:00:00'),
 ('2024-04-02', 2, '14:00:00', '22:00:00'),
 ('2024-04-02', 3, '22:00:00', '06:00:00'),
 ('2024-04-03', 1, '06:00:00', '14:00:00'),
 ('2024-04-03', 2, '14:00:00', '22:00:00'),
 ('2024-04-03', 3, '22:00:00', '06:00:00'),
 ('2024-04-04', 1, '06:00:00', '14:00:00'),
 ('2024-04-04', 2, '14:00:00', '22:00:00'),
 ('2024-04-04', 3, '22:00:00', '06:00:00'),
 ('2024-04-05', 1, '06:00:00', '14:00:00'),
 ('2024-04-05', 2, '14:00:00', '22:00:00'),
 ('2024-04-05', 3, '22:00:00', '06:00:00'),
 ('2024-04-06', 1, '06:00:00', '14:00:00'),
 ('2024-04-06', 2, '14:00:00', '22:00:00'),
 ('2024-04-06', 3, '22:00:00', '06:00:00');
 
 
 
 -- Tworzenie tabeli napraw
 
  CREATE TABLE Repairs (
     repair_id INT PRIMARY KEY IDENTITY,
     device_id NVARCHAR(26),
     repair_start DATETIME,
     repair_end DATETIME,
     repair_description NVARCHAR(255),
     FOREIGN KEY (device_id) REFERENCES Renewable_Energy_Devices(device_id)
 );
 
 -- Wstawianie danych do tabeli napraw  (można kilka razy)
 
 DECLARE @i INT = 0
 WHILE @i <= 9
 BEGIN
     INSERT INTO Repairs (device_id, repair_start, repair_end, repair_description)
     VALUES (CONCAT('0000000000000000000000000', @i), DATEADD(DAY, -@i, GETDATE()), DATEADD(DAY, -@i+1, GETDATE()), 'Naprawa urządzenia');
     SET @i = @i + 1
 END
 
 DECLARE @j INT = 10
 WHILE @j <= 50
 BEGIN
     INSERT INTO Repairs (device_id, repair_start, repair_end, repair_description)
     VALUES (CONCAT('000000000000000000000000', @j), DATEADD(DAY, -@j, GETDATE()), DATEADD(DAY, -@j+1, GETDATE()), 'Naprawa urządzenia');
     SET @j = @j + 1
 END