I cannot find a way to make the final results appear in a TIMESTAMP format. I would appreciate any help y’all may have.
Select Distinct
b.WH_ID
, b.ORDER_NUMBER
, Avg(b.PICKING_TIME) As Picking_Start_Avg
, Avg(b.STAGING_TIME) As Picking_Completion_Avg
, Avg(b.SHIPPING_TIME) As Birth_2_Death_Avg
From(Select distinct
a.wh_id
, a.WH_TIMEZONE
, a.ORDER_NUMBER
, Sum(a.LINES_PICKED) As Total_Lines_Picked
, Sum(a.LINES_SHIPPED) As Total_Lines_Shipped
, Sum(a.FIRST_PICK-a.EST_PRINT_TIME) As Picking_Time
, Sum(a.STAGED-a.EST_PRINT_TIME) As Staging_Time
, Sum(a.SHIPPED-a.EST_PRINT_TIME) As Shipping_Time
From (SELECT distinct
orm.wh_id, WHS.WH_TIMEZONE, orm.ORDER_NUMBER, orm.CARRIER,
count(distinct tl.line_number) over (partition by tl.control_number) as lines_picked,
count(distinct tl2.line_number) over (partition by tl2.control_number) as lines_shipped,
max(orm.STAGED_DATE) over (partition by orm.wh_id, orm.order_number) as EST_PRINT_TIME,
case when whs.wh_timezone=‘US/Pacific’ then ‘3’ when whs.wh_timezone=‘US/Mountain’ then ‘2’ when whs.wh_timezone=‘US/Central’ then ‘1’ else ‘0’ end as subtr_hours,
orm.earliest_delivery_date,
min(tl.start_tran_time) over (partition by orm.wh_id, orm.order_number) as first_pick,
max(tl.end_tran_time) over (partition by orm.wh_id, orm.order_number) as staged,
max(tl2.end_tran_time) over (partition by orm.wh_id, orm.order_number) as shipped
FROM SI_ORDER ORM
inner join t_whse whs on whs.wh_id=orm.wh_id left
outer join t_tran_log tl on tl.wh_id=orm.wh_id and