WikiAdmin: 1 revision imported

2023-07-14T13:00:35Z

1 revision imported

← Older revision

Revision as of 13:00, 14 July 2023

Intra>Chanson: /* Dispatch Times Super Mega Query */

2020-02-25T15:28:19Z

Dispatch Times Super Mega Query

New page

Dispatch Time Analysis (03/30/17)

===Dispatch Times Super Mega Query===
This beast gives you most every time calculation you would want from Plant, Office and Sale ticket history. Along with some basic ticket header info you get:
*Actual Times for each status event
*Target Times for each status event
*Minutes in Each status
*Minutes Late for Each Status Event (Negative = Early)
*Minutes over for each status (Negative = Under)

<div class="toccolours mw-collapsible">
<PRE>
SELECT
plant_no,ticket_no, cust_no,job_po_loc, zone, dr.driver_name,truck_no,
batch_start_dt, batch_end_dt, leave_plant_dt, arrive_job_dt, begin_pour_dt, end_pour_dt, leave_job_dt, return_plant_dt,
tgt_batch_start_dt, tgt_batch_end_dt, tgt_leave_plant_dt, tgt_arrive_job_dt, tgt_begin_pour_dt, tgt_end_pour_dt, tgt_leave_job_dt, tgt_return_plant_dt,

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) ) "TICKET_TO_BATCH_MIN",

floor(.5 + 1440*(batch_end_dt-batch_start_dt)) "BATCH_MIN",
floor(.5 + 1440*(leave_plant_dt-batch_end_dt)) "PLANT_WASH_MIN",
floor(.5 + 1440*(arrive_job_dt-leave_plant_dt)) "TO_JOB_MIN",
floor(.5 + 1440*(begin_pour_dt-arrive_job_dt)) "JOB_WAIT_MIN",
floor(.5 + 1440*(end_pour_dt-begin_pour_dt)) "POUR_MIN",
floor(.5 + 1440*(leave_job_dt-end_pour_dt)) "JOB_WASH_MIN",
floor(.5 + 1440*(return_plant_dt-leave_job_dt)) "TO_PLANT_MIN",
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 ) ) RTT,

floor(.5 + 1440*(batch_start_dt-tgt_batch_start_dt)) BATCH_START_LATE,
floor(.5 + 1440*(batch_end_dt-tgt_batch_end_dt)) BATCH_END_LATE,
floor(.5 + 1440*(leave_plant_dt-tgt_leave_plant_dt)) LEAVE_PLANT_LATE,
floor(.5 + 1440*(arrive_job_dt-tgt_arrive_job_dt)) ARRIVE_JOB_LATE,
floor(.5 + 1440*(begin_pour_dt-tgt_begin_pour_dt)) BEGIN_POUR_LATE,
floor(.5 + 1440*(end_pour_dt-tgt_end_pour_dt)) END_POUR_LATE,
floor(.5 + 1440*(leave_job_dt-tgt_leave_job_dt)) LEAVE_JOB_LATE,
floor(.5 + 1440*(return_plant_dt-tgt_return_plant_dt)) RETURN_PLANT_LATE,

floor(.5 + 1440*((batch_end_dt-batch_start_dt)-(tgt_batch_end_dt-tgt_batch_start_dt))) "BATCH_OVR",
floor(.5 + 1440*((leave_plant_dt-batch_end_dt)- (tgt_leave_plant_dt-tgt_batch_end_dt))) "PLANT_WASH_OVR",
floor(.5 + 1440*((arrive_job_dt-leave_plant_dt)-(tgt_arrive_job_dt-tgt_leave_plant_dt))) "TO_JOB_OVR",
floor(.5 + 1440*((begin_pour_dt-arrive_job_dt)-(tgt_begin_pour_dt-tgt_arrive_job_dt))) "JOB_WAIT_OVR",
floor(.5 + 1440*((end_pour_dt-begin_pour_dt)-(tgt_end_pour_dt-tgt_begin_pour_dt))) "POUR_OVR",
floor(.5 + 1440*((leave_job_dt-end_pour_dt)-(tgt_leave_job_dt-tgt_end_pour_dt))) "JOB_WASH_OVR",
floor(.5 + 1440*((return_plant_dt-leave_job_dt)-(tgt_return_plant_dt-tgt_leave_job_dt))) "TO_PLANT_OVR",
order_load_no "LD_NO",
extract (hour from batch_start_dt) "BAT_STRT_HR",
QTY_SOLD,th.elapsed_time,
TICKET_DATE ,TICKET_TIME,order_code
FROM DIP_TICKETHISTORY
('Y','Y','Y','Y',:BEG_DATE,:END_DATE,null,null,NULL,NULL,NULL,NULL,null,null) th
left outer join artdriv dr on dr.driver_code = th.driver
order by plant_no,ticket_no
</PRE>
</div>

