WikiAdmin: 1 revision imported

2023-07-14T13:00:38Z

1 revision imported

← Older revision	Revision as of 13:00, 14 July 2023
(No difference)

Intra>User: 1 revision imported

2017-01-10T00:02:24Z

1 revision imported

New page

This query demonstrates different calculations combining HHMM times and Date fields with TimeStamps:

<PRE>
select ticket_date,ticket_time,
ticket_time/100 "Hour",
ticket_time-100*(ticket_time/100) "Minute",
60*(ticket_time/100)+ticket_time-100*(ticket_time/100) "Minutes",
cast ( (60*(ticket_time/100)+ticket_time-100*(ticket_time/100)) as numeric (11,6) ) /1440 "Frac day",
cast(ticket_Date as timestamp )+cast ( .01 + (60*(ticket_time/100)+ticket_time-100*(ticket_time/100)) as numeric (11,6) ) /1440 "time day",
return_plant_dt "Return DateTime",
floor(1440*(Return_Plant_DT-cast(ticket_Date as timestamp )-cast ( (60*(ticket_time/100)+ticket_time-100*(ticket_time/100)) as numeric (11,6) ) /1440) ) "Elapsed Mins",
floor(floor(1440*(Return_Plant_DT-cast(ticket_Date as timestamp )-cast ( (60*(ticket_time/100)+ticket_time-100*(ticket_time/100)) as numeric (11,6) ) /1440) )/60) "Elapsed HH",
floor(.5 +60* (floor(1440*(Return_Plant_DT-cast(ticket_Date as timestamp )-cast ( (60*(ticket_time/100)+ticket_time-100*(ticket_time/100)) as numeric (11,6) ) /1440) )/60 -
floor(floor(1440*(Return_Plant_DT-cast(ticket_Date as timestamp )-cast ( (60*(ticket_time/100)+ticket_time-100*(ticket_time/100)) as numeric (11,6) ) /1440) )/60) ) ) "Elapsed MM",
100*floor(floor(1440*(Return_Plant_DT-cast(ticket_Date as timestamp )-cast ( (60*(ticket_time/100)+ticket_time-100*(ticket_time/100)) as numeric (11,6) ) /1440) )/60) +
floor(.5 +60* (floor(1440*(Return_Plant_DT-cast(ticket_Date as timestamp )-cast ( (60*(ticket_time/100)+ticket_time-100*(ticket_time/100)) as numeric (11,6) ) /1440) )/60 -
floor(floor(1440*(Return_Plant_DT-cast(ticket_Date as timestamp )-cast ( (60*(ticket_time/100)+ticket_time-100*(ticket_time/100)) as numeric (11,6) ) /1440) )/60) ) ) "Elapsed HHMM"
from artslshd
where ticket_date = :Test_Date and return_plant_dt is not null and ticket_time is not null
</PRE>

=== Sample Elapsed Time Review ===

<PRE>
select Plant_no,cust_no,ticket_no,ticket_date,ticket_time,
return_plant_dt "Return DateTime",
floor(1440*(Return_Plant_DT-cast(ticket_Date as timestamp )-cast ( (60*(ticket_time/100)+ticket_time-100*(ticket_time/100)) as numeric (11,6) ) /1440) ) "Calc Elapsed Mins",
100*floor(floor(1440*(Return_Plant_DT-cast(ticket_Date as timestamp )-cast ( (60*(ticket_time/100)+ticket_time-100*(ticket_time/100)) as numeric (11,6) ) /1440) )/60) +
floor(.5 +60* (floor(1440*(Return_Plant_DT-cast(ticket_Date as timestamp )-cast ( (60*(ticket_time/100)+ticket_time-100*(ticket_time/100)) as numeric (11,6) ) /1440) )/60 -
floor(floor(1440*(Return_Plant_DT-cast(ticket_Date as timestamp )-cast ( (60*(ticket_time/100)+ticket_time-100*(ticket_time/100)) as numeric (11,6) ) /1440) )/60) ) ) "Calc Elapsed HHMM",
Elapsed_time "Sys Elapsed Time"
from artslshd
where ticket_date = '3/31/08' and return_plant_dt is not null
</PRE>

