API tools faq
paste
Advertisement
JanuszKowalski123

oze8

JanuszKowalski123
Apr 24th, 2024
35
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 14.58 KB | None | 0 0
raw download clone embed print report
  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 @j INT = 10
  88. WHILE @j <= 50
  89. BEGIN
  90.     INSERT INTO Device_Failures (device_id, shift_number, failure_start, failure_end)
  91.     VALUES (CONCAT('000000000000000000000000', @j), round(rand() * 2 + 1,0,0), DATEADD(DAY, -@j, GETDATE()), DATEADD(DAY, -@j+1, GETDATE()));
  92.     SET @j = @j + 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. ALTER TABLE dbo.Renewable_Energy_Devices
  224. ADD CONSTRAINT FK_OZE_SC FOREIGN KEY (company_id) REFERENCES Servicing_Companies(company_id);
  225. ############################
  226.  
  227. -- Tworzenie tabeli zmian serwisowych
  228.  
  229. CREATE TABLE Servicing_Shifts (
  230.     shift_number INT PRIMARY KEY IDENTITY,
  231.     shift_date DATE,
  232.     shift_type INT CHECK (shift_type >= 1 AND shift_type <= 3),
  233.     start_time TIME,
  234.     end_time TIME
  235. );
  236.  
  237. -- Wstawianie danych do tabeli zmian serwisowych (poniedziałek - sobota)
  238. INSERT INTO Servicing_Shifts (shift_date, shift_type, start_time, end_time) VALUES
  239. ('2024-04-01', 1, '06:00:00', '14:00:00'),
  240. ('2024-04-01', 2, '14:00:00', '22:00:00'),
  241. ('2024-04-01', 3, '22:00:00', '06:00:00'),
  242. ('2024-04-02', 1, '06:00:00', '14:00:00'),
  243. ('2024-04-02', 2, '14:00:00', '22:00:00'),
  244. ('2024-04-02', 3, '22:00:00', '06:00:00'),
  245. ('2024-04-03', 1, '06:00:00', '14:00:00'),
  246. ('2024-04-03', 2, '14:00:00', '22:00:00'),
  247. ('2024-04-03', 3, '22:00:00', '06:00:00'),
  248. ('2024-04-04', 1, '06:00:00', '14:00:00'),
  249. ('2024-04-04', 2, '14:00:00', '22:00:00'),
  250. ('2024-04-04', 3, '22:00:00', '06:00:00'),
  251. ('2024-04-05', 1, '06:00:00', '14:00:00'),
  252. ('2024-04-05', 2, '14:00:00', '22:00:00'),
  253. ('2024-04-05', 3, '22:00:00', '06:00:00'),
  254. ('2024-04-06', 1, '06:00:00', '14:00:00'),
  255. ('2024-04-06', 2, '14:00:00', '22:00:00'),
  256. ('2024-04-06', 3, '22:00:00', '06:00:00');
  257.  
  258.  
  259.  
  260. -- Tworzenie tabeli napraw
  261.  
  262.  CREATE TABLE Repairs (
  263.     repair_id INT PRIMARY KEY IDENTITY,
  264.     device_id NVARCHAR(26),
  265.     repair_start DATETIME,
  266.     repair_end DATETIME,
  267.     repair_description NVARCHAR(255),
  268.     FOREIGN KEY (device_id) REFERENCES Renewable_Energy_Devices(device_id)
  269. );
  270.  
  271. -- Wstawianie danych do tabeli napraw  (można kilka razy)
  272.  
  273. DECLARE @i INT = 0
  274. WHILE @i <= 9
  275. BEGIN
  276.     INSERT INTO Repairs (device_id, repair_start, repair_end, repair_description)
  277.     VALUES (CONCAT('0000000000000000000000000', @i), DATEADD(DAY, -@i, GETDATE()), DATEADD(DAY, -@i+1, GETDATE()), 'Naprawa urządzenia');
  278.     SET @i = @i + 1
  279. END
  280.  
  281. DECLARE @j INT = 10
  282. WHILE @j <= 50
  283. BEGIN
  284.     INSERT INTO Repairs (device_id, repair_start, repair_end, repair_description)
  285.     VALUES (CONCAT('000000000000000000000000', @j), DATEADD(DAY, -@j, GETDATE()), DATEADD(DAY, -@j+1, GETDATE()), 'Naprawa urządzenia');
  286.     SET @j = @j + 1
  287. END
  288.  
  289.  
  290. --RAPORTY ####################################################3
  291.  
  292. --Na jakiej zmianie dane urządzenie OZE uległo awarii:
  293.  
  294. SELECT df.device_id, df.shift_number
  295. FROM Device_Failures df
  296. ORDER BY df.failure_start;
  297.  
  298.  
  299. --Na jakiej zmianie urządzenie OZE zostało naprawione:
  300.  
  301. SELECT r.device_id, df.shift_number
  302. FROM Repairs r
  303. JOIN Device_Failures df ON r.device_id = df.device_id
  304. WHERE r.repair_start BETWEEN df.failure_start AND df.failure_end
  305. ORDER BY r.repair_start;
  306.  
  307.  
  308. --Jaki był czas przestoju danego urządzenia OZE w ciągu zadanego okresu czasu:
  309.  
  310. SELECT df.device_id, SUM(DATEDIFF(HOUR, df.failure_start, df.failure_end)) AS downtime_hours
  311. FROM Device_Failures df
  312. GROUP BY df.device_id;
  313.  
  314.  
  315. --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):
  316.  
  317. SELECT df.device_id, SUM(DATEDIFF(HOUR, df.failure_start, df.failure_end)) AS downtime_hours
  318. FROM Device_Failures df
  319. JOIN Servicing_Shifts ss ON df.shift_number = ss.shift_number
  320. WHERE DATENAME(WEEKDAY, df.failure_start) NOT IN ('Saturday', 'Sunday')
  321. GROUP BY df.device_id;
  322.  
  323.  
  324. --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):
  325.  
  326. SELECT SUM(downtime_hours) AS total_downtime_hours
  327. FROM (
  328.     SELECT df.device_id, SUM(DATEDIFF(HOUR, df.failure_start, df.failure_end)) AS downtime_hours
  329.     FROM Device_Failures df
  330.     JOIN Servicing_Shifts ss ON df.shift_number = ss.shift_number
  331.     WHERE DATENAME(WEEKDAY, df.failure_start) NOT IN ('Saturday', 'Sunday')
  332.     GROUP BY df.device_id
  333. ) AS downtime_per_device;
  334.  
  335.  
  336.  
  337. --Jaki jest procentowy udział urządzeń OZE w poszczególnych fazach awarii w stosunku do ilości wszystkich dostępnych urządzeń OZE:
  338.  
  339. WITH Device_Failure_Count AS (
  340.     SELECT device_id, COUNT(*) AS failure_count
  341.     FROM Device_Failures
  342.     GROUP BY device_id
  343. )
  344. SELECT df.device_status, COUNT(*) AS device_count, DFC.failure_count, CAST(COUNT(*) AS DECIMAL) / DFC.failure_count * 100 AS percentage
  345. FROM Renewable_Energy_Devices df
  346. JOIN Device_Failure_Count DFC ON df.device_id = DFC.device_id
  347. GROUP BY df.device_status, DFC.failure_count;
  348.  
  349.  
  350. --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):
  351.  
  352. SELECT df.device_id, COUNT(DISTINCT df.shift_number) AS downtime_shifts
  353. FROM Device_Failures df
  354. JOIN Servicing_Shifts ss ON df.shift_number = ss.shift_number
  355. 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!