API tools faq
paste
Advertisement
JanuszKowalski123

OZE - tworzenie i wypełnianie tabel V2

JanuszKowalski123
May 14th, 2024 (edited)
71
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 11.68 KB | None | 0 0
raw download clone embed print report diff
  1. -- Tworzenie tabeli urządzeń OZE
  2. CREATE TABLE Renewable_Energy_Devices (
  3.     device_id NVARCHAR(26) PRIMARY KEY,
  4.     device_status INT CHECK (device_status >= 0 AND device_status <= 6),
  5.     device_power DECIMAL(18,0),
  6.     company_id INT,
  7.     device_location GEOGRAPHY, -- Nowa kolumna przechowująca współrzędne geograficzne urządzenia
  8.     device_location_geom GEOMETRY, -- Nowa kolumna przechowująca współrzędne geometryczne urządzenia
  9.     device_hierarchy HIERARCHYID, -- Nowa kolumna przechowująca hierarchię urządzeń
  10.     device_description XML -- Nowa kolumna opisująca urządzenia
  11. );
  12.  
  13. -- Tworzenie zmiennych dla współrzędnych geograficznych i geometrycznych
  14. DECLARE @latitude DECIMAL(9, 6), @longitude DECIMAL(9, 6);
  15. DECLARE @point GEOMETRY;
  16. DECLARE @hierarchy HIERARCHYID;
  17. DECLARE @device_description XML;
  18.  
  19. -- Pętla wypełniająca tabelę
  20. DECLARE @i INT = 1;
  21. WHILE @i <= 50
  22. BEGIN
  23.     -- Generowanie losowych danych dla każdej kolumny
  24.     SET @latitude = RAND() * 180 - 90;
  25.     SET @longitude = RAND() * 360 - 180;
  26.     SET @point = GEOMETRY::Point(@latitude, @longitude, 4326);
  27.     SET @hierarchy = HIERARCHYID::GetRoot();
  28.     SET @device_description = '<description>Device ' + CAST(@i AS NVARCHAR(5)) + '</description>';
  29.  
  30.     -- Wstawianie danych do tabeli
  31.     INSERT INTO Renewable_Energy_Devices (device_id, device_status, device_power, company_id, device_location, device_location_geom, device_hierarchy, device_description)
  32.     VALUES (
  33.         'Device_' + CAST(@i AS NVARCHAR(5)), -- device_id
  34.         CAST(RAND() * 6 AS INT), -- device_status
  35.         CAST(RAND() * 1000 AS DECIMAL(18, 0)), -- device_power
  36.         CAST(RAND() * 10 AS INT) + 1, -- company_id
  37.         GEOGRAPHY::Point(@latitude, @longitude, 4326), -- device_location
  38.         @point, -- device_location_geom
  39.         @hierarchy.GetDescendant(NULL, NULL), -- device_hierarchy
  40.         @device_description -- device_description
  41.     );
  42.  
  43.     SET @i = @i + 1;
  44. END;
  45.  
  46.  
  47.  
  48. -- Tworzenie tabeli zgłoszeń awarii
  49.  
  50. CREATE TABLE Device_Failures (
  51.     failure_id INT PRIMARY KEY IDENTITY,
  52.     device_id NVARCHAR(26),
  53.     shift_number INT,
  54.     failure_start DATETIME,
  55.     failure_end DATETIME,
  56.     FOREIGN KEY (device_id) REFERENCES Renewable_Energy_Devices(device_id)
  57. );
  58.  
  59. -- Wstawianie danych do tabeli zgłoszeń awarii
  60.  
  61. -- Zmienna do przechowywania liczby zgłoszeń awarii
  62. DECLARE @numFailures INT = 50;
  63.  
  64. -- Pętla wstawiająca przykładowe zgłoszenia awarii
  65. DECLARE @j INT = 1;
  66. WHILE @j <= @numFailures
  67. BEGIN
  68.     -- Losowanie daty początkowej i końcowej awarii w zakresie ostatniego miesiąca
  69.     DECLARE @startDate DATETIME = DATEADD(DAY, -RAND()*30, GETDATE());
  70.     DECLARE @endDate DATETIME = DATEADD(HOUR, RAND()*24, @startDate);
  71.  
  72.     -- Losowanie numeru zmiany
  73.     DECLARE @shiftNumber INT = ROUND(RAND() * 2 + 1, 0, 0);
  74.  
  75.     -- Losowanie ID urządzenia
  76.     DECLARE @deviceID NVARCHAR(26) = 'Device_' + CAST(ROUND(RAND()*50 + 1, 0, 0) AS NVARCHAR(2));
  77.  
  78.     -- Wstawienie danych do tabeli zgłoszeń awarii
  79.     INSERT INTO Device_Failures (device_id, shift_number, failure_start, failure_end)
  80.     VALUES (@deviceID, @shiftNumber, @startDate, @endDate);
  81.  
  82.     -- Inkrementacja licznika
  83.     SET @j = @j + 1;
  84. END;
  85.  
  86.  
  87. -- Tworzenie tabeli klientów
  88.  
  89. CREATE TABLE Customers (
  90.     customer_id INT PRIMARY KEY,
  91.     name VARCHAR(255),
  92.     email VARCHAR(255),
  93.     phone VARCHAR(20),
  94.     address VARCHAR(255)
  95. );
  96.  
  97. -- Wstawianie danych do tabeli klientów
  98. INSERT INTO Customers (customer_id, name, email, phone, address)
  99. VALUES
  100. (1, 'John Doe', '[email protected]', '123456789', '123 Main St'),
  101. (2, 'Jane Smith', '[email protected]', '987654321', '456 Elm St'),
  102. (3, 'Michael Johnson', '[email protected]', '555123456', '789 Oak St'),
  103. (4, 'Emily Brown', '[email protected]', '444987654', '321 Pine St'),
  104. (5, 'William Wilson', '[email protected]', '789456123', '654 Birch St'),
  105. (6, 'Olivia Taylor', '[email protected]', '987123456', '987 Maple St'),
  106. (7, 'James Martinez', '[email protected]', '321654987', '159 Cedar St'),
  107. (8, 'Emma Anderson', '[email protected]', '666333999', '753 Walnut St'),
  108. (9, 'Noah Thomas', '[email protected]', '222333444', '852 Spruce St'),
  109. (10, 'Ava Hernandez', '[email protected]', '111222333', '369 Cherry St'),
  110. (11, 'Sophia Lopez', '[email protected]', '777888999', '741 Elm St'),
  111. (12, 'Matthew Gonzalez', '[email protected]', '222111333', '258 Oak St'),
  112. (13, 'Isabella Perez', '[email protected]', '666777888', '951 Pine St'),
  113. (14, 'Jacob Robinson', '[email protected]', '999888777', '753 Maple St'),
  114. (15, 'Ethan Lee', '[email protected]', '111222333', '852 Cedar St'),
  115. (16, 'Oliver Walker', '[email protected]', '444555666', '963 Walnut St'),
  116. (17, 'Daniel Hall', '[email protected]', '888777666', '357 Birch St'),
  117. (18, 'Liam Young', '[email protected]', '999888777', '159 Spruce St'),
  118. (19, 'Charlotte Hernandez', '[email protected]', '333222111', '456 Cherry St'),
  119. (20, 'Amelia Moore', '[email protected]', '222333444', '789 Elm St'),
  120. (21, 'Benjamin Nelson', '[email protected]', '444555666', '357 Pine St'),
  121. (22, 'Lucas Hill', '[email protected]', '111222333', '852 Maple St'),
  122. (23, 'Mia King', '[email protected]', '555666777', '963 Cedar St'),
  123. (24, 'Harper Baker', '[email protected]', '777888999', '357 Walnut St'),
  124. (25, 'Evelyn Adams', '[email protected]', '999888777', '456 Birch St');
  125.  
  126. INSERT INTO Customers (customer_id, name, email, phone, address)
  127. VALUES (26, 'John Doe', '[email protected]', '123456789', '556 Main St'),
  128.        (27, 'Jane Smith', '[email protected]', '987654321', '431 Elm St');
  129.  
  130.  
  131. -- Tworzenie tabeli zamówień
  132.  
  133. CREATE TABLE Customers_Orders (
  134.     customer_order_id INT PRIMARY KEY,
  135.     customer_id INT,
  136.     customer_order_date_start DATETIME,
  137.     customer_order_date_end DATETIME,
  138.     customer_order_amount_of_energy DECIMAL(18,0),
  139.     FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
  140. );
  141.  
  142. -- Wstawianie danych do tabeli zamówień
  143.  
  144. INSERT INTO Customers_Orders (customer_order_id, customer_id, customer_order_date_start, customer_order_date_end, customer_order_amount_of_energy)
  145. VALUES
  146. (1, 1, '2024-04-01 08:00:00', '2024-04-01 12:00:00', 100),
  147. (2, 1, '2024-04-02 10:00:00', '2024-04-02 14:00:00', 120),
  148. (3, 2, '2024-04-03 12:00:00', '2024-04-03 16:00:00', 150),
  149. (4, 2, '2024-04-04 09:00:00', '2024-04-04 13:00:00', 130),
  150. (5, 3, '2024-04-05 11:00:00', '2024-04-05 15:00:00', 110),
  151. (6, 3, '2024-04-06 08:00:00', '2024-04-06 12:00:00', 140),
  152. (7, 4, '2024-04-07 10:00:00', '2024-04-07 14:00:00', 125),
  153. (8, 4, '2024-04-08 12:00:00', '2024-04-08 16:00:00', 105),
  154. (9, 5, '2024-04-09 07:00:00', '2024-04-09 11:00:00', 115),
  155. (10, 5, '2024-04-10 11:00:00', '2024-04-10 15:00:00', 135),
  156. (11, 6, '2024-04-11 09:00:00', '2024-04-11 13:00:00', 155),
  157. (12, 6, '2024-04-12 08:00:00', '2024-04-12 12:00:00', 125),
  158. (13, 7, '2024-04-13 10:00:00', '2024-04-13 14:00:00', 145),
  159. (14, 7, '2024-04-14 11:00:00', '2024-04-14 15:00:00', 120),
  160. (15, 8, '2024-04-15 12:00:00', '2024-04-15 16:00:00', 130),
  161. (16, 8, '2024-04-16 08:00:00', '2024-04-16 12:00:00', 110),
  162. (17, 9, '2024-04-17 09:00:00', '2024-04-17 13:00:00', 140),
  163. (18, 9, '2024-04-18 10:00:00', '2024-04-18 14:00:00', 150),
  164. (19, 10, '2024-04-19 08:00:00', '2024-04-19 12:00:00', 120),
  165. (20, 10, '2024-04-20 11:00:00', '2024-04-20 15:00:00', 130),
  166. (21, 11, '2024-04-21 10:00:00', '2024-04-21 14:00:00', 140),
  167. (22, 11, '2024-04-22 12:00:00', '2024-04-22 16:00:00', 160),
  168. (23, 12, '2024-04-23 09:00:00', '2024-04-23 13:00:00', 125),
  169. (24, 12, '2024-04-24 07:00:00', '2024-04-24 11:00:00', 135),
  170. (25, 13, '2024-04-25 11:00:00', '2024-04-25 15:00:00', 115),
  171. (26, 13, '2024-04-26 12:00:00', '2024-04-26 16:00:00', 105),
  172. (27, 14, '2024-04-27 10:00:00', '2024-04-27 14:00:00', 145),
  173. (28, 14, '2024-04-28 08:00:00', '2024-04-28 12:00:00', 125),
  174. (29, 15, '2024-04-29 11:00:00', '2024-04-29 15:00:00', 135),
  175. (30, 15, '2024-04-30 09:00:00', '2024-04-30 13:00:00', 155),
  176. (31, 16, '2024-05-01 08:00:00', '2024-05-01 12:00:00', 125),
  177. (32, 16, '2024-05-02 10:00:00', '2024-05-02 14:00:00', 145),
  178. (33, 17, '2024-05-03 12:00:00', '2024-05-03 16:00:00', 130),
  179. (34, 17, '2024-05-04 09:00:00', '2024-05-04 13:00:00', 140),
  180. (35, 18, '2024-05-05 11:00:00', '2024-05-05 15:00:00', 150),
  181. (36, 18, '2024-05-06 08:00:00', '2024-05-06 12:00:00', 120),
  182. (37, 19, '2024-05-07 10:00:00', '2024-05-07 14:00:00', 110),
  183. (38, 19, '2024-05-08 12:00:00', '2024-05-08 16:00:00', 130),
  184. (39, 20, '2024-05-09 07:00:00', '2024-05-09 11:00:00', 140),
  185. (40, 20, '2024-05-10 11:00:00', '2024-05-10 15:00:00', 150),
  186. (41, 21, '2024-05-11 09:00:00', '2024-05-11 13:00:00', 125),
  187. (42, 21, '2024-05-12 08:00:00', '2024-05-12 12:00:00', 135),
  188. (43, 22, '2024-05-13 10:00:00', '2024-05-13 14:00:00', 115),
  189. (44, 22, '2024-05-14 11:00:00', '2024-05-14 15:00:00', 105),
  190. (45, 23, '2024-05-15 12:00:00', '2024-05-15 16:00:00', 145),
  191. (46, 23, '2024-05-16 08:00:00', '2024-05-16 12:00:00', 125),
  192. (47, 24, '2024-05-17 09:00:00', '2024-05-17 13:00:00', 135),
  193. (48, 24, '2024-05-18 10:00:00', '2024-05-18 14:00:00', 145),
  194. (49, 25, '2024-05-19 08:00:00', '2024-05-19 12:00:00', 155),
  195. (50, 25, '2024-05-20 11:00:00', '2024-05-20 15:00:00', 125);
  196.  
  197.  
  198.  
  199. -- Tworzenie tabeli firm serwisowych
  200.  
  201. CREATE TABLE Servicing_Companies (
  202.     company_id INT PRIMARY KEY,
  203.     name VARCHAR(255),
  204.     working_hours VARCHAR(255)
  205. );
  206.  
  207. -- Wstawianie danych do tabeli firm serwisowych
  208.  
  209. INSERT INTO Servicing_Companies (company_id, name, working_hours)
  210. VALUES (1, 'Company Y', '6:00-14:00, 14:00-22:00, 22:00-6:00');
  211.  
  212.  
  213. ----------
  214. ALTER TABLE dbo.Renewable_Energy_Devices
  215. ADD CONSTRAINT FK_OZE_SC FOREIGN KEY (company_id) REFERENCES Servicing_Companies(company_id);
  216. ----------
  217.  
  218. -- Tworzenie tabeli zmian serwisowych
  219.  
  220. CREATE TABLE Servicing_Shifts (
  221.     shift_number INT PRIMARY KEY IDENTITY,
  222.     shift_date DATE,
  223.     shift_type INT CHECK (shift_type >= 1 AND shift_type <= 3),
  224.     start_time TIME,
  225.     end_time TIME
  226. );
  227.  
  228. -- Wstawianie danych do tabeli zmian serwisowych (poniedziałek - sobota)
  229. INSERT INTO Servicing_Shifts (shift_date, shift_type, start_time, end_time) VALUES
  230. ('2024-04-01', 1, '06:00:00', '14:00:00'),
  231. ('2024-04-01', 2, '14:00:00', '22:00:00'),
  232. ('2024-04-01', 3, '22:00:00', '06:00:00'),
  233. ('2024-04-02', 1, '06:00:00', '14:00:00'),
  234. ('2024-04-02', 2, '14:00:00', '22:00:00'),
  235. ('2024-04-02', 3, '22:00:00', '06:00:00'),
  236. ('2024-04-03', 1, '06:00:00', '14:00:00'),
  237. ('2024-04-03', 2, '14:00:00', '22:00:00'),
  238. ('2024-04-03', 3, '22:00:00', '06:00:00'),
  239. ('2024-04-04', 1, '06:00:00', '14:00:00'),
  240. ('2024-04-04', 2, '14:00:00', '22:00:00'),
  241. ('2024-04-04', 3, '22:00:00', '06:00:00'),
  242. ('2024-04-05', 1, '06:00:00', '14:00:00'),
  243. ('2024-04-05', 2, '14:00:00', '22:00:00'),
  244. ('2024-04-05', 3, '22:00:00', '06:00:00'),
  245. ('2024-04-06', 1, '06:00:00', '14:00:00'),
  246. ('2024-04-06', 2, '14:00:00', '22:00:00'),
  247. ('2024-04-06', 3, '22:00:00', '06:00:00');
  248.  
  249.  
  250.  
  251. -- Tworzenie tabeli napraw
  252.  
  253.  CREATE TABLE Repairs (
  254.     repair_id INT PRIMARY KEY IDENTITY,
  255.     device_id NVARCHAR(26),
  256.     repair_start DATETIME,
  257.     repair_end DATETIME,
  258.     repair_description NVARCHAR(255),
  259.     FOREIGN KEY (device_id) REFERENCES Renewable_Energy_Devices(device_id)
  260. );
  261.  
  262. -- Wstawianie danych do tabeli napraw  (można kilka razy)
  263.  
  264. DECLARE @i INT = 0
  265. WHILE @i <= 9
  266. BEGIN
  267.     INSERT INTO Repairs (device_id, repair_start, repair_end, repair_description)
  268.     VALUES (CONCAT('0000000000000000000000000', @i), DATEADD(DAY, -@i, GETDATE()), DATEADD(DAY, -@i+1, GETDATE()), 'Naprawa urządzenia');
  269.     SET @i = @i + 1
  270. END
  271.  
  272. DECLARE @j INT = 10
  273. WHILE @j <= 50
  274. BEGIN
  275.     INSERT INTO Repairs (device_id, repair_start, repair_end, repair_description)
  276.     VALUES (CONCAT('000000000000000000000000', @j), DATEADD(DAY, -@j, GETDATE()), DATEADD(DAY, -@j+1, GETDATE()), 'Naprawa urządzenia');
  277.     SET @j = @j + 1
  278. END
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!