Use SQL fiddle and use the below SQL statements for loading data. Posted the pictures below CREATE and INSERT statements to explain data and details of how to solve Questions 1.1 and 1.2
CREATE TABLE trlr_activity ( trlract_id INT IDENTITY PRIMARY KEY, trndte DATETIME, actcod NVARCHAR(40), trlr_id NVARCHAR(20), wh_id NVARCHAR(32) )
INSERT INTO trlr_activity (trndte, actcod, trlr_id, wh_id) VALUES ('2020-09-24 15:02:30.000', 'CREATED', 'TRL0000002', 'WAREHOUSE_01'), ('2020-09-24 15:06:02.000', 'CHECKED-IN', 'TRL0000002', 'WAREHOUSE_01'), ('2020-10-10 13:39:32.000', 'MOVED', 'TRL0000002', 'WAREHOUSE_01'), ('2020-10-11 19:37:42.000', 'DISPATCHED', 'TRL0000002', 'WAREHOUSE_01'), ('2020-09-30 12:11:51.000', 'CREATED', 'TRL0000009', 'WAREHOUSE_01'), ('2020-09-30 16:26:41.000', 'LOADING STARTED', 'TRL0000009', 'WAREHOUSE_01'), ('2020-09-30 16:26:41.000', 'CHECKED-IN', 'TRL0000009', 'WAREHOUSE_01'), ('2020-09-30 16:34:47.000', 'LOADED', 'TRL0000009', 'WAREHOUSE_01'), ('2020-09-30 16:34:48.000', 'DISPATCHED', 'TRL0000009', 'WAREHOUSE_01'), ('2020-10-01 07:10:53.000', 'CREATED', 'TRL0000011', 'WAREHOUSE_01'), ('2020-10-01 13:10:41.000', 'LOADING STARTED', 'TRL0000011', 'WAREHOUSE_01'), ('2020-10-01 11:05:41.000', 'CHECKED-IN', 'TRL0000011', 'WAREHOUSE_01'), ('2020-10-01 13:18:56.000', 'LOADED', 'TRL0000011', 'WAREHOUSE_01'), ('2020-10-01 13:19:01.000', 'DISPATCHED', 'TRL0000011', 'WAREHOUSE_01'), ('2020-10-02 17:57:38.000', 'CREATED', 'TRL0000014', 'WAREHOUSE_01'), ('2020-10-07 19:51:25.000', 'CREATED', 'TRL0000024', 'WAREHOUSE_01'), ('2020-10-07 20:29:45.000', 'LOADING STARTED', 'TRL0000024', 'WAREHOUSE_01'), ('2020-10-07 20:29:45.000', 'CHECKED-IN', 'TRL0000024', 'WAREHOUSE_01'), ('2020-10-07 20:38:38.000', 'LOADED', 'TRL0000024', 'WAREHOUSE_01'), ('2020-10-07 20:39:28.000', 'DISPATCHED', 'TRL0000024', 'WAREHOUSE_01'), ('2020-10-13 16:01:32.000', 'LOADING STARTED', 'TRL0000060', 'WAREHOUSE_01'), ('2020-10-14 16:06:36.000', 'LOADED', 'TRL0000060', 'WAREHOUSE_01'), ('2020-10-14 16:07:37.000', 'DISPATCHED', 'TRL0000060', 'WAREHOUSE_01'), ('2020-10-12 09:34:01.000', 'CREATED', 'TRL0000062', 'WAREHOUSE_01'), ('2020-10-12 09:38:47.000', 'LOADING STARTED', 'TRL0000062', 'WAREHOUSE_01'), ('2020-10-12 09:38:47.000', 'CHECKED-IN', 'TRL0000062', 'WAREHOUSE_01'), ('2020-10-12 09:41:56.000', 'LOADED', 'TRL0000062', 'WAREHOUSE_01'), ('2020-10-12 15:40:57.000', 'DISPATCHED', 'TRL0000062', 'WAREHOUSE_01'), ('2020-10-12 19:08:35.000', 'CREATED', 'TRL0000065', 'WAREHOUSE_01'), ('2020-10-13 19:30:43.000', 'LOADING STARTED', 'TRL0000065', 'WAREHOUSE_01'), ('2020-10-13 08:30:43.000', 'CHECKED-IN', 'TRL0000065', 'WAREHOUSE_01'), ('2020-10-13 19:55:38.000', 'LOADED', 'TRL0000065', 'WAREHOUSE_01'), ('2020-10-13 22:39:25.000', 'DISPATCHED', 'TRL0000065', 'WAREHOUSE_01'), ('2020-10-18 14:45:08.000', 'CREATED', 'TRL0000088', 'WAREHOUSE_01'), ('2020-10-20 14:47:36.000', 'LOADING STARTED', 'TRL0000088', 'WAREHOUSE_01'), ('2020-10-20 13:30:36.000', 'CHECKED-IN', 'TRL0000088', 'WAREHOUSE_01'), ('2020-10-20 15:50:10.000', 'LOADED', 'TRL0000088', 'WAREHOUSE_01'), ('2020-10-20 16:55:18.000', 'DISPATCHED', 'TRL0000088', 'WAREHOUSE_01'), ('2020-10-21 09:57:17.000', 'CREATED', 'TRL0000092', 'WAREHOUSE_01'), ('2020-10-23 08:31:10.000', 'CREATED', 'TRL0000097', 'WAREHOUSE_01'), ('2020-10-23 08:32:06.000', 'CHECKED-IN', 'TRL0000097', 'WAREHOUSE_01'), ('2020-10-29 14:48:25.000', 'CREATED', 'TRL0000102', 'WAREHOUSE_01'), ('2020-10-29 14:49:22.000', 'CHECKED-IN', 'TRL0000102', 'WAREHOUSE_01'), ('2020-11-03 10:46:24.000', 'DISPATCHED', 'TRL0000102', 'WAREHOUSE_01'), ('2020-11-03 11:37:50.000', 'CREATED', 'TRL0000118', 'WAREHOUSE_01'), ('2020-11-03 14:35:40.000', 'LOADING STARTED', 'TRL0000118', 'WAREHOUSE_01'), ('2020-11-03 12:30:40.000', 'CHECKED-IN', 'TRL0000118', 'WAREHOUSE_01'), ('2020-11-03 14:42:09.000', 'LOADED', 'TRL0000118', 'WAREHOUSE_01'), ('2020-11-03 14:42:10.000', 'DISPATCHED', 'TRL0000118', 'WAREHOUSE_01'), ('2020-11-03 14:55:49.000', 'CREATED', 'TRL0000119', 'WAREHOUSE_01'), ('2020-11-03 14:57:13.000', 'LOADING STARTED', 'TRL0000119', 'WAREHOUSE_01')
Problem 1 - Trailer Dwell-Time Trailer dwell-time is the time between a trailer has been checked-in (moved to the loading door) and the time the trailer has been dispatched (left the facility). You are given a trailer activity table that logs different types of activities for a trailer, along with the date that activity occurred. The normal progression of activities for a trailer are: 1. CREATED - The trailer record has been created but it has not arrived yet at the facility 2. CHECKED-IN The trailer arrived at the facility and was checked into the dock (the start of the dwell-time) 3. MOVED - The trailer has been moved to a different dock door LOADING STARTED - The loading of the trailer has started 5. LOADED - The loading of the trailer has completed 6. DISPATCHED - The trailer has left the facility (this is the end of the dwell-time calculation) 4. Note however that not all the activities need to exist, as well as some activities could be missing for a trailer. The dwell-time will be calculated as follows: If a trailer has both CHECKED-IN and DISPATCHED activities, it would be the time between the earliest trndte associated with CHECKED-IN activity and the earliest date associated with DISPATCHED activity. For example, for trlr_id TRL0000002, the dwell time will be: o 412 hours If a trailer has only CREATED activity, the dwell time will be 0 and this trailer will not be considered for dwell-time aggregations. CREATED transaction means the trailer record has been created, however it did not arrive at the warehouse. If a trailer has CHECKED-IN activity (or any other activity after CREATED, such as MOVED, LOADING STARTED, LOADED), but there is no DISPATCHED activity, the dwell-time will be calculated between the date associated with the earliest activity (that is NOT CREATED) and current timestamp (now). For example, for trlr_id TRL0000097: trlract_id trndte actcod trlr_id 40 41 2020-10-23 08:31:10.000 CREATED 2020-10-23 08:32:06.600 CHECKED-IN TRL0000097 TRL0000097 the dwell time will be 280 hours (rounded-up), assuming now is 2020-11-04 00:00:00: trir_id earliest_date now_date dwell_time_hour dwell_time_min TRL0000097 2020-10-23 08:32:06.000 2020-11-04 00:00:00.000 280 16768 If a trailer has no CHECKED-IN activity, the dwell time will be calculated as the time between the earliest activity that is NOT CREATED, and the DISPATCHED activity. If the DISPATCHED activity is also missing, then the dwell-time will be calculated between the earliest activity (that is NOT CREATED) and current date. o For example, for trir_id TRL0900119: trlract_id trndte actcod trlr_id 50 51 2020-11-03 14155149.800 CREATED 2029-11-03 14157113.680 LOADING STARTED TRL0000119 TRL 6900119 the dwell time will be calculated between the first activity that is NOT CREATED, which is LOADING STARTED, and current date time. Assuming now is 2020-11-04 00:00:00, the dwell-time will be: trir_id earliest_date now_date dwell_time_hour_dwell_time_min TRL0000119 2020-11-03 14:57:13.000 2020-11-04 00:00:00.000 10 o Another example is trlr_id TRL0000060: trlract_id trndte trlr_id 543 actcod 21 22 2020-10-13 16:01:32.600 LOADING STARTED 2020-10-14 16:06:36.600 LOADED 2020-10-14 16:07:37.600 DISPATCHED TRL0000060 TRL0000060 TRL 0000060 ) o The dwell-time will be the time between trndte associated with LOADING STARTED activity (the earliest activity for that trailer that is NOT CREATED), and the time associated with DISPATCHED: trir_id earliest_date dwell_time_hour dwell_time_min TRL0000060 2020-10-13 16:01:32.000 24 1446 trlr_activity table (there is a separate file to CREATE the table and load this sample data): trlract_id trndte actcod trlr_id wh_id WAREHOUSE_01 1 2020-09-24 15:02:30 CREATED TRLOO00082 2 2020-09-24 15:06:02 CHECKED-IN TRL0000002 WAREHOUSE_01 WAREHOUSE_01 3 2020-10-10 13:39:32 MOVED TRL0000002 4 2020-10-11 19:37:42 DISPATCHED TRL0000002 WAREHOUSE_01 WAREHOUSE_01 5 2020-09-30 12:11:51 CREATED TRL0000009 7 2020-09-30 16:26:41 CHECKED-IN TRLO000009 WAREHOUSE_01 6 2020-09-30 16:26:41 LOADING STARTED TRLO000009 WAREHOUSE_01 WAREHOUSE_01 8 LOADED 2020-09-30 16:34:47 2020-09-30 16:34:48 TRL0000009 TRLB006099 9 DISPATCHED WAREHOUSE_01 10 2020-10-01 07:10:53 CREATED TRL0000011 WAREHOUSE_01 WAREHOUSE_01 12 2020-10-01 11:05:41 CHECKED-IN TRLO000011 2 11 2020-10-01 13:10:41 LOADING STARTED TRL0000011 WAREHOUSE_01 13 2020-10-01 13:18:56 LOADED TRL0000011 WAREHOUSE_01 14 2020-10-01 13:19:01 DISPATCHED TRL0000011 WAREHOUSE_01 15 2020-10-02 17:57:38 CREATED TRL0000014 WAREHOUSE_01 16 2020-10-07 19:51:25 CREATED TRL0000024 WAREHOUSE_01 18 2020-10-07 20:29:45 CHECKED-IN TRL0000024 WAREHOUSE_01 17 2020-10-07 20:29:45 LOADING STARTED TRL0000024 WAREHOUSE_01 19 2020-10-07 20:38:38 LOADED TRL0000024 WAREHOUSE_01 WAREHOUSE_01 20 2020-10-07 20:39:28 DISPATCHED TRL0000024 21 2020-10-13 16:01:32 LOADING STARTED TRL0000060 WAREHOUSE_01 WAREHOUSE_01 22 2020-10-14 16:06:36 LOADED TRL0000060 23 2020-10-14 16:07:37 DISPATCHED TRL0000060 WAREHOUSE_01 WAREHOUSE_01 24 2020-10-12 09:34:01 CREATED TRL0000062 26 2020-10-12 09:38:47 CHECKED-IN TRL 6006062 25 2020-10-12 09:38:47 LOADING STARTED TRL0000062 WAREHOUSE_01 WAREHOUSE_01 WAREHOUSE_01 27 2020-10-12 09:41:56 LOADED TRL0000062 28 2020-10-12 15:40:57 DISPATCHED TRL0000062 WAREHOUSE_01 29 CREATED 2020-10-12 19:08:35 2020-10-13 08:30:43 TRL0000065 TRL0000065 WAREHOUSE_01 WAREHOUSE_01 31 CHECKED-IN 30 2020-10-13 19:39:43 LOADING STARTED TRL0000065 WAREHOUSE_01 32 2020-10-13 19:55:38 LOADED TRL0000065 WAREHOUSE_01 WAREHOUSE_01 33 2020-10-13 22:39:25 DISPATCHED TRL0000065 34 2020-10-18 14:45:08 CREATED TRL0000088 WAREHOUSE_01 WAREHOUSE_01 36 2020-10-20 13:30:36 CHECKED-IN TRL0000088 35 2020-10-20 14:47:36 LOADING STARTED TRL0008088 WAREHOUSE_01 37 2020-10-20 15:50:10 LOADED TRLOO00088 WAREHOUSE_01 38 2020-10-20 16:55:18 DISPATCHED TRL0006988 WAREHOUSE_01 39 2020-10-21 09:57:17 CREATED TRL0006092 WAREHOUSE_01 WAREHOUSE_01 40 2020-10-23 08:31:10 CREATED TRL0000097 41 2020-10-23 08:32:06 CHECKED-IN TRL0000097 WAREHOUSE_01 42 2020-10-29 14:48:25 CREATED TRL0000102 WAREHOUSE_01 WAREHOUSE_01 43 2020-10-29 14:49:22 CHECKED-IN TRL0009102 44 2020-11-03 10:46:24 DISPATCHED TRL0009102 WAREHOUSE_01 45 2020-11-03 11:37:50 CREATED TRL0009118 WAREHOUSE_01 47 2020-11-03 12:30:40 CHECKED-IN TRL0009118 WAREHOUSE_01 46 2020-11-03 14:35:40 LOADING STARTED TRL0009118 WAREHOUSE_01 WAREHOUSE_01 48 2020-11-03 14:42:09 LOADED TRL0000118 49 DISPATCHED TRLO000118 2020-11-03 14:42:10 2020-11-03 14:55:49 WAREHOUSE_01 WAREHOUSE_01 50 CREATED TRL0000119 51 2020-11-03 14:57:13 LOADING STARTED TRLO000119 WAREHOUSE_01 Question 1.1 For each trailer, show the dwell time in hours and minutes, as well as, whether the trailer is currently at a door or not: this will be "Y" if the trailer has an activity after it was CREATED, but no DISPATCHED activity, and "N" if it was DISPATCHED. Exclude any trailers that only have CREATED activity, and no other activity at all. This is the output given the sample data (current date was 2020-11-04 00:00:00): trlr_id dwell_time_hour dwell_time_min at_door now_date 412 2 TRL0000002 TRL0000009 TRL0000011 TRL0000024 TRL0000060 TRL0000062 TRL0000065 TRL0000088 TRL0000097 TRL0000102 TRL0000118 TRL0000119 24 6 14 3 280 24751 8 134 10 1446 362 849 205 16768 6957 132 543 N N N N N N N N Y N N 2020-11-04 00:00:00.000 2020-11-04 00:00:00.000 2020-11-04 00:00:00.000 2020-11-04 00:00:00.000 2020-11-04 00:00:00.000 2020-11-04 00:00:00.000 2020-11-04 00:00:00.000 2020-11-04 00:00:00.000 2020-11-04 00:00:00.000 2020-11-04 00:00:00.000 2020-11-04 00:00:00.000 2020-11-04 00:00:00.000 116 2 10 Question 1.2 Calculate the average dwell time grouped by month of the earliest activity associated with each trailer. Note that trailers that only have CREATED activity are not included in the below calculation. Include the following fields in your query: month_val aggregated month of the earliest activity for each trailer total_trailers - total distinct trailers that were processed on that month total_dispatched_trailers - total distinct trailers that have been dispatched average_overall_dwell_hour average dwell time (in hours) for all trailers average_overall_dwell_min - average dwell time (in minutes) for all trailers average_dispatched_dwell_hour average dwell time (in hours) for just the dispatched trailers average_dispatched_dwell_min - average dwell time (in minutes) for just the dispatched trailers Given the sample data, this should be the output of your query: . month_value total_trailers total_dispatched_trailers average_overall_dwell_hour average_overall_twol_min average_dispatched_two_hour average_dispatched_twell_min 9 2 2 206 12379 206 12379 10 8 7 55 30341 23 1423 11 1 6 337 2 132 2 Problem 1 - Trailer Dwell-Time Trailer dwell-time is the time between a trailer has been checked-in (moved to the loading door) and the time the trailer has been dispatched (left the facility). You are given a trailer activity table that logs different types of activities for a trailer, along with the date that activity occurred. The normal progression of activities for a trailer are: 1. CREATED - The trailer record has been created but it has not arrived yet at the facility 2. CHECKED-IN The trailer arrived at the facility and was checked into the dock (the start of the dwell-time) 3. MOVED - The trailer has been moved to a different dock door LOADING STARTED - The loading of the trailer has started 5. LOADED - The loading of the trailer has completed 6. DISPATCHED - The trailer has left the facility (this is the end of the dwell-time calculation) 4. Note however that not all the activities need to exist, as well as some activities could be missing for a trailer. The dwell-time will be calculated as follows: If a trailer has both CHECKED-IN and DISPATCHED activities, it would be the time between the earliest trndte associated with CHECKED-IN activity and the earliest date associated with DISPATCHED activity. For example, for trlr_id TRL0000002, the dwell time will be: o 412 hours If a trailer has only CREATED activity, the dwell time will be 0 and this trailer will not be considered for dwell-time aggregations. CREATED transaction means the trailer record has been created, however it did not arrive at the warehouse. If a trailer has CHECKED-IN activity (or any other activity after CREATED, such as MOVED, LOADING STARTED, LOADED), but there is no DISPATCHED activity, the dwell-time will be calculated between the date associated with the earliest activity (that is NOT CREATED) and current timestamp (now). For example, for trlr_id TRL0000097: trlract_id trndte actcod trlr_id 40 41 2020-10-23 08:31:10.000 CREATED 2020-10-23 08:32:06.600 CHECKED-IN TRL0000097 TRL0000097 the dwell time will be 280 hours (rounded-up), assuming now is 2020-11-04 00:00:00: trir_id earliest_date now_date dwell_time_hour dwell_time_min TRL0000097 2020-10-23 08:32:06.000 2020-11-04 00:00:00.000 280 16768 If a trailer has no CHECKED-IN activity, the dwell time will be calculated as the time between the earliest activity that is NOT CREATED, and the DISPATCHED activity. If the DISPATCHED activity is also missing, then the dwell-time will be calculated between the earliest activity (that is NOT CREATED) and current date. o For example, for trir_id TRL0900119: trlract_id trndte actcod trlr_id 50 51 2020-11-03 14155149.800 CREATED 2029-11-03 14157113.680 LOADING STARTED TRL0000119 TRL 6900119 the dwell time will be calculated between the first activity that is NOT CREATED, which is LOADING STARTED, and current date time. Assuming now is 2020-11-04 00:00:00, the dwell-time will be: trir_id earliest_date now_date dwell_time_hour_dwell_time_min TRL0000119 2020-11-03 14:57:13.000 2020-11-04 00:00:00.000 10 o Another example is trlr_id TRL0000060: trlract_id trndte trlr_id 543 actcod 21 22 2020-10-13 16:01:32.600 LOADING STARTED 2020-10-14 16:06:36.600 LOADED 2020-10-14 16:07:37.600 DISPATCHED TRL0000060 TRL0000060 TRL 0000060 ) o The dwell-time will be the time between trndte associated with LOADING STARTED activity (the earliest activity for that trailer that is NOT CREATED), and the time associated with DISPATCHED: trir_id earliest_date dwell_time_hour dwell_time_min TRL0000060 2020-10-13 16:01:32.000 24 1446 trlr_activity table (there is a separate file to CREATE the table and load this sample data): trlract_id trndte actcod trlr_id wh_id WAREHOUSE_01 1 2020-09-24 15:02:30 CREATED TRLOO00082 2 2020-09-24 15:06:02 CHECKED-IN TRL0000002 WAREHOUSE_01 WAREHOUSE_01 3 2020-10-10 13:39:32 MOVED TRL0000002 4 2020-10-11 19:37:42 DISPATCHED TRL0000002 WAREHOUSE_01 WAREHOUSE_01 5 2020-09-30 12:11:51 CREATED TRL0000009 7 2020-09-30 16:26:41 CHECKED-IN TRLO000009 WAREHOUSE_01 6 2020-09-30 16:26:41 LOADING STARTED TRLO000009 WAREHOUSE_01 WAREHOUSE_01 8 LOADED 2020-09-30 16:34:47 2020-09-30 16:34:48 TRL0000009 TRLB006099 9 DISPATCHED WAREHOUSE_01 10 2020-10-01 07:10:53 CREATED TRL0000011 WAREHOUSE_01 WAREHOUSE_01 12 2020-10-01 11:05:41 CHECKED-IN TRLO000011 2 11 2020-10-01 13:10:41 LOADING STARTED TRL0000011 WAREHOUSE_01 13 2020-10-01 13:18:56 LOADED TRL0000011 WAREHOUSE_01 14 2020-10-01 13:19:01 DISPATCHED TRL0000011 WAREHOUSE_01 15 2020-10-02 17:57:38 CREATED TRL0000014 WAREHOUSE_01 16 2020-10-07 19:51:25 CREATED TRL0000024 WAREHOUSE_01 18 2020-10-07 20:29:45 CHECKED-IN TRL0000024 WAREHOUSE_01 17 2020-10-07 20:29:45 LOADING STARTED TRL0000024 WAREHOUSE_01 19 2020-10-07 20:38:38 LOADED TRL0000024 WAREHOUSE_01 WAREHOUSE_01 20 2020-10-07 20:39:28 DISPATCHED TRL0000024 21 2020-10-13 16:01:32 LOADING STARTED TRL0000060 WAREHOUSE_01 WAREHOUSE_01 22 2020-10-14 16:06:36 LOADED TRL0000060 23 2020-10-14 16:07:37 DISPATCHED TRL0000060 WAREHOUSE_01 WAREHOUSE_01 24 2020-10-12 09:34:01 CREATED TRL0000062 26 2020-10-12 09:38:47 CHECKED-IN TRL 6006062 25 2020-10-12 09:38:47 LOADING STARTED TRL0000062 WAREHOUSE_01 WAREHOUSE_01 WAREHOUSE_01 27 2020-10-12 09:41:56 LOADED TRL0000062 28 2020-10-12 15:40:57 DISPATCHED TRL0000062 WAREHOUSE_01 29 CREATED 2020-10-12 19:08:35 2020-10-13 08:30:43 TRL0000065 TRL0000065 WAREHOUSE_01 WAREHOUSE_01 31 CHECKED-IN 30 2020-10-13 19:39:43 LOADING STARTED TRL0000065 WAREHOUSE_01 32 2020-10-13 19:55:38 LOADED TRL0000065 WAREHOUSE_01 WAREHOUSE_01 33 2020-10-13 22:39:25 DISPATCHED TRL0000065 34 2020-10-18 14:45:08 CREATED TRL0000088 WAREHOUSE_01 WAREHOUSE_01 36 2020-10-20 13:30:36 CHECKED-IN TRL0000088 35 2020-10-20 14:47:36 LOADING STARTED TRL0008088 WAREHOUSE_01 37 2020-10-20 15:50:10 LOADED TRLOO00088 WAREHOUSE_01 38 2020-10-20 16:55:18 DISPATCHED TRL0006988 WAREHOUSE_01 39 2020-10-21 09:57:17 CREATED TRL0006092 WAREHOUSE_01 WAREHOUSE_01 40 2020-10-23 08:31:10 CREATED TRL0000097 41 2020-10-23 08:32:06 CHECKED-IN TRL0000097 WAREHOUSE_01 42 2020-10-29 14:48:25 CREATED TRL0000102 WAREHOUSE_01 WAREHOUSE_01 43 2020-10-29 14:49:22 CHECKED-IN TRL0009102 44 2020-11-03 10:46:24 DISPATCHED TRL0009102 WAREHOUSE_01 45 2020-11-03 11:37:50 CREATED TRL0009118 WAREHOUSE_01 47 2020-11-03 12:30:40 CHECKED-IN TRL0009118 WAREHOUSE_01 46 2020-11-03 14:35:40 LOADING STARTED TRL0009118 WAREHOUSE_01 WAREHOUSE_01 48 2020-11-03 14:42:09 LOADED TRL0000118 49 DISPATCHED TRLO000118 2020-11-03 14:42:10 2020-11-03 14:55:49 WAREHOUSE_01 WAREHOUSE_01 50 CREATED TRL0000119 51 2020-11-03 14:57:13 LOADING STARTED TRLO000119 WAREHOUSE_01 Question 1.1 For each trailer, show the dwell time in hours and minutes, as well as, whether the trailer is currently at a door or not: this will be "Y" if the trailer has an activity after it was CREATED, but no DISPATCHED activity, and "N" if it was DISPATCHED. Exclude any trailers that only have CREATED activity, and no other activity at all. This is the output given the sample data (current date was 2020-11-04 00:00:00): trlr_id dwell_time_hour dwell_time_min at_door now_date 412 2 TRL0000002 TRL0000009 TRL0000011 TRL0000024 TRL0000060 TRL0000062 TRL0000065 TRL0000088 TRL0000097 TRL0000102 TRL0000118 TRL0000119 24 6 14 3 280 24751 8 134 10 1446 362 849 205 16768 6957 132 543 N N N N N N N N Y N N 2020-11-04 00:00:00.000 2020-11-04 00:00:00.000 2020-11-04 00:00:00.000 2020-11-04 00:00:00.000 2020-11-04 00:00:00.000 2020-11-04 00:00:00.000 2020-11-04 00:00:00.000 2020-11-04 00:00:00.000 2020-11-04 00:00:00.000 2020-11-04 00:00:00.000 2020-11-04 00:00:00.000 2020-11-04 00:00:00.000 116 2 10 Question 1.2 Calculate the average dwell time grouped by month of the earliest activity associated with each trailer. Note that trailers that only have CREATED activity are not included in the below calculation. Include the following fields in your query: month_val aggregated month of the earliest activity for each trailer total_trailers - total distinct trailers that were processed on that month total_dispatched_trailers - total distinct trailers that have been dispatched average_overall_dwell_hour average dwell time (in hours) for all trailers average_overall_dwell_min - average dwell time (in minutes) for all trailers average_dispatched_dwell_hour average dwell time (in hours) for just the dispatched trailers average_dispatched_dwell_min - average dwell time (in minutes) for just the dispatched trailers Given the sample data, this should be the output of your query: . month_value total_trailers total_dispatched_trailers average_overall_dwell_hour average_overall_twol_min average_dispatched_two_hour average_dispatched_twell_min 9 2 2 206 12379 206 12379 10 8 7 55 30341 23 1423 11 1 6 337 2 132 2
