I need to create a query for the test duration. For each of the three rooms, ple
ID: 3589247 • Letter: I
Question
I need to create a query for the test duration. For each of the three rooms, please get the Average, Min, and Max test durations.
Is it possible to create a duration field that will take the difference of Finish Time -Start Time and give that number in duration. Assigning it to it column
OR
I need to create a query test duration. For each of the three rooms, please get the Average, Min, and Max test durations with the information shown.
Please create the MYSQL command.
Exam ID Patient ID MD ID Start Time 23:37:26 08:25:05 09:10:04 10:09:45 08:41:17 12:28:09 13:13:43 12:49:35 Finish Time 00:19:47 08:49:40 09:50:36 10:28:29 12:07:42 12:58:43 13:29:47 13:38:51 Room ID 109 35 108 46 163 75 43 57 4 IR1 4 IR2 30 IR2 28 IR3 24 IR1 19 IR2 15 IR1 13 IR3 4 7Explanation / Answer
----------- TABLE CREATED FOR TESTING PURPOSE----------
CREATE TABLE HosptRoom2
(
Exam_ID INTEGER,
PatientID INTEGER,
MD_ID INTEGER,
Room_ID VARCHAR(5),
Start_Time TIME,
Finsh_Time TIME
);
-- DATA INSERTED AS PER QUESTION FOR TESTING PURPOSE-------------
INSERT INTO HosptRoom2 VALUES(1,109,4,'IR1','23:37:26','00:19:47' );
INSERT INTO HosptRoom2 VALUES(2,35,4,'IR1','08:25:05','08:49:40');
INSERT INTO HosptRoom2 VALUES(3,108,30,'IR2','09:10:04','09:50:36');
INSERT INTO HosptRoom2 VALUES(4,46,28,'IR3','10:09:45','10:28:29');
INSERT INTO HosptRoom2 VALUES(5,163,24,'IR1','08:41:17','12:07:42');
INSERT INTO HosptRoom2 VALUES(6,75,19,'IR2','12:28:09','12:58:43');
INSERT INTO HosptRoom2 VALUES(7,43,15,'IR1','13:13:43','13:29:47');
INSERT INTO HosptRoom2 VALUES(8,57,13,'IR3','12:49:35','13:38:51');
---- DATA FETCHED BY CREATING TEMPORARY TABLE USING SUBQUERY AND TEMPORARY COLUMN TIME_DIFF1
SELECT Room_Id, MAX(TIME_DIFF1) Maximum_Time ,MIN(TIME_DIFF1) Min_Time ,cast(cast(avg(cast(CAST(TIME_DIFF1 as datetime) as float)) as datetime) as time) Avg_Time
FROM
( SELECT ROOM_ID, CAST(DATEADD(SECOND, DATEDIFF(SECOND, Start_Time, Finsh_Time), 0) as TIME) AS TIME_DIFF1
FROM HosptRoom2
)
HosptRoom2 GROUP BY Room_ID;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.