Time Fix Queries

Leave Plant vs. Ticket Date/Time

Review Leave Plant<Ticket Date/Time

select plant_no,ticket_date,ticket_time,leave_plant_dt, 
cast (ticket_date||' '||cast(ticket_time/100 as char(2))||':'||cast(ticket_time-100*(ticket_time/100)
   as char(2))   as timestamp)+.006945  fix_LeavePlant_dt
from ARTSLSHD
WHERE leave_plant_dt<cast (ticket_date||' '||cast(ticket_time/100 as char(2))||':'||cast(ticket_time-100*(ticket_time/100)
   as char(2))   as timestamp)

Fix Leave Plant:

update artslshd
set leave_plant_dt=cast (ticket_date||' '||cast(ticket_time/100 as char(2))||':'||cast(ticket_time-100*(ticket_time/100)
   as char(2))   as timestamp)+.006945
WHERE leave_plant_dt<cast (ticket_date||' '||cast(ticket_time/100 as char(2))||':'||cast(ticket_time-100*(ticket_time/100)
   as char(2))   as timestamp)
and ticket_date='2/1/7'

Arrive Job vs. Leave Plant

Arrive Job Analysis:

select plant_no,leave_plant_dt, arrive_job_dt,
leave_plant_dt+.020834  fix_Arrive_job_dt
from ARTSLSHD
WHERE arrive_job_dt<leave_plant_dt

Fix Arrive Job:

update ARTSLSHD
set  arrive_job_dt=leave_plant_dt+.020834
WHERE arrive_job_dt<leave_plant_dt
and ticket_date='2/1/7'

Return Plant vs. Leave Plant (a.m. p.m. fix)

Return Time Analysis

select leave_plant_dt,arrive_job_dt,begin_pour_dt,end_pour_dt,leave_job_dt,return_plant_dt,return_plant_dt+.5 est_fix__ret_plt
,return_plant_dt-leave_plant_dt Days_off,cast((return_plant_dt-leave_plant_dt)*1440 as integer) Mins_off
from ARTSLSHD
WHERE RETURN_PLANT_DT<LEAVE_PLANT_dt

Return Time Fix

update ARTSLSHD
set return_plant_dt=return_plant_dt+.5 where
 RETURN_PLANT_DT-LEAVE_PLANT_dt  between -1.0 and -.5

Elapsed Time

select plant_no,ticket_date,ticket_time,return_plant_dt,  
cast (ticket_date||' '||cast(ticket_time/100 as char(2))||':'||cast(ticket_time-100*(ticket_time/100)
   as char(2))   as timestamp) Ticket_dt,
cast (.005+1440*(return_plant_dt-cast (ticket_date||' '||cast(ticket_time/100 as char(2))||':'||cast(ticket_time-100*(ticket_time/100)
   as char(2))   as timestamp)) as integer) Fix_elapsed,elapsed_time
from ARTSLSHD
WHERE ticket_date>'2/1'
and return_plant_dt>cast (ticket_date||' '||cast(ticket_time/100 as char(2))||':'||cast(ticket_time-100*(ticket_time/100)
   as char(2))   as timestamp)
rows 1 to 1000

update artslshd set
elapsed_time=cast (.005+1440*(return_plant_dt-cast (ticket_date||' '||cast(ticket_time/100 as char(2))||':'||cast(ticket_time-100*(ticket_time/100)
   as char(2))   as timestamp)) as integer) 
from ARTSLSHD
WHERE ticket_date>'2/1'
and return_plant_dt>cast (ticket_date||' '||cast(ticket_time/100 as char(2))||':'||cast(ticket_time-100*(ticket_time/100)
  as char(2))   as timestamp)

NOTE: This query overrides existing elapsed times. Consider a more stringent where clause.