· 7 years ago · Dec 03, 2018, 06:26 PM
1Skip to content
2
3Search or jump to…
4
5Pull requests
6Issues
7Marketplace
8Explore
9 @elenest Sign out
103
11🔖 Release-only subscription
12You can now limit a repository's notifications exclusively to releases. Use it to follow your favorite repositories!
13
140 0 Shopfans/splurge Private
15 Code Issues 0 Pull requests 0 Projects 0 Wiki Insights
16splurge/app/sql/bi/sla.sql
17b7f9434 on 20 Apr
18 Webster Modify column name in sla.sql
19
201741 lines (1512 sloc) 57.8 KB
21
22
23-- Aggregate the status changes by day
24
25DROP TABLE IF EXISTS audit_operations_status_changes;
26
27CREATE TABLE audit_operations_status_changes
28AS
29SELECT
30 DATE_TRUNC('day', dts) AS day,
31 entity_name AS entity_name,
32 operation AS operation,
33 COUNT(DISTINCT entity_id) AS entity_cnt
34FROM
35 flydata_shoporama.audit
36WHERE
37 REGEXP_INSTR(operation, '^Status changed') > 0
38GROUP BY
39 day,
40 entity_name,
41 operation
42ORDER BY
43 operation,
44 entity_name,
45 day;
46
47
48-- w/o Shopfans Lite shipments
49
50DROP TABLE IF EXISTS audit_operations_status_changed_no_sflite;
51
52CREATE TABLE audit_operations_status_changed_no_sflite
53AS
54WITH audit_packages AS (
55 SELECT
56 audit.*
57 FROM
58 flydata_shoporama.audit AS audit
59 WHERE
60 entity_name = 'package'
61),
62audit_shipments AS (
63 SELECT
64 audit.*
65 FROM
66 flydata_shoporama.audit AS audit
67 LEFT JOIN
68 event_shoporama_shipments AS shipments
69 ON
70 audit.entity_id = shipments.id
71 WHERE
72 entity_name = 'shipment' AND
73 type != 'Shopfans Lite'
74)
75SELECT
76 DATE_TRUNC('day', dts) AS day,
77 entity_name AS entity_name,
78 operation AS operation,
79 COUNT(DISTINCT entity_id) AS entity_cnt
80FROM
81(
82 (SELECT * FROM audit_packages)
83 UNION
84 (SELECT * FROM audit_shipments)
85)
86WHERE
87 REGEXP_INSTR(operation, '^Status changed') > 0
88GROUP BY
89 day,
90 entity_name,
91 operation
92ORDER BY
93 operation,
94 entity_name,
95 day;
96
97
98-- Calculate duration of each step in our shipping process
99
100DROP TABLE IF EXISTS sla_packages;
101
102CREATE TABLE sla_packages
103AS
104SELECT
105 packages.id AS package_id,
106 packages.day AS package_day,
107 packages.week AS package_week,
108 packages.month AS package_month,
109 packages.year AS package_year,
110 packages.season AS package_season,
111 shipments.id AS shipment_id,
112 shipments.type AS shipment_type,
113
114 shipments.batch_id AS batch_id,
115 shipments.batch_number AS batch_number,
116 shipments.batch_mawb AS batch_mawb,
117 shipments.batch_status_name AS batch_status_name,
118 shipments.batch_partner_id AS batch_partner_id,
119 shipments.batch_broker_name AS batch_broker_name,
120 shipments.batch_destination_country_id AS batch_destination_country_id,
121
122 shipments.delivery_customer_pickup AS delivery_customer_pickup,
123 shipments.delivery_center_id AS delivery_center_id,
124 shipments.delivery_partner_id AS delivery_partner_id,
125 shipments.delivery_shiptype_code AS delivery_shiptype_code,
126 shipments.delivery_external_tracking AS delivery_external_tracking,
127
128 shipments.address_city AS address_city,
129
130 shipments.delivery_status AS delivery_status,
131 shipments.delivery_total_cost AS delivery_total_cost,
132 shipments.delivery_cost AS delivery_cost,
133
134 packages.weight_oz AS package_weight_oz,
135 packages.weight_lbs AS package_weight_lbs,
136 packages.weight_kg AS package_weight_kg,
137
138 CASE
139 WHEN packages.sfx_lite_ready_time IS NOT NULL
140 THEN TRUE
141 ELSE FALSE
142 END
143 AS sfl_ready,
144 packages.awaiting_arrival_time AS package_awaiting_arrival_time,
145 packages.arrival_time AS package_arrival_time,
146 packages.sfx_lite_ready_time AS package_sfl_ready_time,
147
148 shipments.created_time AS created_time,
149 shipments.consolidation_time AS consolidation_time,
150 shipments.sfx_lite_processing_started_time AS sfl_processing_started_time,
151
152 shipments.conso_started_time AS conso_started_time,
153 shipments.conso_closed_time AS conso_closed_time,
154 shipments.packed_time AS packed_time,
155 shipments.payment_required_time AS payment_required_time,
156 shipments.processed_time AS processed_time,
157 shipments.sfx_review_approved_time AS sfx_review_approved_time,
158 shipments.sfx_review_failed_time AS sfx_review_failed_time,
159 shipments.shipped_time AS shipped_time,
160
161 shipments.in_transit_time AS in_transit_time,
162 shipments.region_transit_time AS region_transit_time,
163 shipments.on_delivery_stock_time AS on_delivery_stock_time,
164 shipments.exported_to_ups_time AS exported_to_ups_time,
165
166 shipments.manifest_uploaded_time AS manifest_uploaded_time,
167 shipments.checked_into_warehouse_time AS checked_into_warehouse_time,
168 shipments.ready_to_ship_time AS ready_to_ship_time,
169 shipments.dispatched_time AS dispatched_time,
170 shipments.customs_data_obtained_time AS customs_data_obtained_time,
171 shipments.shipment_exported_us_time AS exported_us_time,
172 shipments.shipment_arrived_at_customs_time AS arrived_at_customs_time,
173 shipments.missing_customs_data_time AS missing_customs_data_time,
174 shipments.held_at_customs_time AS held_at_customs_time,
175 shipments.passed_customs_time AS passed_customs_time,
176 shipments.sorting_time AS sorting_time,
177 shipments.sorting_for_pickup_point_time AS sorting_for_pickup_point_time,
178 shipments.transit_time AS transit_time,
179 shipments.transit_to_pickup_point_time AS transit_to_pickup_point_time,
180 shipments.in_stock_time AS in_stock_time,
181 shipments.checked_in_at_spp_time AS checked_in_at_spp_time,
182 shipments.ready_for_delivery_time AS ready_for_delivery_time,
183 shipments.transfer_to_car_time AS transfer_to_car_time,
184 shipments.in_route_time AS in_route_time,
185 shipments.delivery_failed_time AS delivery_failed_time,
186 shipments.returned_time AS returned_time,
187 shipments.delivered_time AS delivered_time,
188
189 DATEDIFF('minutes', packages.awaiting_arrival_time, packages.arrival_time) AS minutes_awaiting_arrival,
190
191 DATEDIFF('minutes', packages.arrival_time, shipments.created_time) AS minutes_arrival_to_shipment_created,
192
193 DATEDIFF('minutes', shipments.created_time, shipments.consolidation_time) AS minutes_until_picking,
194
195 DATEDIFF('minutes', shipments.consolidation_time, shipments.conso_started_time) AS minutes_picking,
196
197 DATEDIFF('minutes', shipments.conso_started_time, shipments.conso_closed_time) AS minutes_consolidating,
198
199 DATEDIFF('minutes', shipments.conso_closed_time, shipments.packed_time) AS minutes_packing,
200
201 DATEDIFF('minutes', shipments.packed_time, shipments.processed_time) AS minutes_until_processed,
202
203 DATEDIFF('minutes', shipments.payment_required_time, shipments.processed_time) AS minutes_waiting_for_payment,
204
205 DATEDIFF('minutes', shipments.packed_time, shipments.processed_time) - DATEDIFF('minutes', shipments.payment_required_time, shipments.processed_time)
206 AS minutes_processing,
207
208 DATEDIFF('minutes', shipments.processed_time, shipments.sfx_review_approved_time) AS minutes_waiting_for_broker,
209
210 DATEDIFF('minutes', shipments.processed_time, shipments.shipped_time) AS minutes_until_shipped,
211
212 DATEDIFF('minutes', shipments.shipped_time, shipments.in_transit_time) AS minutes_until_in_transit,
213
214 DATEDIFF('minutes', shipments.shipped_time, shipments.passed_customs_time) AS minutes_until_passed_customs,
215
216 DATEDIFF('minutes', shipments.shipped_time, shipments.region_transit_time) AS minutes_until_region_transit,
217
218 -- All shopfans lite packages have passed_customs_time, on_delivery_stock_time, delivered_time
219
220 DATEDIFF('minutes', shipments.passed_customs_time, shipments.on_delivery_stock_time) AS minutes_until_on_delivery_stock,
221
222 DATEDIFF('minutes', shipments.on_delivery_stock_time, shipments.delivered_time) AS minutes_until_on_delivered
223FROM
224 event_shoporama_packages AS packages
225LEFT JOIN
226(
227 SELECT DISTINCT
228 shipment_id,
229 ship_type_id,
230 ship_type_name,
231 package_id
232 FROM
233 shipment_spec
234) AS ss
235ON
236 packages.id = ss.package_id
237LEFT JOIN
238 event_shoporama_shipments_all AS shipments
239ON
240 ss.shipment_id = shipments.id
241WHERE
242 packages.time IS NOT NULL
243ORDER BY
244 type,
245 package_day DESC;
246
247--
248
249DROP TABLE IF EXISTS sla_packages_by_day_shipment_type;
250
251CREATE TABLE sla_packages_by_day_shipment_type
252AS
253WITH totals_by_day_type AS (
254 SELECT
255 package_day AS package_day,
256 shipment_type AS shipment_type,
257 COUNT(DISTINCT package_id) AS packages,
258 COUNT(DISTINCT shipment_id) AS shipments
259 FROM
260 sla_packages
261 GROUP BY
262 package_day,
263 shipment_type
264 ORDER BY
265 shipment_type,
266 package_day
267),
268minutes_awaiting_arrival_percentiles AS (
269 SELECT
270 package_day,
271 shipment_type,
272
273 COUNT(*) AS minutes_awaiting_arrival_n,
274
275 PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY minutes_awaiting_arrival)
276 AS minutes_awaiting_arrival_median,
277
278 PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY minutes_awaiting_arrival)
279 AS minutes_awaiting_arrival_90th_percentile
280 FROM
281 sla_packages
282 WHERE
283 minutes_awaiting_arrival IS NOT NULL
284 GROUP BY
285 package_day,
286 shipment_type
287 ORDER BY
288 shipment_type,
289 package_day
290),
291minutes_arrival_to_shipment_created_percentiles AS (
292 SELECT
293 package_day,
294 shipment_type,
295
296 COUNT(*) AS minutes_arrival_to_shipment_created_n,
297
298 PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY minutes_arrival_to_shipment_created)
299 AS minutes_arrival_to_shipment_created_median,
300
301 PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY minutes_arrival_to_shipment_created)
302 AS minutes_arrival_to_shipment_created_90th_percentile
303 FROM
304 sla_packages
305 WHERE
306 minutes_arrival_to_shipment_created IS NOT NULL
307 GROUP BY
308 package_day,
309 shipment_type
310 ORDER BY
311 shipment_type,
312 package_day
313),
314minutes_until_picking_percentiles AS (
315 SELECT
316 package_day,
317 shipment_type,
318
319 COUNT(*) AS minutes_until_picking_n,
320
321 PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY minutes_until_picking)
322 AS minutes_until_picking_median,
323
324 PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY minutes_until_picking)
325 AS minutes_until_picking_90th_percentile
326 FROM
327 sla_packages
328 WHERE
329 minutes_until_picking IS NOT NULL
330 GROUP BY
331 package_day,
332 shipment_type
333 ORDER BY
334 shipment_type,
335 package_day
336),
337minutes_picking_percentiles AS (
338 SELECT
339 package_day,
340 shipment_type,
341
342 COUNT(*) AS minutes_picking_n,
343
344 PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY minutes_picking)
345 AS minutes_picking_median,
346
347 PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY minutes_picking)
348 AS minutes_picking_90th_percentile
349 FROM
350 sla_packages
351 WHERE
352 minutes_picking IS NOT NULL
353 GROUP BY
354 package_day,
355 shipment_type
356 ORDER BY
357 shipment_type,
358 package_day
359),
360minutes_consolidating_percentiles AS (
361 SELECT
362 package_day,
363 shipment_type,
364
365 COUNT(*) AS minutes_consolidating_n,
366
367 PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY minutes_consolidating)
368 AS minutes_consolidating_median,
369
370 PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY minutes_consolidating)
371 AS minutes_consolidating_90th_percentile
372 FROM
373 sla_packages
374 WHERE
375 minutes_consolidating IS NOT NULL
376 GROUP BY
377 package_day,
378 shipment_type
379 ORDER BY
380 shipment_type,
381 package_day
382),
383minutes_packing_percentiles AS (
384 SELECT
385 package_day,
386 shipment_type,
387
388 COUNT(*) AS minutes_packing_n,
389
390 PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY minutes_packing)
391 AS minutes_packing_median,
392
393 PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY minutes_packing)
394 AS minutes_packing_90th_percentile
395 FROM
396 sla_packages
397 WHERE
398 minutes_packing IS NOT NULL
399 GROUP BY
400 package_day,
401 shipment_type
402 ORDER BY
403 shipment_type,
404 package_day
405),
406minutes_until_processed_percentiles AS (
407 SELECT
408 package_day,
409 shipment_type,
410
411 COUNT(*) AS minutes_until_processed_n,
412
413 PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY minutes_until_processed)
414 AS minutes_until_processed_median,
415
416 PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY minutes_until_processed)
417 AS minutes_until_processed_90th_percentile
418 FROM
419 sla_packages
420 WHERE
421 minutes_until_processed IS NOT NULL
422 GROUP BY
423 package_day,
424 shipment_type
425 ORDER BY
426 shipment_type,
427 package_day
428),
429minutes_waiting_for_payment_percentiles AS (
430 SELECT
431 package_day,
432 shipment_type,
433
434 COUNT(*) AS minutes_waiting_for_payment_n,
435
436 PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY minutes_waiting_for_payment)
437 AS minutes_waiting_for_payment_median,
438
439 PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY minutes_waiting_for_payment)
440 AS minutes_waiting_for_payment_90th_percentile
441 FROM
442 sla_packages
443 WHERE
444 minutes_waiting_for_payment IS NOT NULL
445 GROUP BY
446 package_day,
447 shipment_type
448 ORDER BY
449 shipment_type,
450 package_day
451),
452minutes_processing_percentiles AS (
453 SELECT
454 package_day,
455 shipment_type,
456
457 COUNT(*) AS minutes_processing_n,
458
459 PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY minutes_processing)
460 AS minutes_processing_median,
461
462 PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY minutes_processing)
463 AS minutes_processing_90th_percentile
464 FROM
465 sla_packages
466 WHERE
467 minutes_processing IS NOT NULL
468 GROUP BY
469 package_day,
470 shipment_type
471 ORDER BY
472 shipment_type,
473 package_day
474),
475minutes_waiting_for_broker_percentiles AS (
476 SELECT
477 package_day,
478 shipment_type,
479
480 COUNT(*) AS minutes_waiting_for_broker_n,
481
482 PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY minutes_waiting_for_broker)
483 AS minutes_waiting_for_broker_median,
484
485 PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY minutes_waiting_for_broker)
486 AS minutes_waiting_for_broker_90th_percentile
487 FROM
488 sla_packages
489 WHERE
490 minutes_waiting_for_broker IS NOT NULL
491 GROUP BY
492 package_day,
493 shipment_type
494 ORDER BY
495 shipment_type,
496 package_day
497),
498minutes_until_shipped_percentiles AS (
499 SELECT
500 package_day,
501 shipment_type,
502
503 COUNT(*) AS minutes_until_shipped_n,
504
505 PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY minutes_until_shipped)
506 AS minutes_until_shipped_median,
507
508 PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY minutes_until_shipped)
509 AS minutes_until_shipped_90th_percentile
510 FROM
511 sla_packages
512 WHERE
513 minutes_until_shipped IS NOT NULL
514 GROUP BY
515 package_day,
516 shipment_type
517 ORDER BY
518 shipment_type,
519 package_day
520)
521SELECT
522 totals.package_day AS package_day,
523 totals.shipment_type AS shipment_type,
524 totals.packages AS packages,
525 totals.shipments AS shipments,
526
527 minutes_awaiting_arrival_percentiles.minutes_awaiting_arrival_n,
528 minutes_awaiting_arrival_percentiles.minutes_awaiting_arrival_median,
529 minutes_awaiting_arrival_percentiles.minutes_awaiting_arrival_90th_percentile,
530
531 minutes_arrival_to_shipment_created_percentiles.minutes_arrival_to_shipment_created_n,
532 minutes_arrival_to_shipment_created_percentiles.minutes_arrival_to_shipment_created_median,
533 minutes_arrival_to_shipment_created_percentiles.minutes_arrival_to_shipment_created_90th_percentile,
534
535 minutes_until_picking_percentiles.minutes_until_picking_n,
536 minutes_until_picking_percentiles.minutes_until_picking_median,
537 minutes_until_picking_percentiles.minutes_until_picking_90th_percentile,
538
539 minutes_picking_percentiles.minutes_picking_n,
540 minutes_picking_percentiles.minutes_picking_median,
541 minutes_picking_percentiles.minutes_picking_90th_percentile,
542
543 minutes_consolidating_percentiles.minutes_consolidating_n,
544 minutes_consolidating_percentiles.minutes_consolidating_median,
545 minutes_consolidating_percentiles.minutes_consolidating_90th_percentile,
546
547 minutes_packing_percentiles.minutes_packing_n,
548 minutes_packing_percentiles.minutes_packing_median,
549 minutes_packing_percentiles.minutes_packing_90th_percentile,
550
551 minutes_until_processed_percentiles.minutes_until_processed_n,
552 minutes_until_processed_percentiles.minutes_until_processed_median,
553 minutes_until_processed_percentiles.minutes_until_processed_90th_percentile,
554
555 minutes_waiting_for_payment_percentiles.minutes_waiting_for_payment_n,
556 minutes_waiting_for_payment_percentiles.minutes_waiting_for_payment_median,
557 minutes_waiting_for_payment_percentiles.minutes_waiting_for_payment_90th_percentile,
558
559 minutes_processing_percentiles.minutes_processing_n,
560 minutes_processing_percentiles.minutes_processing_median,
561 minutes_processing_percentiles.minutes_processing_90th_percentile,
562
563 minutes_waiting_for_broker_percentiles.minutes_waiting_for_broker_n,
564 minutes_waiting_for_broker_percentiles.minutes_waiting_for_broker_median,
565 minutes_waiting_for_broker_percentiles.minutes_waiting_for_broker_90th_percentile,
566
567 minutes_until_shipped_percentiles.minutes_until_shipped_n,
568 minutes_until_shipped_percentiles.minutes_until_shipped_median,
569 minutes_until_shipped_percentiles.minutes_until_shipped_90th_percentile
570FROM
571 totals_by_day_type AS totals
572LEFT JOIN
573 minutes_awaiting_arrival_percentiles
574ON
575 totals.package_day = minutes_awaiting_arrival_percentiles.package_day AND
576 totals.shipment_type = minutes_awaiting_arrival_percentiles.shipment_type
577LEFT JOIN
578 minutes_arrival_to_shipment_created_percentiles
579ON
580 totals.package_day = minutes_arrival_to_shipment_created_percentiles.package_day AND
581 totals.shipment_type = minutes_arrival_to_shipment_created_percentiles.shipment_type
582LEFT JOIN
583 minutes_until_picking_percentiles
584ON
585 totals.package_day = minutes_until_picking_percentiles.package_day AND
586 totals.shipment_type = minutes_until_picking_percentiles.shipment_type
587LEFT JOIN
588 minutes_picking_percentiles
589ON
590 totals.package_day = minutes_picking_percentiles.package_day AND
591 totals.shipment_type = minutes_picking_percentiles.shipment_type
592LEFT JOIN
593 minutes_consolidating_percentiles
594ON
595 totals.package_day = minutes_consolidating_percentiles.package_day AND
596 totals.shipment_type = minutes_consolidating_percentiles.shipment_type
597LEFT JOIN
598 minutes_packing_percentiles
599ON
600 totals.package_day = minutes_packing_percentiles.package_day AND
601 totals.shipment_type = minutes_packing_percentiles.shipment_type
602LEFT JOIN
603 minutes_until_processed_percentiles
604ON
605 totals.package_day = minutes_until_processed_percentiles.package_day AND
606 totals.shipment_type = minutes_until_processed_percentiles.shipment_type
607LEFT JOIN
608 minutes_waiting_for_payment_percentiles
609ON
610 totals.package_day = minutes_waiting_for_payment_percentiles.package_day AND
611 totals.shipment_type = minutes_waiting_for_payment_percentiles.shipment_type
612LEFT JOIN
613 minutes_processing_percentiles
614ON
615 totals.package_day = minutes_processing_percentiles.package_day AND
616 totals.shipment_type = minutes_processing_percentiles.shipment_type
617LEFT JOIN
618 minutes_waiting_for_broker_percentiles
619ON
620 totals.package_day = minutes_waiting_for_broker_percentiles.package_day AND
621 totals.shipment_type = minutes_waiting_for_broker_percentiles.shipment_type
622LEFT JOIN
623 minutes_until_shipped_percentiles
624ON
625 totals.package_day = minutes_until_shipped_percentiles.package_day AND
626 totals.shipment_type = minutes_until_shipped_percentiles.shipment_type
627ORDER BY
628 shipment_type,
629 package_day;
630
631--
632
633DROP TABLE IF EXISTS sla_packages_sfl;
634
635CREATE TABLE sla_packages_sfl
636AS
637SELECT
638 package_id AS package_id,
639 shipment_id AS shipment_id,
640 shipment_type AS shipment_type,
641
642 package_day AS package_day,
643 package_week AS package_week,
644 package_month AS package_month,
645 package_year AS package_year,
646 package_season AS package_season,
647
648 batch_id AS batch_id,
649 batch_number AS batch_number,
650 batch_mawb AS batch_mawb,
651 batch_status_name AS batch_status_name,
652 batch_partner_id AS batch_partner_id,
653 batch_broker_name AS batch_broker_name,
654 batch_destination_country_id AS batch_destination_country_id,
655
656 delivery_customer_pickup AS delivery_customer_pickup,
657 delivery_center_id AS delivery_center_id,
658 delivery_partner_id AS delivery_partner_id,
659 delivery_shiptype_code AS delivery_shiptype_code,
660 delivery_external_tracking AS delivery_external_tracking,
661
662 address_city AS address_city,
663
664 delivery_status AS delivery_status,
665 delivery_total_cost AS delivery_total_cost,
666 delivery_cost AS delivery_cost,
667
668 package_weight_oz AS package_weight_oz,
669 package_weight_lbs AS package_weight_lbs,
670 package_weight_kg AS package_weight_kg,
671
672 sfl_ready AS sfl_ready,
673
674 current_process AS current_process,
675
676 CASE
677 WHEN current_process = '0 - Waiting for Arrival'
678 THEN DATEDIFF('minutes', package_awaiting_arrival_time, GETDATE())
679 WHEN current_process = '1 - Waiting for Customer'
680 THEN DATEDIFF('minutes', package_arrival_time, GETDATE())
681 WHEN current_process = '2 - Waiting for Consolidation'
682 THEN DATEDIFF('minutes', created_time, GETDATE())
683 WHEN current_process = '3 - Picking'
684 THEN DATEDIFF('minutes', consolidation_time, GETDATE())
685 WHEN current_process = '4 - Waiting for SFL Processing'
686 THEN DATEDIFF('minutes', package_arrival_time, GETDATE())
687 WHEN current_process = '5 - Waiting for Customer'
688 THEN DATEDIFF('minutes', sfl_processing_started_time, GETDATE())
689 WHEN current_process = '6 - Waiting for Broker'
690 THEN DATEDIFF('minutes', processed_time, GETDATE())
691 WHEN current_process = '7 - Waiting for Customs Fix'
692 THEN DATEDIFF('minutes', sfx_review_approved_time, GETDATE())
693 WHEN current_process = '8 - Waiting to Ship'
694 THEN DATEDIFF('minutes', sfx_review_approved_time, GETDATE())
695 WHEN current_process = '9 - Waiting for Export'
696 THEN DATEDIFF('minutes', shipped_time, GETDATE())
697 WHEN current_process = '10 - In Transit to MOW'
698 THEN DATEDIFF('minutes', exported_us_time, GETDATE())
699 WHEN current_process = '11 - Waiting for Customs'
700 THEN DATEDIFF('minutes', arrived_at_customs_time, GETDATE())
701 WHEN current_process = '12 - Waiting for Sorting'
702 THEN DATEDIFF('minutes', passed_customs_time, GETDATE())
703 WHEN current_process = '13 - In Delivery Network'
704 THEN DATEDIFF('minutes', sorting_for_pickup_point_time, GETDATE())
705 WHEN current_process = '14 - Waiting for Pickup'
706 THEN DATEDIFF('minutes', on_delivery_stock_time, GETDATE())
707 WHEN current_process = '15 - Delivered'
708 THEN 0
709 ELSE NULL
710 END
711 AS minutes_current_process,
712
713 package_awaiting_arrival_time AS package_awaiting_arrival_time,
714
715 CASE
716 WHEN current_process = '0 - Waiting for Arrival'
717 THEN DATEDIFF('minutes', package_awaiting_arrival_time, GETDATE())
718 ELSE DATEDIFF('minutes', package_awaiting_arrival_time, package_arrival_time)
719 END
720 AS minutes_0_waiting_for_arrival,
721
722 package_arrival_time AS package_arrival_time,
723
724 CASE
725 WHEN current_process = '1 - Waiting for Customer'
726 THEN DATEDIFF('minutes', package_arrival_time, GETDATE())
727 ELSE DATEDIFF('minutes', package_arrival_time, created_time)
728 END
729 AS minutes_1_waiting_for_customer,
730
731 created_time AS created_time,
732
733 CASE
734 WHEN current_process = '2 - Waiting for Consolidation'
735 THEN DATEDIFF('minutes', created_time, GETDATE())
736 ELSE DATEDIFF('minutes', created_time, consolidation_time)
737 END
738 AS minutes_2_waiting_for_consolidation,
739
740 consolidation_time AS consolidation_time,
741
742 CASE
743 WHEN current_process = '3 - Picking'
744 THEN DATEDIFF('minutes', consolidation_time, GETDATE())
745 ELSE DATEDIFF('minutes', consolidation_time, sfl_processing_started_time)
746 END
747 AS minutes_3_picking,
748 CASE
749 WHEN current_process = '4 - Waiting for SFL Processing'
750 THEN DATEDIFF('minutes', package_arrival_time, GETDATE())
751 ELSE DATEDIFF('minutes', package_arrival_time, sfl_processing_started_time)
752 END
753 AS minutes_4_waiting_for_sfl_processing,
754
755 sfl_processing_started_time AS sfl_processing_started_time,
756
757 CASE
758 WHEN current_process = '5 - Waiting for Customer'
759 THEN DATEDIFF('minutes', sfl_processing_started_time, GETDATE())
760 ELSE DATEDIFF('minutes', sfl_processing_started_time, processed_time)
761 END
762 AS minutes_5_waiting_for_customer,
763
764 processed_time AS processed_time,
765
766 CASE
767 WHEN current_process = '6 - Waiting for Broker'
768 THEN DATEDIFF('minutes', processed_time, GETDATE())
769 ELSE DATEDIFF('minutes', processed_time, LEAST(sfx_review_approved_time, sfx_review_failed_time))
770 END
771 AS minutes_6_waiting_for_broker,
772
773 sfx_review_failed_time AS sfx_review_failed_time,
774
775 CASE
776 WHEN current_process = '7 - Waiting for Customs Fix'
777 THEN DATEDIFF('minutes', sfx_review_failed_time, GETDATE())
778 WHEN sfx_review_failed_time IS NOT NULL
779 THEN DATEDIFF('minutes', sfx_review_failed_time, sfx_review_approved_time)
780 ELSE NULL
781 END
782 AS minutes_7_waiting_for_customs_fix,
783
784 sfx_review_approved_time AS sfx_review_approved_time,
785
786 CASE
787 WHEN current_process = '8 - Waiting to Ship'
788 THEN DATEDIFF('minutes', sfx_review_approved_time, GETDATE())
789 ELSE DATEDIFF('minutes', sfx_review_approved_time, shipped_time)
790 END
791 AS minutes_8_waiting_to_ship,
792
793 shipped_time AS shipped_time,
794
795 DATEDIFF('minutes', package_arrival_time, shipped_time)
796
797 AS minutes_total_in_warehouse,
798
799 DATEDIFF('minutes', package_arrival_time, shipped_time)
800 - COALESCE(DATEDIFF('minutes', packed_time, processed_time), 0)
801 - COALESCE(DATEDIFF('minutes', package_arrival_time, created_time), 0)
802
803 AS minutes_total_warehouse_processes,
804
805 CASE
806 WHEN current_process = '9 - Waiting for Export'
807 THEN DATEDIFF('minutes', shipped_time, GETDATE())
808 ELSE DATEDIFF('minutes', shipped_time, exported_us_time)
809 END
810 AS minutes_9_waiting_for_export,
811
812 exported_us_time AS exported_us_time,
813
814 CASE
815 WHEN current_process = '10 - In Transit to MOW'
816 THEN DATEDIFF('minutes', exported_us_time, GETDATE())
817 ELSE DATEDIFF('minutes', exported_us_time, arrived_at_customs_time)
818 END
819 AS minutes_10_in_transit_to_mow,
820
821 arrived_at_customs_time AS arrived_at_customs_time,
822
823 CASE
824 WHEN current_process = '11 - Waiting for Customs'
825 THEN DATEDIFF('minutes', arrived_at_customs_time, GETDATE())
826 ELSE DATEDIFF('minutes', arrived_at_customs_time, passed_customs_time)
827 END
828 AS minutes_11_waiting_for_customs,
829
830 passed_customs_time AS passed_customs_time,
831
832 CASE
833 WHEN current_process = '12 - Waiting for Sorting'
834 THEN DATEDIFF('minutes', passed_customs_time, GETDATE())
835 ELSE DATEDIFF('minutes', passed_customs_time, sorting_for_pickup_point_time)
836 END
837 AS minutes_12_waiting_for_sorting,
838
839 sorting_for_pickup_point_time AS sorting_for_pickup_point_time,
840
841 CASE
842 WHEN current_process = '13 - In Delivery Network'
843 THEN DATEDIFF('minutes', sorting_for_pickup_point_time, GETDATE())
844 ELSE DATEDIFF('minutes', sorting_for_pickup_point_time, on_delivery_stock_time)
845 END
846 AS minutes_13_in_delivery_network,
847
848 on_delivery_stock_time AS on_delivery_stock_time,
849
850 CASE
851 WHEN current_process = '14 - Waiting for Pickup'
852 THEN DATEDIFF('minutes', on_delivery_stock_time, GETDATE())
853 ELSE DATEDIFF('minutes', on_delivery_stock_time, delivered_time)
854 END
855 AS minutes_14_waiting_for_pickup,
856
857 delivered_time AS delivered_time,
858
859 -- extra timestamps that I'm not sure what to use for
860
861 package_sfl_ready_time AS package_sfl_ready_time,
862 region_transit_time AS region_transit_time,
863 transit_to_pickup_point_time AS transit_to_pickup_point_time,
864 checked_in_at_spp_time AS checked_in_at_spp_time,
865 missing_customs_data_time AS missing_customs_data_time,
866 held_at_customs_time AS held_at_customs_time,
867 sorting_time AS sorting_time,
868 transit_time AS transit_time,
869 in_stock_time AS in_stock_time,
870 ready_for_delivery_time AS ready_for_delivery_time,
871 transfer_to_car_time AS transfer_to_car_time,
872 in_route_time AS in_route_time,
873 delivery_failed_time AS delivery_failed_time,
874 returned_time AS returned_time
875FROM
876(
877 SELECT
878 package_id,
879 shipment_id,
880 shipment_type,
881
882 package_day,
883 package_week,
884 package_month,
885 package_year,
886 package_season,
887
888 batch_id AS batch_id,
889 batch_number AS batch_number,
890 batch_mawb AS batch_mawb,
891 batch_status_name AS batch_status_name,
892 batch_partner_id AS batch_partner_id,
893 batch_broker_name AS batch_broker_name,
894 batch_destination_country_id AS batch_destination_country_id,
895
896 delivery_customer_pickup AS delivery_customer_pickup,
897 delivery_center_id AS delivery_center_id,
898 delivery_partner_id AS delivery_partner_id,
899 delivery_shiptype_code AS delivery_shiptype_code,
900 delivery_external_tracking AS delivery_external_tracking,
901
902 address_city AS address_city,
903
904 delivery_status AS delivery_status,
905 delivery_total_cost AS delivery_total_cost,
906 delivery_cost AS delivery_cost,
907
908 package_weight_oz,
909 package_weight_lbs,
910 package_weight_kg,
911
912 sfl_ready,
913
914 CASE
915 WHEN
916 delivered_time IS NOT NULL
917 THEN '15 - Delivered'
918 WHEN
919 on_delivery_stock_time IS NOT NULL
920 AND delivered_time IS NULL
921 THEN '14 - Waiting for Pickup'
922 WHEN
923 sorting_for_pickup_point_time IS NOT NULL
924 AND on_delivery_stock_time IS NULL
925 THEN '13 - In Delivery Network'
926 WHEN
927 passed_customs_time IS NOT NULL
928 AND sorting_for_pickup_point_time IS NULL
929 THEN '12 - Waiting for Sorting'
930 WHEN
931 arrived_at_customs_time IS NOT NULL
932 AND passed_customs_time IS NULL
933 THEN '11 - Waiting for Customs'
934 WHEN
935 exported_us_time IS NOT NULL
936 AND arrived_at_customs_time IS NULL
937 THEN '10 - In Transit to MOW'
938 WHEN
939 shipped_time IS NOT NULL
940 AND exported_us_time IS NULL
941 AND package_day >= '2017-10-01 00:00:00'
942 THEN '9 - Waiting for Export'
943 WHEN
944 sfx_review_approved_time IS NOT NULL
945 AND shipped_time IS NULL
946 THEN '8 - Waiting to Ship'
947 WHEN
948 sfx_review_failed_time IS NOT NULL
949 AND sfx_review_approved_time IS NULL
950 AND shipped_time IS NULL
951 THEN '7 - Waiting for Customs Fix'
952 WHEN
953 processed_time IS NOT NULL
954 AND COALESCE(sfx_review_approved_time, sfx_review_failed_time) IS NULL
955 THEN '6 - Waiting for Broker'
956 WHEN
957 sfl_processing_started_time IS NOT NULL
958 AND processed_time IS NULL
959 THEN '5 - Waiting for Customer'
960 WHEN
961 package_arrival_time IS NOT NULL
962 AND package_sfl_ready_time IS NOT NULL
963 AND sfl_processing_started_time IS NULL
964 THEN '4 - Waiting for SFL Processing'
965 WHEN
966 package_sfl_ready_time IS NULL
967 AND consolidation_time IS NOT NULL
968 AND sfl_processing_started_time IS NULL
969 AND packed_time IS NULL
970 AND processed_time IS NULL
971 THEN '3 - Picking'
972 WHEN
973 package_sfl_ready_time IS NULL
974 AND created_time IS NOT NULL
975 AND consolidation_time IS NULL
976 THEN '2 - Waiting for Consolidation'
977 WHEN
978 package_sfl_ready_time IS NULL
979 AND package_arrival_time IS NOT NULL
980 AND created_time IS NULL
981 THEN '1 - Waiting for Customer'
982 WHEN
983 package_awaiting_arrival_time IS NOT NULL
984 AND package_arrival_time IS NULL
985 THEN '0 - Waiting for Arrival'
986 ELSE 'UNKNOWN'
987 END
988 AS current_process,
989
990 package_awaiting_arrival_time AS package_awaiting_arrival_time,
991 package_arrival_time AS package_arrival_time,
992 package_sfl_ready_time AS package_sfl_ready_time,
993
994 created_time AS created_time,
995 consolidation_time AS consolidation_time,
996 sfl_processing_started_time AS sfl_processing_started_time,
997 packed_time AS packed_time,
998 processed_time AS processed_time,
999 sfx_review_approved_time AS sfx_review_approved_time,
1000 sfx_review_failed_time AS sfx_review_failed_time,
1001 shipped_time AS shipped_time,
1002 in_transit_time AS in_transit_time,
1003 region_transit_time AS region_transit_time,
1004 on_delivery_stock_time AS on_delivery_stock_time,
1005
1006 manifest_uploaded_time AS manifest_uploaded_time,
1007 checked_into_warehouse_time AS checked_into_warehouse_time,
1008 ready_to_ship_time AS ready_to_ship_time,
1009 dispatched_time AS dispatched_time,
1010 customs_data_obtained_time AS customs_data_obtained_time,
1011 exported_us_time AS exported_us_time,
1012 arrived_at_customs_time AS arrived_at_customs_time,
1013 missing_customs_data_time AS missing_customs_data_time,
1014 held_at_customs_time AS held_at_customs_time,
1015 passed_customs_time AS passed_customs_time,
1016 sorting_time AS sorting_time,
1017 sorting_for_pickup_point_time AS sorting_for_pickup_point_time,
1018 transit_time AS transit_time,
1019 transit_to_pickup_point_time AS transit_to_pickup_point_time,
1020 in_stock_time AS in_stock_time,
1021 checked_in_at_spp_time AS checked_in_at_spp_time,
1022 ready_for_delivery_time AS ready_for_delivery_time,
1023 transfer_to_car_time AS transfer_to_car_time,
1024 in_route_time AS in_route_time,
1025 delivery_failed_time AS delivery_failed_time,
1026 returned_time AS returned_time,
1027 delivered_time AS delivered_time
1028 FROM
1029 sla_packages
1030 WHERE
1031 shipment_type = 'Shopfans Lite'
1032 ORDER BY
1033 package_day DESC
1034) AS packages
1035ORDER BY
1036 package_day DESC;
1037
1038
1039--
1040
1041DROP TABLE IF EXISTS sla_packages_sfl_by_day;
1042
1043CREATE TABLE sla_packages_sfl_by_day
1044AS
1045WITH totals_by_day_type AS (
1046 SELECT
1047 package_day AS package_day,
1048 shipment_type AS shipment_type,
1049 COUNT(DISTINCT package_id) AS packages,
1050 COUNT(DISTINCT shipment_id) AS shipments
1051 FROM
1052 sla_packages_sfl
1053 GROUP BY
1054 package_day,
1055 shipment_type
1056 ORDER BY
1057 shipment_type,
1058 package_day
1059),
1060minutes_0_waiting_for_arrival_percentiles AS (
1061 SELECT
1062 package_day,
1063 shipment_type,
1064
1065 COUNT(*) AS minutes_0_waiting_for_arrival_n,
1066
1067 PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY minutes_0_waiting_for_arrival)
1068 AS minutes_0_waiting_for_arrival_median,
1069
1070 PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY minutes_0_waiting_for_arrival)
1071 AS minutes_0_waiting_for_arrival_90th_percentile
1072 FROM
1073 sla_packages_sfl
1074 WHERE
1075 minutes_0_waiting_for_arrival IS NOT NULL
1076 GROUP BY
1077 package_day,
1078 shipment_type
1079 ORDER BY
1080 shipment_type,
1081 package_day
1082),
1083minutes_1_waiting_for_customer_percentiles AS (
1084 SELECT
1085 package_day,
1086 shipment_type,
1087
1088 COUNT(*) AS minutes_1_waiting_for_customer_n,
1089
1090 PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY minutes_1_waiting_for_customer)
1091 AS minutes_1_waiting_for_customer_median,
1092
1093 PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY minutes_1_waiting_for_customer)
1094 AS minutes_1_waiting_for_customer_90th_percentile
1095 FROM
1096 sla_packages_sfl
1097 WHERE
1098 minutes_1_waiting_for_customer IS NOT NULL
1099 GROUP BY
1100 package_day,
1101 shipment_type
1102 ORDER BY
1103 shipment_type,
1104 package_day
1105),
1106minutes_2_waiting_for_consolidation_percentiles AS (
1107 SELECT
1108 package_day,
1109 shipment_type,
1110
1111 COUNT(*) AS minutes_2_waiting_for_consolidation_n,
1112
1113 PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY minutes_2_waiting_for_consolidation)
1114 AS minutes_2_waiting_for_consolidation_median,
1115
1116 PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY minutes_2_waiting_for_consolidation)
1117 AS minutes_2_waiting_for_consolidation_90th_percentile
1118 FROM
1119 sla_packages_sfl
1120 WHERE
1121 minutes_2_waiting_for_consolidation IS NOT NULL
1122 GROUP BY
1123 package_day,
1124 shipment_type
1125 ORDER BY
1126 shipment_type,
1127 package_day
1128),
1129minutes_3_picking_percentiles AS (
1130 SELECT
1131 package_day,
1132 shipment_type,
1133
1134 COUNT(*) AS minutes_3_picking_n,
1135
1136 PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY minutes_3_picking)
1137 AS minutes_3_picking_median,
1138
1139 PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY minutes_3_picking)
1140 AS minutes_3_picking_90th_percentile
1141 FROM
1142 sla_packages_sfl
1143 WHERE
1144 minutes_3_picking IS NOT NULL
1145 GROUP BY
1146 package_day,
1147 shipment_type
1148 ORDER BY
1149 shipment_type,
1150 package_day
1151),
1152minutes_4_waiting_for_sfl_processing_percentiles AS (
1153 SELECT
1154 package_day,
1155 shipment_type,
1156
1157 COUNT(*) AS minutes_4_waiting_for_sfl_processing_n,
1158
1159 PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY minutes_4_waiting_for_sfl_processing)
1160 AS minutes_4_waiting_for_sfl_processing_median,
1161
1162 PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY minutes_4_waiting_for_sfl_processing)
1163 AS minutes_4_waiting_for_sfl_processing_90th_percentile
1164 FROM
1165 sla_packages_sfl
1166 WHERE
1167 minutes_4_waiting_for_sfl_processing IS NOT NULL
1168 GROUP BY
1169 package_day,
1170 shipment_type
1171 ORDER BY
1172 shipment_type,
1173 package_day
1174),
1175minutes_5_waiting_for_customer_percentiles AS (
1176 SELECT
1177 package_day,
1178 shipment_type,
1179
1180 COUNT(*) AS minutes_5_waiting_for_customer_n,
1181
1182 PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY minutes_5_waiting_for_customer)
1183 AS minutes_5_waiting_for_customer_median,
1184
1185 PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY minutes_5_waiting_for_customer)
1186 AS minutes_5_waiting_for_customer_90th_percentile
1187 FROM
1188 sla_packages_sfl
1189 WHERE
1190 minutes_5_waiting_for_customer IS NOT NULL
1191 GROUP BY
1192 package_day,
1193 shipment_type
1194 ORDER BY
1195 shipment_type,
1196 package_day
1197),
1198minutes_6_waiting_for_broker_percentiles AS (
1199 SELECT
1200 package_day,
1201 shipment_type,
1202
1203 COUNT(*) AS minutes_6_waiting_for_broker_n,
1204
1205 PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY minutes_6_waiting_for_broker)
1206 AS minutes_6_waiting_for_broker_median,
1207
1208 PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY minutes_6_waiting_for_broker)
1209 AS minutes_6_waiting_for_broker_90th_percentile
1210 FROM
1211 sla_packages_sfl
1212 WHERE
1213 minutes_6_waiting_for_broker IS NOT NULL
1214 GROUP BY
1215 package_day,
1216 shipment_type
1217 ORDER BY
1218 shipment_type,
1219 package_day
1220),
1221minutes_7_waiting_for_customs_fix_percentiles AS (
1222 SELECT
1223 package_day,
1224 shipment_type,
1225
1226 COUNT(*) AS minutes_7_waiting_for_customs_fix_n,
1227
1228 PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY minutes_7_waiting_for_customs_fix)
1229 AS minutes_7_waiting_for_customs_fix_median,
1230
1231 PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY minutes_7_waiting_for_customs_fix)
1232 AS minutes_7_waiting_for_customs_fix_90th_percentile
1233 FROM
1234 sla_packages_sfl
1235 WHERE
1236 minutes_7_waiting_for_customs_fix IS NOT NULL
1237 GROUP BY
1238 package_day,
1239 shipment_type
1240 ORDER BY
1241 shipment_type,
1242 package_day
1243),
1244minutes_8_waiting_to_ship_percentiles AS (
1245 SELECT
1246 package_day,
1247 shipment_type,
1248
1249 COUNT(*) AS minutes_8_waiting_to_ship_n,
1250
1251 PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY minutes_8_waiting_to_ship)
1252 AS minutes_8_waiting_to_ship_median,
1253
1254 PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY minutes_8_waiting_to_ship)
1255 AS minutes_8_waiting_to_ship_90th_percentile
1256 FROM
1257 sla_packages_sfl
1258 WHERE
1259 minutes_8_waiting_to_ship IS NOT NULL
1260 GROUP BY
1261 package_day,
1262 shipment_type
1263 ORDER BY
1264 shipment_type,
1265 package_day
1266),
1267minutes_9_waiting_for_export_percentiles AS (
1268 SELECT
1269 package_day,
1270 shipment_type,
1271
1272 COUNT(*) AS minutes_9_waiting_for_export_n,
1273
1274 PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY minutes_9_waiting_for_export)
1275 AS minutes_9_waiting_for_export_median,
1276
1277 PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY minutes_9_waiting_for_export)
1278 AS minutes_9_waiting_for_export_90th_percentile
1279 FROM
1280 sla_packages_sfl
1281 WHERE
1282 minutes_9_waiting_for_export IS NOT NULL
1283 GROUP BY
1284 package_day,
1285 shipment_type
1286 ORDER BY
1287 shipment_type,
1288 package_day
1289),
1290minutes_10_in_transit_to_mow_percentiles AS (
1291 SELECT
1292 package_day,
1293 shipment_type,
1294
1295 COUNT(*) AS minutes_10_in_transit_to_mow_n,
1296
1297 PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY minutes_10_in_transit_to_mow)
1298 AS minutes_10_in_transit_to_mow_median,
1299
1300 PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY minutes_10_in_transit_to_mow)
1301 AS minutes_10_in_transit_to_mow_90th_percentile
1302 FROM
1303 sla_packages_sfl
1304 WHERE
1305 minutes_10_in_transit_to_mow IS NOT NULL
1306 GROUP BY
1307 package_day,
1308 shipment_type
1309 ORDER BY
1310 shipment_type,
1311 package_day
1312),
1313minutes_11_waiting_for_customs_percentiles AS (
1314 SELECT
1315 package_day,
1316 shipment_type,
1317
1318 COUNT(*) AS minutes_11_waiting_for_customs_n,
1319
1320 PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY minutes_11_waiting_for_customs)
1321 AS minutes_11_waiting_for_customs_median,
1322
1323 PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY minutes_11_waiting_for_customs)
1324 AS minutes_11_waiting_for_customs_90th_percentile
1325 FROM
1326 sla_packages_sfl
1327 WHERE
1328 minutes_11_waiting_for_customs IS NOT NULL
1329 GROUP BY
1330 package_day,
1331 shipment_type
1332 ORDER BY
1333 shipment_type,
1334 package_day
1335),
1336minutes_12_waiting_for_sorting_percentiles AS (
1337 SELECT
1338 package_day,
1339 shipment_type,
1340
1341 COUNT(*) AS minutes_12_waiting_for_sorting_n,
1342
1343 PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY minutes_12_waiting_for_sorting)
1344 AS minutes_12_waiting_for_sorting_median,
1345
1346 PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY minutes_12_waiting_for_sorting)
1347 AS minutes_12_waiting_for_sorting_90th_percentile
1348 FROM
1349 sla_packages_sfl
1350 WHERE
1351 minutes_12_waiting_for_sorting IS NOT NULL
1352 GROUP BY
1353 package_day,
1354 shipment_type
1355 ORDER BY
1356 shipment_type,
1357 package_day
1358),
1359minutes_13_in_delivery_network_percentiles AS (
1360 SELECT
1361 package_day,
1362 shipment_type,
1363
1364 COUNT(*) AS minutes_13_in_delivery_network_n,
1365
1366 PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY minutes_13_in_delivery_network)
1367 AS minutes_13_in_delivery_network_median,
1368
1369 PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY minutes_13_in_delivery_network)
1370 AS minutes_13_in_delivery_network_90th_percentile
1371 FROM
1372 sla_packages_sfl
1373 WHERE
1374 minutes_13_in_delivery_network IS NOT NULL
1375 GROUP BY
1376 package_day,
1377 shipment_type
1378 ORDER BY
1379 shipment_type,
1380 package_day
1381),
1382minutes_14_waiting_for_pickup_percentiles AS (
1383 SELECT
1384 package_day,
1385 shipment_type,
1386
1387 COUNT(*) AS minutes_14_waiting_for_pickup_n,
1388
1389 PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY minutes_14_waiting_for_pickup)
1390 AS minutes_14_waiting_for_pickup_median,
1391
1392 PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY minutes_14_waiting_for_pickup)
1393 AS minutes_14_waiting_for_pickup_90th_percentile
1394 FROM
1395 sla_packages_sfl
1396 WHERE
1397 minutes_14_waiting_for_pickup IS NOT NULL
1398 GROUP BY
1399 package_day,
1400 shipment_type
1401 ORDER BY
1402 shipment_type,
1403 package_day
1404)
1405SELECT
1406 totals.package_day AS package_day,
1407 totals.shipment_type AS shipment_type,
1408 totals.packages AS packages,
1409 totals.shipments AS shipments,
1410
1411 minutes_0_waiting_for_arrival_percentiles.minutes_0_waiting_for_arrival_n,
1412 minutes_0_waiting_for_arrival_percentiles.minutes_0_waiting_for_arrival_median,
1413 minutes_0_waiting_for_arrival_percentiles.minutes_0_waiting_for_arrival_90th_percentile,
1414
1415 minutes_1_waiting_for_customer_percentiles.minutes_1_waiting_for_customer_n,
1416 minutes_1_waiting_for_customer_percentiles.minutes_1_waiting_for_customer_median,
1417 minutes_1_waiting_for_customer_percentiles.minutes_1_waiting_for_customer_90th_percentile,
1418
1419 minutes_2_waiting_for_consolidation_percentiles.minutes_2_waiting_for_consolidation_n,
1420 minutes_2_waiting_for_consolidation_percentiles.minutes_2_waiting_for_consolidation_median,
1421 minutes_2_waiting_for_consolidation_percentiles.minutes_2_waiting_for_consolidation_90th_percentile,
1422
1423 minutes_3_picking_percentiles.minutes_3_picking_n,
1424 minutes_3_picking_percentiles.minutes_3_picking_median,
1425 minutes_3_picking_percentiles.minutes_3_picking_90th_percentile,
1426
1427 minutes_4_waiting_for_sfl_processing_percentiles.minutes_4_waiting_for_sfl_processing_n,
1428 minutes_4_waiting_for_sfl_processing_percentiles.minutes_4_waiting_for_sfl_processing_median,
1429 minutes_4_waiting_for_sfl_processing_percentiles.minutes_4_waiting_for_sfl_processing_90th_percentile,
1430
1431 minutes_5_waiting_for_customer_percentiles.minutes_5_waiting_for_customer_n,
1432 minutes_5_waiting_for_customer_percentiles.minutes_5_waiting_for_customer_median,
1433 minutes_5_waiting_for_customer_percentiles.minutes_5_waiting_for_customer_90th_percentile,
1434
1435 minutes_6_waiting_for_broker_percentiles.minutes_6_waiting_for_broker_n,
1436 minutes_6_waiting_for_broker_percentiles.minutes_6_waiting_for_broker_median,
1437 minutes_6_waiting_for_broker_percentiles.minutes_6_waiting_for_broker_90th_percentile,
1438
1439 minutes_7_waiting_for_customs_fix_percentiles.minutes_7_waiting_for_customs_fix_n,
1440 minutes_7_waiting_for_customs_fix_percentiles.minutes_7_waiting_for_customs_fix_median,
1441 minutes_7_waiting_for_customs_fix_percentiles.minutes_7_waiting_for_customs_fix_90th_percentile,
1442
1443 minutes_8_waiting_to_ship_percentiles.minutes_8_waiting_to_ship_n,
1444 minutes_8_waiting_to_ship_percentiles.minutes_8_waiting_to_ship_median,
1445 minutes_8_waiting_to_ship_percentiles.minutes_8_waiting_to_ship_90th_percentile,
1446
1447 minutes_9_waiting_for_export_percentiles.minutes_9_waiting_for_export_n,
1448 minutes_9_waiting_for_export_percentiles.minutes_9_waiting_for_export_median,
1449 minutes_9_waiting_for_export_percentiles.minutes_9_waiting_for_export_90th_percentile,
1450
1451 minutes_10_in_transit_to_mow_percentiles.minutes_10_in_transit_to_mow_n,
1452 minutes_10_in_transit_to_mow_percentiles.minutes_10_in_transit_to_mow_median,
1453 minutes_10_in_transit_to_mow_percentiles.minutes_10_in_transit_to_mow_90th_percentile,
1454
1455 minutes_11_waiting_for_customs_percentiles.minutes_11_waiting_for_customs_n,
1456 minutes_11_waiting_for_customs_percentiles.minutes_11_waiting_for_customs_median,
1457 minutes_11_waiting_for_customs_percentiles.minutes_11_waiting_for_customs_90th_percentile,
1458
1459 minutes_12_waiting_for_sorting_percentiles.minutes_12_waiting_for_sorting_n,
1460 minutes_12_waiting_for_sorting_percentiles.minutes_12_waiting_for_sorting_median,
1461 minutes_12_waiting_for_sorting_percentiles.minutes_12_waiting_for_sorting_90th_percentile,
1462
1463 minutes_13_in_delivery_network_percentiles.minutes_13_in_delivery_network_n,
1464 minutes_13_in_delivery_network_percentiles.minutes_13_in_delivery_network_median,
1465 minutes_13_in_delivery_network_percentiles.minutes_13_in_delivery_network_90th_percentile,
1466
1467 minutes_14_waiting_for_pickup_percentiles.minutes_14_waiting_for_pickup_n,
1468 minutes_14_waiting_for_pickup_percentiles.minutes_14_waiting_for_pickup_median,
1469 minutes_14_waiting_for_pickup_percentiles.minutes_14_waiting_for_pickup_90th_percentile
1470FROM
1471 totals_by_day_type AS totals
1472LEFT JOIN
1473 minutes_0_waiting_for_arrival_percentiles
1474ON
1475 totals.package_day = minutes_0_waiting_for_arrival_percentiles.package_day AND
1476 totals.shipment_type = minutes_0_waiting_for_arrival_percentiles.shipment_type
1477LEFT JOIN
1478 minutes_1_waiting_for_customer_percentiles
1479ON
1480 totals.package_day = minutes_1_waiting_for_customer_percentiles.package_day AND
1481 totals.shipment_type = minutes_1_waiting_for_customer_percentiles.shipment_type
1482LEFT JOIN
1483 minutes_2_waiting_for_consolidation_percentiles
1484ON
1485 totals.package_day = minutes_2_waiting_for_consolidation_percentiles.package_day AND
1486 totals.shipment_type = minutes_2_waiting_for_consolidation_percentiles.shipment_type
1487LEFT JOIN
1488 minutes_3_picking_percentiles
1489ON
1490 totals.package_day = minutes_3_picking_percentiles.package_day AND
1491 totals.shipment_type = minutes_3_picking_percentiles.shipment_type
1492LEFT JOIN
1493 minutes_4_waiting_for_sfl_processing_percentiles
1494ON
1495 totals.package_day = minutes_4_waiting_for_sfl_processing_percentiles.package_day AND
1496 totals.shipment_type = minutes_4_waiting_for_sfl_processing_percentiles.shipment_type
1497LEFT JOIN
1498 minutes_5_waiting_for_customer_percentiles
1499ON
1500 totals.package_day = minutes_5_waiting_for_customer_percentiles.package_day AND
1501 totals.shipment_type = minutes_5_waiting_for_customer_percentiles.shipment_type
1502LEFT JOIN
1503 minutes_6_waiting_for_broker_percentiles
1504ON
1505 totals.package_day = minutes_6_waiting_for_broker_percentiles.package_day AND
1506 totals.shipment_type = minutes_6_waiting_for_broker_percentiles.shipment_type
1507LEFT JOIN
1508 minutes_7_waiting_for_customs_fix_percentiles
1509ON
1510 totals.package_day = minutes_7_waiting_for_customs_fix_percentiles.package_day AND
1511 totals.shipment_type = minutes_7_waiting_for_customs_fix_percentiles.shipment_type
1512LEFT JOIN
1513 minutes_8_waiting_to_ship_percentiles
1514ON
1515 totals.package_day = minutes_8_waiting_to_ship_percentiles.package_day AND
1516 totals.shipment_type = minutes_8_waiting_to_ship_percentiles.shipment_type
1517LEFT JOIN
1518 minutes_9_waiting_for_export_percentiles
1519ON
1520 totals.package_day = minutes_9_waiting_for_export_percentiles.package_day AND
1521 totals.shipment_type = minutes_9_waiting_for_export_percentiles.shipment_type
1522LEFT JOIN
1523 minutes_10_in_transit_to_mow_percentiles
1524ON
1525 totals.package_day = minutes_10_in_transit_to_mow_percentiles.package_day AND
1526 totals.shipment_type = minutes_10_in_transit_to_mow_percentiles.shipment_type
1527LEFT JOIN
1528 minutes_11_waiting_for_customs_percentiles
1529ON
1530 totals.package_day = minutes_11_waiting_for_customs_percentiles.package_day AND
1531 totals.shipment_type = minutes_11_waiting_for_customs_percentiles.shipment_type
1532LEFT JOIN
1533 minutes_12_waiting_for_sorting_percentiles
1534ON
1535 totals.package_day = minutes_12_waiting_for_sorting_percentiles.package_day AND
1536 totals.shipment_type = minutes_12_waiting_for_sorting_percentiles.shipment_type
1537LEFT JOIN
1538 minutes_13_in_delivery_network_percentiles
1539ON
1540 totals.package_day = minutes_13_in_delivery_network_percentiles.package_day AND
1541 totals.shipment_type = minutes_13_in_delivery_network_percentiles.shipment_type
1542LEFT JOIN
1543 minutes_14_waiting_for_pickup_percentiles
1544ON
1545 totals.package_day = minutes_14_waiting_for_pickup_percentiles.package_day AND
1546 totals.shipment_type = minutes_14_waiting_for_pickup_percentiles.shipment_type
1547ORDER BY
1548 shipment_type,
1549 package_day;
1550
1551--
1552
1553
1554DROP TABLE IF EXISTS sla_queues_sfl_by_hour;
1555
1556CREATE TABLE sla_queues_sfl_by_hour
1557AS
1558SELECT
1559 hours.hour AS hour,
1560
1561 SUM(
1562 CASE
1563 WHEN hours.hour >= packages.package_awaiting_arrival_time AND
1564 hours.hour < packages.package_arrival_time
1565 THEN 1
1566 WHEN current_process = '0 - Waiting for Arrival'
1567 THEN 1
1568 ELSE 0
1569 END
1570 ) AS queue_0_waiting_for_arrival,
1571
1572 SUM(
1573 CASE
1574 WHEN hours.hour >= packages.package_arrival_time AND
1575 hours.hour < packages.created_time
1576 THEN 1
1577 WHEN current_process = '1 - Waiting for Customer'
1578 THEN 1
1579 ELSE 0
1580 END
1581 ) AS queue_1_waiting_on_customer,
1582
1583 SUM(
1584 CASE
1585 WHEN hours.hour >= packages.created_time AND
1586 hours.hour < packages.consolidation_time
1587 THEN 1
1588 WHEN current_process = '2 - Waiting for Consolidation'
1589 THEN 1
1590 ELSE 0
1591 END
1592 ) AS queue_2_waiting_for_consolidation,
1593
1594 SUM(
1595 CASE
1596 WHEN hours.hour >= packages.consolidation_time AND
1597 hours.hour < packages.sfl_processing_started_time
1598 THEN 1
1599 WHEN current_process = '3 - Picking'
1600 THEN 1
1601 ELSE 0
1602 END
1603 ) AS queue_3_picking,
1604
1605 SUM(
1606 CASE
1607 WHEN hours.hour >= packages.package_arrival_time AND
1608 hours.hour < packages.sfl_processing_started_time
1609 THEN 1
1610 WHEN current_process = '4 - Waiting for SFL Processing'
1611 THEN 1
1612 ELSE 0
1613 END
1614 ) AS queue_4_waiting_for_sfl_processing,
1615
1616 SUM(
1617 CASE
1618 WHEN hours.hour >= packages.sfl_processing_started_time AND
1619 hours.hour < packages.processed_time
1620 THEN 1
1621 WHEN current_process = '5 - Waiting for Customer'
1622 THEN 1
1623 ELSE 0
1624 END
1625 ) AS queue_5_waiting_for_customer,
1626
1627 SUM(
1628 CASE
1629 WHEN hours.hour >= packages.processed_time AND
1630 hours.hour < COALESCE(packages.sfx_review_approved_time, packages.sfx_review_failed_time)
1631 THEN 1
1632 WHEN current_process = '6 - Waiting for Broker'
1633 THEN 1
1634 ELSE 0
1635 END
1636 ) AS queue_6_waiting_for_broker,
1637
1638
1639 SUM(
1640 CASE
1641 WHEN hours.hour >= packages.sfx_review_failed_time AND
1642 hours.hour < packages.sfx_review_approved_time
1643 THEN 1
1644 WHEN current_process = '7 - Waiting for Customs Fix'
1645 THEN 1
1646 ELSE 0
1647 END
1648 ) AS queue_7_waiting_for_customs_fix,
1649
1650 SUM(
1651 CASE
1652 WHEN hours.hour >= packages.sfx_review_approved_time
1653 AND hours.hour < packages.shipped_time
1654 THEN 1
1655 WHEN current_process = '8 - Waiting to Ship'
1656 THEN 1
1657 ELSE 0
1658 END
1659 ) AS queue_8_waiting_to_ship,
1660
1661 SUM(
1662 CASE
1663 WHEN hours.hour >= packages.package_arrival_time
1664 AND hours.hour < packages.shipped_time
1665 THEN 1
1666 WHEN hours.hour >= packages.package_arrival_time
1667 AND packages.shipped_time IS NULL
1668 THEN 1
1669 ELSE 0
1670 END
1671 ) AS queue_total_in_warehouse,
1672
1673 SUM(
1674 CASE
1675 WHEN hours.hour >= packages.shipped_time
1676 AND hours.hour < packages.exported_us_time
1677 THEN 1
1678 WHEN current_process = '9 - Waiting for Export'
1679 THEN 1
1680 ELSE 0
1681 END
1682 ) AS queue_9_waiting_for_export,
1683
1684 SUM(
1685 CASE
1686 WHEN hours.hour >= packages.exported_us_time
1687 AND hours.hour < packages.arrived_at_customs_time
1688 THEN 1
1689 WHEN current_process = '10 - In Transit to MOW'
1690 THEN 1
1691 ELSE 0
1692 END
1693 ) AS queue_10_in_transit_to_mow,
1694
1695 SUM(
1696 CASE
1697 WHEN hours.hour >= packages.arrived_at_customs_time
1698 AND hours.hour < packages.passed_customs_time
1699 THEN 1
1700 WHEN current_process = '11 - Waiting for Customs'
1701 THEN 1
1702 ELSE 0
1703 END
1704 ) AS queue_11_waiting_for_customs,
1705
1706 SUM(
1707 CASE
1708 WHEN hours.hour >= packages.passed_customs_time
1709 AND hours.hour < packages.sorting_for_pickup_point_time
1710 THEN 1
1711 WHEN current_process = '12 - Waiting for Sorting'
1712 THEN 1
1713 ELSE 0
1714 END
1715 ) AS queue_12_waiting_for_sorting,
1716
1717 SUM(
1718 CASE
1719 WHEN hours.hour >= packages.sorting_for_pickup_point_time
1720 AND hours.hour < packages.on_delivery_stock_time
1721 THEN 1
1722 WHEN current_process = '13 - In Delivery Network'
1723 THEN 1
1724 ELSE 0
1725 END
1726 ) AS queue_13_in_delivery_network,
1727
1728 SUM(
1729 CASE
1730 WHEN hours.hour >= packages.on_delivery_stock_time
1731 AND hours.hour < packages.delivered_time
1732 THEN 1
1733 WHEN current_process = '14 - Waiting for Pickup'
1734 THEN 1
1735 ELSE 0
1736 END
1737 ) AS queue_14_waiting_for_pickup
1738FROM
1739(
1740 SELECT
1741 *
1742 FROM
1743 sla_packages_sfl
1744 ORDER BY
1745 package_day DESC
1746) AS packages
1747CROSS JOIN
1748(
1749 SELECT
1750 DATEADD(hour, n, '2017-01-01 00:00:00') AS hour
1751 FROM
1752 bi_numbers
1753 WHERE
1754 n <= DATEDIFF(hour, '2017-01-01 00:00:00', GETDATE())
1755 ORDER BY
1756 n ASC
1757) AS hours
1758GROUP BY
1759 hours.hour
1760ORDER BY
1761 hours.hour DESC;
1762© 2018 GitHub, Inc.
1763Terms
1764Privacy
1765Security
1766Status
1767Help
1768Contact GitHub
1769Pricing
1770API
1771Training
1772Blog
1773About
1774Press h to open a hovercard with more details.