(Corrected OVR fields on 02/25/20)

If you need to ID first loads you can add this to your field list...
<PRE>
,
(SELECT
'*' FROM DIP_TICKETHISTORY
('Y','Y','Y','Y',TH.TICKET_DATE,TH.TICKET_DATE,TH.CUST_NO,TH.CUST_NO,NULL,NULL,NULL,NULL,TH.PLANT_NO,TH.PLANT_NO) thX
WHERE THX.ORDER_CODE=TH.ORDER_CODE
having min(order_load_no)=th.order_load_no) "Firsty"
</PRE>

DIP_TICKETHISTORY Parameters:
*'''INC_CUR_TIX''' Plant tickets
*'''INC_EXP_TIX''' Exported Plant tickets
*'''INC_OFF_TIX''' Office tickets
*'''INC_INV_TIX''' Sales
*'''LODATE''' Ticket Date Begin
*'''HIDATE''' Ticket Date End
*'''LOCUST''' Begin Customer
*'''HICUST''' End Customer
*'''LOCCLS''' Low Cust Class
*'''HICCLS''' High Cust Class
*'''LOPCLS''' Low Product Class
*'''HIPCLS''' Hight Product Class
*'''LOPLANT''' Begin Plant
*'''HIPLANT''' End Plant

Use these parameters and not a where clause whenever possible. This is much more efficient. (e.g. if you put a where clause for plant rather than use the parameter, the procedure will first process data for all plants and then filter out all the unselected plants).

===Dispatch Ticket Yardage Report===
<div class="toccolours mw-collapsible">
<PRE>
select h.cust_no,h.job_po_loc,h.tax_authority,h.ticket_date,h.ticket_no,h.plant_no,h.product_code,h.description,h.qty_sold,h.tax_code,
h.void_flag,source_table STATUS from
DIP_TICKETHISTORY ('Y','Y','Y','Y',:BEG_DATE,:END_DATE,'','z',NULL,NULL,NULL,NULL,:"Begin Plant",:"End Plant") h
where ticket_date between :beg_date and :end_date AND LINE_NO=1 and void_flag='N'
</PRE>
</DIV>

Old Style
<div class="toccolours mw-collapsible">
<PRE>
select h.cust_no,h.job_po_loc,h.tax_authority,h.ticket_date,h.ticket_no,h.plant_no,d.product_code,d.description,d.qty_sold,d.tax_code,
h.void_flag,h.closed_flag,'D' STATUS from artptkhd h
inner join artptkdt d on d.session_no=h.session_no and d.trans_no=h.trans_no
where ticket_date between :beg_date and :end_date AND D.LINE_NO=1 AND PLANT_NO BETWEEN :Beg_Plant and :End_Plant and void_flag='N'Union
select h.cust_no,h.job_po_loc,h.tax_authority,h.ticket_date,h.ticket_no,h.plant_no,d.product_code,d.description,d.qty_sold,d.tax_code,h.void_flag,'N','T' STATUS
from arttckhd h
inner join arttckdt d on d.session_no=h.session_no and d.trans_no=h.trans_no
where ticket_date between :beg_date and :end_date AND D.LINE_NO=1 AND PLANT_NO BETWEEN :Beg_Plant and :End_Plant and void_flag='N' UNION
select h.cust_no,h.job_po_loc,h.tax_authority,h.ticket_date,h.ticket_no,h.plant_no,d.product_code,d.description,d.qty_sold,d.tax_code,h.void_flag,'N','S' STATUS
from artSLShd h
inner join artSLSdt d on d.session_no=h.session_no and d.trans_no=h.trans_no
where ticket_date between :beg_date and :end_date AND D.LINE_NO=1 AND PLANT_NO BETWEEN :Beg_Plant and :End_Plant and void_flag='N'
</PRE>
</DIV>

