API tools faq
paste
View difference between Paste ID: 2vb6HWDn and zFumcPvr
SHOW: | | - or go back to the newest paste.
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)
6+
	device_power DECIMAL(18,0),
7
	company_id INT
8
);
9
10-
INSERT INTO Renewable_Energy_Devices (device_id, device_status, device_power)
10+
11
INSERT INTO Renewable_Energy_Devices (device_id, device_status, device_power, company_id)

12-
('00000000000000000000000000',0,	1000),

12+
13-
('00000000000000000000000001',0,	1000),

13+
('00000000000000000000000000',0,	1000,1),

14-
('00000000000000000000000002',0,	1000),

14+
('00000000000000000000000001',0,	1000,1),

15-
('00000000000000000000000003',0,	2000),

15+
('00000000000000000000000002',0,	1000,1),

16-
('00000000000000000000000004',0,	1000),

16+
('00000000000000000000000003',0,	2000,1),

17-
('00000000000000000000000005',0,	1000),

17+
('00000000000000000000000004',0,	1000,1),

18-
('00000000000000000000000006',0,	1000),

18+
('00000000000000000000000005',0,	1000,1),

19-
('00000000000000000000000007',3,	1000),

19+
('00000000000000000000000006',0,	1000,1),

20-
('00000000000000000000000008',0,	5000),

20+
('00000000000000000000000007',3,	1000,1),

21-
('00000000000000000000000009',0,	1000),

21+
('00000000000000000000000008',0,	5000,1),

22-
('00000000000000000000000010',0,	1000),

22+
('00000000000000000000000009',0,	1000,1),

23-
('00000000000000000000000011',0,	1000),

23+
('00000000000000000000000010',0,	1000,1),

24-
('00000000000000000000000012',0,	1000),

24+
('00000000000000000000000011',0,	1000,1),

25-
('00000000000000000000000013',0,	1000),

25+
('00000000000000000000000012',0,	1000,1),

26-
('00000000000000000000000014',0,	1000),

26+
('00000000000000000000000013',0,	1000,1),

27-
('00000000000000000000000015',2,	4000),

27+
('00000000000000000000000014',0,	1000,1),

28-
('00000000000000000000000016',1,	1000),

28+
('00000000000000000000000015',2,	4000,1),

29-
('00000000000000000000000017',0,	1000),

29+
('00000000000000000000000016',1,	1000,1),

30-
('00000000000000000000000018',0,	1000),

30+
('00000000000000000000000017',0,	1000,1),

31-
('00000000000000000000000019',0,	1000),

31+
('00000000000000000000000018',0,	1000,1),

32-
('00000000000000000000000020',0,	7000),

32+
('00000000000000000000000019',0,	1000,1),

33-
('00000000000000000000000021',0,	1000),

33+
('00000000000000000000000020',0,	7000,1),

34-
('00000000000000000000000022',0,	1000),

34+
('00000000000000000000000021',0,	1000,1),

35-
('00000000000000000000000023',0,	1000),

35+
('00000000000000000000000022',0,	1000,1),

36-
('00000000000000000000000024',0,	1000),

36+
('00000000000000000000000023',0,	1000,1),

37-
('00000000000000000000000025',0,	1000),

37+
('00000000000000000000000024',0,	1000,1),

38-
('00000000000000000000000026',0,	1000),

38+
('00000000000000000000000025',0,	1000,1),

39-
('00000000000000000000000027',0,	1000),

39+
('00000000000000000000000026',0,	1000,1),

40-
('00000000000000000000000028',0,	1000),

40+
('00000000000000000000000027',0,	1000,1),

41-
('00000000000000000000000029',0,	1000),

41+
('00000000000000000000000028',0,	1000,1),

42-
('00000000000000000000000030',0,	1000),

42+
('00000000000000000000000029',0,	1000,1),

43-
('00000000000000000000000031',0,	8000),

43+
('00000000000000000000000030',0,	1000,1),

44-
('00000000000000000000000032',0,	1000),

44+
('00000000000000000000000031',0,	8000,1),

45-
('00000000000000000000000033',0,	1000),

45+
('00000000000000000000000032',0,	1000,1),

46-
('00000000000000000000000034',0,	1000),

46+
('00000000000000000000000033',0,	1000,1),

47-
('00000000000000000000000035',0,	1000),

47+
('00000000000000000000000034',0,	1000,1),

48-
('00000000000000000000000036',0,	1000),

48+
('00000000000000000000000035',0,	1000,1),

49-
('00000000000000000000000037',0,	1000),

49+
('00000000000000000000000036',0,	1000,1),

50-
('00000000000000000000000038',0,	1000),

50+
('00000000000000000000000037',0,	1000,1),

51-
('00000000000000000000000039',0,	1000),

51+
('00000000000000000000000038',0,	1000,1),

52-
('00000000000000000000000040',0,	1000),

52+
('00000000000000000000000039',0,	1000,1),

53-
('00000000000000000000000041',0,	1000),

53+
('00000000000000000000000040',0,	1000,1),

54-
('00000000000000000000000042',0,	1000),

54+
('00000000000000000000000041',0,	1000,1),

55-
('00000000000000000000000043',0,	1000),

55+
('00000000000000000000000042',0,	1000,1),

56-
('00000000000000000000000044',0,	1000),

56+
('00000000000000000000000043',0,	1000,1),

57-
('00000000000000000000000045',0,	1000),

57+
('00000000000000000000000044',0,	1000,1),

58-
('00000000000000000000000046',0,	9000),

58+
('00000000000000000000000045',0,	1000,1),

59-
('00000000000000000000000047',0,	1000),

59+
('00000000000000000000000046',0,	9000,1),

60-
('00000000000000000000000048',0,	1000),

60+
('00000000000000000000000047',0,	1000,1),

61-
('00000000000000000000000049',0,	1000),

61+
('00000000000000000000000048',0,	1000,1),

62-
('00000000000000000000000050',3,	7000);

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-
    working_hours VARCHAR(255) -- Można to dostosować w zależności od potrzeb

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;
        

    


            


                                


        

            



                
    

        Public Pastes
    

    
            

    

                    

        

    





    


    
    
    
    
    
    
    
    




    


    



                

            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!