API tools faq
paste
Advertisement
JanuszKowalski123

oze7

JanuszKowalski123
Apr 24th, 2024 (edited)
47
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 14.51 KB | None | 0 0
raw download clone embed print report diff
  1. -- Tworzenie tabeli urządzeń OZE
  2.  
  3. CREATE TABLE Renewable_Energy_Devices (
  4.     device_id NVARCHAR(26) PRIMARY KEY,
  5.     device_status INT CHECK (device_status >= 0 AND device_status <= 6),
  6.     device_power DECIMAL(18,0),
  7.     company_id INT
  8. );
  9.  
  10. -- Wstawianie danych do tabeli urządzeń OZE
  11. INSERT INTO Renewable_Energy_Devices (device_id, device_status, device_power, company_id)
  12. VALUES
  13. ('00000000000000000000000000',0,    1000,1),
  14. ('00000000000000000000000001',0,    1000,1),
  15. ('00000000000000000000000002',0,    1000,1),
  16. ('00000000000000000000000003',0,    2000,1),
  17. ('00000000000000000000000004',0,    1000,1),
  18. ('00000000000000000000000005',0,    1000,1),
  19. ('00000000000000000000000006',0,    1000,1),
  20. ('00000000000000000000000007',3,    1000,1),
  21. ('00000000000000000000000008',0,    5000,1),
  22. ('00000000000000000000000009',0,    1000,1),
  23. ('00000000000000000000000010',0,    1000,1),
  24. ('00000000000000000000000011',0,    1000,1),
  25. ('00000000000000000000000012',0,    1000,1),
  26. ('00000000000000000000000013',0,    1000,1),
  27. ('00000000000000000000000014',0,    1000,1),
  28. ('00000000000000000000000015',2,    4000,1),
  29. ('00000000000000000000000016',1,    1000,1),
  30. ('00000000000000000000000017',0,    1000,1),
  31. ('00000000000000000000000018',0,    1000,1),
  32. ('00000000000000000000000019',0,    1000,1),
  33. ('00000000000000000000000020',0,    7000,1),
  34. ('00000000000000000000000021',0,    1000,1),
  35. ('00000000000000000000000022',0,    1000,1),
  36. ('00000000000000000000000023',0,    1000,1),
  37. ('00000000000000000000000024',0,    1000,1),
  38. ('00000000000000000000000025',0,    1000,1),
  39. ('00000000000000000000000026',0,    1000,1),
  40. ('00000000000000000000000027',0,    1000,1),
  41. ('00000000000000000000000028',0,    1000,1),
  42. ('00000000000000000000000029',0,    1000,1),
  43. ('00000000000000000000000030',0,    1000,1),
  44. ('00000000000000000000000031',0,    8000,1),
  45. ('00000000000000000000000032',0,    1000,1),
  46. ('00000000000000000000000033',0,    1000,1),
  47. ('00000000000000000000000034',0,    1000,1),
  48. ('00000000000000000000000035',0,    1000,1),
  49. ('00000000000000000000000036',0,    1000,1),
  50. ('00000000000000000000000037',0,    1000,1),
  51. ('00000000000000000000000038',0,    1000,1),
  52. ('00000000000000000000000039',0,    1000,1),
  53. ('00000000000000000000000040',0,    1000,1),
  54. ('00000000000000000000000041',0,    1000,1),
  55. ('00000000000000000000000042',0,    1000,1),
  56. ('00000000000000000000000043',0,    1000,1),
  57. ('00000000000000000000000044',0,    1000,1),
  58. ('00000000000000000000000045',0,    1000,1),
  59. ('00000000000000000000000046',0,    9000,1),
  60. ('00000000000000000000000047',0,    1000,1),
  61. ('00000000000000000000000048',0,    1000,1),
  62. ('00000000000000000000000049',0,    1000,1),
  63. ('00000000000000000000000050',3,    7000,1);
  64.  
  65.  
  66. -- Tworzenie tabeli zgłoszeń awarii
  67.  
  68. CREATE TABLE Device_Failures (
  69.     failure_id INT PRIMARY KEY IDENTITY,
  70.     device_id NVARCHAR(26),
  71.     shift_number INT,
  72.     failure_start DATETIME,
  73.     failure_end DATETIME,
  74.     FOREIGN KEY (device_id) REFERENCES Renewable_Energy_Devices(device_id)
  75. );
  76.  
  77. -- Wstawianie danych do tabeli zgłoszeń awarii
  78.  
  79. DECLARE @i INT = 0
  80. WHILE @i <= 9
  81. BEGIN
  82.     INSERT INTO Device_Failures (device_id, shift_number, failure_start, failure_end)
  83.     VALUES (CONCAT('0000000000000000000000000', @i), round(rand() * 2 + 1,0,0), DATEADD(DAY, -@i, GETDATE()), DATEADD(DAY, -@i+1, GETDATE()));
  84.     SET @i = @i + 1
  85. END
  86.  
  87. DECLARE @i INT = 10
  88. WHILE @i <= 50
  89. BEGIN
  90.     INSERT INTO Device_Failures (device_id, shift_number, failure_start, failure_end)
  91.     VALUES (CONCAT('000000000000000000000000', @i), round(rand() * 2 + 1,0,0), DATEADD(DAY, -@i, GETDATE()), DATEADD(DAY, -@i+1, GETDATE()));
  92.     SET @i = @i + 1
  93. END
  94.  
  95.  
  96. -- Tworzenie tabeli klientów
  97.  
  98. CREATE TABLE Customers (
  99.     customer_id INT PRIMARY KEY,
  100.     name VARCHAR(255),
  101.     email VARCHAR(255),
  102.     phone VARCHAR(20),
  103.     address VARCHAR(255)
  104. );
  105.  
  106. -- Wstawianie danych do tabeli klientów
  107. INSERT INTO Customers (customer_id, name, email, phone, address)
  108. VALUES
  109. (1, 'John Doe', '[email protected]', '123456789', '123 Main St'),
  110. (2, 'Jane Smith', '[email protected]', '987654321', '456 Elm St'),
  111. (3, 'Michael Johnson', '[email protected]', '555123456', '789 Oak St'),
  112. (4, 'Emily Brown', '[email protected]', '444987654', '321 Pine St'),
  113. (5, 'William Wilson', '[email protected]', '789456123', '654 Birch St'),
  114. (6, 'Olivia Taylor', '[email protected]', '987123456', '987 Maple St'),
  115. (7, 'James Martinez', '[email protected]', '321654987', '159 Cedar St'),
  116. (8, 'Emma Anderson', '[email protected]', '666333999', '753 Walnut St'),
  117. (9, 'Noah Thomas', '[email protected]', '222333444', '852 Spruce St'),
  118. (10, 'Ava Hernandez', '[email protected]', '111222333', '369 Cherry St'),
  119. (11, 'Sophia Lopez', '[email protected]', '777888999', '741 Elm St'),
  120. (12, 'Matthew Gonzalez', '[email protected]', '222111333', '258 Oak St'),
  121. (13, 'Isabella Perez', '[email protected]', '666777888', '951 Pine St'),
  122. (14, 'Jacob Robinson', '[email protected]', '999888777', '753 Maple St'),
  123. (15, 'Ethan Lee', '[email protected]', '111222333', '852 Cedar St'),
  124. (16, 'Oliver Walker', '[email protected]', '444555666', '963 Walnut St'),
  125. (17, 'Daniel Hall', '[email protected]', '888777666', '357 Birch St'),
  126. (18, 'Liam Young', '[email protected]', '999888777', '159 Spruce St'),
  127. (19, 'Charlotte Hernandez', '[email protected]', '333222111', '456 Cherry St'),
  128. (20, 'Amelia Moore', '[email protected]', '222333444', '789 Elm St'),
  129. (21, 'Benjamin Nelson', '[email protected]', '444555666', '357 Pine St'),
  130. (22, 'Lucas Hill', '[email protected]', '111222333', '852 Maple St'),
  131. (23, 'Mia King', '[email protected]', '555666777', '963 Cedar St'),
  132. (24, 'Harper Baker', '[email protected]', '777888999', '357 Walnut St'),
  133. (25, 'Evelyn Adams', '[email protected]', '999888777', '456 Birch St');
  134.  
  135. INSERT INTO Customers (customer_id, name, email, phone, address)
  136. VALUES (26, 'John Doe', '[email protected]', '123456789', '556 Main St'),
  137.        (27, 'Jane Smith', '[email protected]', '987654321', '431 Elm St');
  138.  
  139.  
  140. -- Tworzenie tabeli zamówień
  141.  
  142. CREATE TABLE Customers_Orders (
  143.     customer_order_id INT PRIMARY KEY,
  144.     customer_id INT,
  145.     customer_order_date_start DATETIME,
  146.     customer_order_date_end DATETIME,
  147.     customer_order_amount_of_energy DECIMAL(18,0),
  148.     FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
  149. );
  150.  
  151. -- Wstawianie danych do tabeli zamówień
  152.  
  153. INSERT INTO Customers_Orders (customer_order_id, customer_id, customer_order_date_start, customer_order_date_end, customer_order_amount_of_energy)
  154. VALUES
  155. (1, 1, '2024-04-01 08:00:00', '2024-04-01 12:00:00', 100),
  156. (2, 1, '2024-04-02 10:00:00', '2024-04-02 14:00:00', 120),
  157. (3, 2, '2024-04-03 12:00:00', '2024-04-03 16:00:00', 150),
  158. (4, 2, '2024-04-04 09:00:00', '2024-04-04 13:00:00', 130),
  159. (5, 3, '2024-04-05 11:00:00', '2024-04-05 15:00:00', 110),
  160. (6, 3, '2024-04-06 08:00:00', '2024-04-06 12:00:00', 140),
  161. (7, 4, '2024-04-07 10:00:00', '2024-04-07 14:00:00', 125),
  162. (8, 4, '2024-04-08 12:00:00', '2024-04-08 16:00:00', 105),
  163. (9, 5, '2024-04-09 07:00:00', '2024-04-09 11:00:00', 115),
  164. (10, 5, '2024-04-10 11:00:00', '2024-04-10 15:00:00', 135),
  165. (11, 6, '2024-04-11 09:00:00', '2024-04-11 13:00:00', 155),
  166. (12, 6, '2024-04-12 08:00:00', '2024-04-12 12:00:00', 125),
  167. (13, 7, '2024-04-13 10:00:00', '2024-04-13 14:00:00', 145),
  168. (14, 7, '2024-04-14 11:00:00', '2024-04-14 15:00:00', 120),
  169. (15, 8, '2024-04-15 12:00:00', '2024-04-15 16:00:00', 130),
  170. (16, 8, '2024-04-16 08:00:00', '2024-04-16 12:00:00', 110),
  171. (17, 9, '2024-04-17 09:00:00', '2024-04-17 13:00:00', 140),
  172. (18, 9, '2024-04-18 10:00:00', '2024-04-18 14:00:00', 150),
  173. (19, 10, '2024-04-19 08:00:00', '2024-04-19 12:00:00', 120),
  174. (20, 10, '2024-04-20 11:00:00', '2024-04-20 15:00:00', 130),
  175. (21, 11, '2024-04-21 10:00:00', '2024-04-21 14:00:00', 140),
  176. (22, 11, '2024-04-22 12:00:00', '2024-04-22 16:00:00', 160),
  177. (23, 12, '2024-04-23 09:00:00', '2024-04-23 13:00:00', 125),
  178. (24, 12, '2024-04-24 07:00:00', '2024-04-24 11:00:00', 135),
  179. (25, 13, '2024-04-25 11:00:00', '2024-04-25 15:00:00', 115),
  180. (26, 13, '2024-04-26 12:00:00', '2024-04-26 16:00:00', 105),
  181. (27, 14, '2024-04-27 10:00:00', '2024-04-27 14:00:00', 145),
  182. (28, 14, '2024-04-28 08:00:00', '2024-04-28 12:00:00', 125),
  183. (29, 15, '2024-04-29 11:00:00', '2024-04-29 15:00:00', 135),
  184. (30, 15, '2024-04-30 09:00:00', '2024-04-30 13:00:00', 155),
  185. (31, 16, '2024-05-01 08:00:00', '2024-05-01 12:00:00', 125),
  186. (32, 16, '2024-05-02 10:00:00', '2024-05-02 14:00:00', 145),
  187. (33, 17, '2024-05-03 12:00:00', '2024-05-03 16:00:00', 130),
  188. (34, 17, '2024-05-04 09:00:00', '2024-05-04 13:00:00', 140),
  189. (35, 18, '2024-05-05 11:00:00', '2024-05-05 15:00:00', 150),
  190. (36, 18, '2024-05-06 08:00:00', '2024-05-06 12:00:00', 120),
  191. (37, 19, '2024-05-07 10:00:00', '2024-05-07 14:00:00', 110),
  192. (38, 19, '2024-05-08 12:00:00', '2024-05-08 16:00:00', 130),
  193. (39, 20, '2024-05-09 07:00:00', '2024-05-09 11:00:00', 140),
  194. (40, 20, '2024-05-10 11:00:00', '2024-05-10 15:00:00', 150),
  195. (41, 21, '2024-05-11 09:00:00', '2024-05-11 13:00:00', 125),
  196. (42, 21, '2024-05-12 08:00:00', '2024-05-12 12:00:00', 135),
  197. (43, 22, '2024-05-13 10:00:00', '2024-05-13 14:00:00', 115),
  198. (44, 22, '2024-05-14 11:00:00', '2024-05-14 15:00:00', 105),
  199. (45, 23, '2024-05-15 12:00:00', '2024-05-15 16:00:00', 145),
  200. (46, 23, '2024-05-16 08:00:00', '2024-05-16 12:00:00', 125),
  201. (47, 24, '2024-05-17 09:00:00', '2024-05-17 13:00:00', 135),
  202. (48, 24, '2024-05-18 10:00:00', '2024-05-18 14:00:00', 145),
  203. (49, 25, '2024-05-19 08:00:00', '2024-05-19 12:00:00', 155),
  204. (50, 25, '2024-05-20 11:00:00', '2024-05-20 15:00:00', 125);
  205.  
  206.  
  207.  
  208. -- Tworzenie tabeli firm serwisowych
  209.  
  210. CREATE TABLE Servicing_Companies (
  211.     company_id INT PRIMARY KEY,
  212.     name VARCHAR(255),
  213.     working_hours VARCHAR(255)
  214. );
  215.  
  216. -- Wstawianie danych do tabeli firm serwisowych
  217.  
  218. INSERT INTO Servicing_Companies (company_id, name, working_hours)
  219. VALUES (1, 'Company Y', '6:00-14:00, 14:00-22:00, 22:00-6:00');
  220.  
  221.  
  222. ############################
  223. FOREIGN KEY (company_id) REFERENCES Servicing_Companies(company_id)
  224. ############################
  225.  
  226. -- Tworzenie tabeli zmian serwisowych
  227.  
  228. CREATE TABLE Servicing_Shifts (
  229.     shift_number INT PRIMARY KEY IDENTITY,
  230.     shift_date DATE,
  231.     shift_type INT CHECK (shift_type >= 1 AND shift_type <= 3),
  232.     start_time TIME,
  233.     end_time TIME
  234. );
  235.  
  236. -- Wstawianie danych do tabeli zmian serwisowych (poniedziałek - sobota)
  237. INSERT INTO Servicing_Shifts (shift_date, shift_type, start_time, end_time) VALUES
  238. ('2024-04-01', 1, '06:00:00', '14:00:00'),
  239. ('2024-04-01', 2, '14:00:00', '22:00:00'),
  240. ('2024-04-01', 3, '22:00:00', '06:00:00'),
  241. ('2024-04-02', 1, '06:00:00', '14:00:00'),
  242. ('2024-04-02', 2, '14:00:00', '22:00:00'),
  243. ('2024-04-02', 3, '22:00:00', '06:00:00'),
  244. ('2024-04-03', 1, '06:00:00', '14:00:00'),
  245. ('2024-04-03', 2, '14:00:00', '22:00:00'),
  246. ('2024-04-03', 3, '22:00:00', '06:00:00'),
  247. ('2024-04-04', 1, '06:00:00', '14:00:00'),
  248. ('2024-04-04', 2, '14:00:00', '22:00:00'),
  249. ('2024-04-04', 3, '22:00:00', '06:00:00'),
  250. ('2024-04-05', 1, '06:00:00', '14:00:00'),
  251. ('2024-04-05', 2, '14:00:00', '22:00:00'),
  252. ('2024-04-05', 3, '22:00:00', '06:00:00'),
  253. ('2024-04-06', 1, '06:00:00', '14:00:00'),
  254. ('2024-04-06', 2, '14:00:00', '22:00:00'),
  255. ('2024-04-06', 3, '22:00:00', '06:00:00');
  256.  
  257.  
  258.  
  259. -- Tworzenie tabeli napraw
  260.  
  261.  CREATE TABLE Repairs (
  262.     repair_id INT PRIMARY KEY IDENTITY,
  263.     device_id NVARCHAR(26),
  264.     repair_start DATETIME,
  265.     repair_end DATETIME,
  266.     repair_description NVARCHAR(255),
  267.     FOREIGN KEY (device_id) REFERENCES Renewable_Energy_Devices(device_id)
  268. );
  269.  
  270. -- Wstawianie danych do tabeli napraw  (można kilka razy)
  271.  
  272. DECLARE @i INT = 0
  273. WHILE @i <= 9
  274. BEGIN
  275.     INSERT INTO Repairs (device_id, repair_start, repair_end, repair_description)
  276.     VALUES (CONCAT('0000000000000000000000000', @i), DATEADD(DAY, -@i, GETDATE()), DATEADD(DAY, -@i+1, GETDATE()), 'Naprawa urządzenia');
  277.     SET @i = @i + 1
  278. END
  279.  
  280. DECLARE @i INT = 10
  281. WHILE @i <= 50
  282. BEGIN
  283.     INSERT INTO Repairs (device_id, repair_start, repair_end, repair_description)
  284.     VALUES (CONCAT('000000000000000000000000', @i), DATEADD(DAY, -@i, GETDATE()), DATEADD(DAY, -@i+1, GETDATE()), 'Naprawa urządzenia');
  285.     SET @i = @i + 1
  286. END
  287.  
  288.  
  289. --RAPORTY ####################################################3
  290.  
  291. --Na jakiej zmianie dane urządzenie OZE uległo awarii:
  292.  
  293. SELECT df.device_id, df.shift_number
  294. FROM Device_Failures df
  295. ORDER BY df.failure_start;
  296.  
  297.  
  298. --Na jakiej zmianie urządzenie OZE zostało naprawione:
  299.  
  300. SELECT r.device_id, df.shift_number
  301. FROM Repairs r
  302. JOIN Device_Failures df ON r.device_id = df.device_id
  303. WHERE r.repair_start BETWEEN df.failure_start AND df.failure_end
  304. ORDER BY r.repair_start;
  305.  
  306.  
  307. --Jaki był czas przestoju danego urządzenia OZE w ciągu zadanego okresu czasu:
  308.  
  309. SELECT df.device_id, SUM(DATEDIFF(HOUR, df.failure_start, df.failure_end)) AS downtime_hours
  310. FROM Device_Failures df
  311. GROUP BY df.device_id;
  312.  
  313.  
  314. --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):
  315.  
  316. SELECT df.device_id, SUM(DATEDIFF(HOUR, df.failure_start, df.failure_end)) AS downtime_hours
  317. FROM Device_Failures df
  318. JOIN Servicing_Shifts ss ON df.shift_number = ss.shift_number
  319. WHERE DATENAME(WEEKDAY, df.failure_start) NOT IN ('Saturday', 'Sunday')
  320. GROUP BY df.device_id;
  321.  
  322.  
  323. --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):
  324.  
  325. SELECT SUM(downtime_hours) AS total_downtime_hours
  326. FROM (
  327.     SELECT df.device_id, SUM(DATEDIFF(HOUR, df.failure_start, df.failure_end)) AS downtime_hours
  328.     FROM Device_Failures df
  329.     JOIN Servicing_Shifts ss ON df.shift_number = ss.shift_number
  330.     WHERE DATENAME(WEEKDAY, df.failure_start) NOT IN ('Saturday', 'Sunday')
  331.     GROUP BY df.device_id
  332. ) AS downtime_per_device;
  333.  
  334.  
  335.  
  336. --Jaki jest procentowy udział urządzeń OZE w poszczególnych fazach awarii w stosunku do ilości wszystkich dostępnych urządzeń OZE:
  337.  
  338. WITH Device_Failure_Count AS (
  339.     SELECT device_id, COUNT(*) AS failure_count
  340.     FROM Device_Failures
  341.     GROUP BY device_id
  342. )
  343. SELECT df.device_status, COUNT(*) AS device_count, DFC.failure_count, CAST(COUNT(*) AS DECIMAL) / DFC.failure_count * 100 AS percentage
  344. FROM Renewable_Energy_Devices df
  345. JOIN Device_Failure_Count DFC ON df.device_id = DFC.device_id
  346. GROUP BY df.device_status, DFC.failure_count;
  347.  
  348.  
  349. --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):
  350.  
  351. SELECT df.device_id, COUNT(DISTINCT df.shift_number) AS downtime_shifts
  352. FROM Device_Failures df
  353. JOIN Servicing_Shifts ss ON df.shift_number = ss.shift_number
  354. GROUP BY df.device_id;
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!