===Ticket Detail Report===
<div class="toccolours mw-collapsible">
<PRE>
select h.cust_no,h.job_po_loc,h.tax_authority,h.ticket_date,h.ticket_no,h.plant_no,h.product_code,h.description,h.qty_sold,h.tax_code,h.void_flag,h.source_table STATUS from
DIP_TICKETHISTORY ('Y','Y','Y','Y',:BEG_DATE,:END_DATE,'','z',NULL,NULL,NULL,NULL,:"Begin Plant",:"End Plant") h
where ticket_date between :beg_date and :end_date AND LINE_NO=1 and void_flag='N'
</PRE>
</DIV>

Old Style:
<div class="toccolours mw-collapsible">
<PRE>
select h.cust_no,h.job_po_loc,h.tax_authority,h.ticket_date,h.ticket_no,h.plant_no,d.product_code,d.description,d.qty_sold,d.tax_code,h.void_flag,h.closed_flag,'D' STATUS from artptkhd h
inner join artptkdt d on d.session_no=h.session_no and d.trans_no=h.trans_no
where ticket_date between :beg_date and :end_date
Union
select h.cust_no,h.job_po_loc,h.tax_authority,h.ticket_date,h.ticket_no,h.plant_no,d.product_code,d.description,d.qty_sold,d.tax_code,h.void_flag,'N','T' STATUS
from arttckhd h
inner join arttckdt d on d.session_no=h.session_no and d.trans_no=h.trans_no
where ticket_date between :beg_date and :end_date
UNION
select h.cust_no,h.job_po_loc,h.tax_authority,h.ticket_date,h.ticket_no,h.plant_no,d.product_code,d.description,d.qty_sold,d.tax_code,h.void_flag,'N','S' STATUS
from artSLShd h
inner join artSLSdt d on d.session_no=h.session_no and d.trans_no=h.trans_no
where ticket_date between :beg_date and :end_date
</PRE>
</DIV>

Tickets by Order Code
<div class="toccolours mw-collapsible">
<PRE>
select h.cust_no,h.job_po_loc,h.tax_authority,h.ticket_date,h.ticket_no,h.plant_no,d.product_code,d.description,d.qty_sold,d.tax_code,h.void_flag,h.closed_flag,'D' STATUS from artptkhd h
inner join artptkdt d on d.session_no=h.session_no and d.trans_no=h.trans_no
where h.order_code=:order_code
Union
select h.cust_no,h.job_po_loc,h.tax_authority,h.ticket_date,h.ticket_no,h.plant_no,d.product_code,d.description,d.qty_sold,d.tax_code,h.void_flag,'N','T' STATUS
from arttckhd h
inner join arttckdt d on d.session_no=h.session_no and d.trans_no=h.trans_no
where h.order_code=:order_code
UNION
select h.cust_no,h.job_po_loc,h.tax_authority,h.ticket_date,h.ticket_no,h.plant_no,d.product_code,d.description,d.qty_sold,d.tax_code,h.void_flag,'N','S' STATUS
from artSLShd h
inner join artSLSdt d on d.session_no=h.session_no and d.trans_no=h.trans_no
where h.order_code=:order_code
</PRE>
</DIV>

Query/Export Dispatch Reports - Revision history

WikiAdmin: 1 revision imported

Intra>Chanson: /* Dispatch Times Super Mega Query */