<PRE>
UPDATE artslshd
SET ELAPSED_TIME=
100*floor(floor(1440*(Return_Plant_DT-cast(ticket_Date as timestamp )-cast ( (60*(ticket_time/100)+ticket_time-100*(ticket_time/100)) as numeric (11,6) ) /1440) )/60) +
floor(.5 +60* (floor(1440*(Return_Plant_DT-cast(ticket_Date as timestamp )-cast ( (60*(ticket_time/100)+ticket_time-100*(ticket_time/100)) as numeric (11,6) ) /1440) )/60 -
floor(floor(1440*(Return_Plant_DT-cast(ticket_Date as timestamp )-cast ( (60*(ticket_time/100)+ticket_time-100*(ticket_time/100)) as numeric (11,6) ) /1440) )/60) ) )
where ticket_date BETWEEN :Beg_Date and :End_Date and return_plant_dt is not null and ticket_time is not null
and plant_no in ('P1', 'P2', 'P3');
</PRE>

==Older Example==

Example Time Query
<PRE>
select artdriv.driver_name,plant_no,truck_no, ticket_date,ticket_no,artslsdt.qty_sold, ticket_time,

floor(1440*(batch_end_DT-cast(ticket_Date as timestamp )-cast ( (60*(ticket_time/100)+ticket_time-100*
(ticket_time/100)) as numeric (11,6) ) /1440) ) "TB Time",
floor(.5 + 1440*(leave_plant_dt-batch_end_dt)) "End Batch to Leave",
floor(.5 + 1440*(arrive_job_dt-leave_plant_dt)) "To Job Minutes",

FLOOR( floor(.5 + 1440*(arrive_job_dt-leave_plant_dt)) / 60) "To Job Hrs",
floor(.5 + 1440*(arrive_job_dt-leave_plant_dt))- 60*FLOOR( floor(.5 + 1440*(arrive_job_dt-leave_plant_dt)) / 60) "To Job Min",

100*FLOOR( floor(.5 + 1440*(arrive_job_dt-leave_plant_dt)) / 60) +
floor(.5 + 1440*(arrive_job_dt-leave_plant_dt))- 60*FLOOR( floor(.5 + 1440*(arrive_job_dt-leave_plant_dt)) / 60) "To Job HH:MM",

floor(.5 + 1440*(begin_pour_dt-arrive_job_dt)) "Waiting",
floor(.5 + 1440*(end_pour_dt-begin_pour_dt)) "Pour",
floor(.5 + 1440*(leave_job_dt-end_pour_dt)) "Washing",
floor(.5 + 1440*(return_plant_dt-leave_job_dt)) "To Plant",

floor(1440*(return_plant_DT-cast(ticket_Date as timestamp )-cast ( (60*(ticket_time/100)+ticket_time-100*
(ticket_time/100)) as numeric (11,6) ) /1440 ) ) "TTR_MIN",

100*floor(
floor(1440*(return_plant_DT-cast(ticket_Date as timestamp )-cast ( (60*(ticket_time/100)+ticket_time-100*
(ticket_time/100)) as numeric (11,6) ) /1440 ) ) / 60 ) +

floor(1440*(return_plant_DT-cast(ticket_Date as timestamp )-cast ( (60*(ticket_time/100)+ticket_time-100*
(ticket_time/100)) as numeric (11,6) ) /1440 ) ) -
60*floor(
floor(1440*(return_plant_DT-cast(ticket_Date as timestamp )-cast ( (60*(ticket_time/100)+ticket_time-100*
(ticket_time/100)) as numeric (11,6) ) /1440 ) ) / 60 ) TTHHMM

from artslshd

inner join artslsdt td on artslsdt.session_no=artslshd.session_no and artslsdt.trans_no=artslshd.trans_no and artslsdt.line_no=1

inner join artdriv on artdriv.driver_code = artslshd.driver
where driver is not null and batch_start_dt is not null and batch_end_dt is not null
and ticket_date between:Begin_date and :End_date and driver is not null and void_flag ^='Y'
order by artdriv.driver_name, ticket_date, ticket_no

</PRE>

Time Calculation Queries - Revision history

WikiAdmin: 1 revision imported

Intra>User: 1 revision imported