Hello,
I have a SQL query that pulls data on a rolling 24 hour timeframe. It resets at 3am every day. I'm unfamiliar with SQL but have tried changing some things around unsuccessfully. I need this to only capture 5pm - 6am, instead of 3am - 3am.
Here's the code:
SELECT dts.wh_id "Whse",
dts.usr_id "User ID",
dts.usr_nm "User Name",
to_char(dts.min_dtm, 'hh:mi AM') "Start",
to_char(dts.max_dtm, 'hh:mi AM') "End",
round((dts.max_dtm - dts.min_dtm) *24, 2) "WMS Hrs",
round((dts.plts_rcvd / pdv_rcv.rtnum1) + (dts.plts_load / pdv_load.rtnum1) + (dts.plts_put / pdv_put.rtnum1) + (dts.plts_rpln / pdv_rpln_fp.rtnum1) + (dts.fp_pck / pdv_pck_fp.rtnum1) + (dts.cp_pck / pdv_pck_cp.rtnum1) + (dts.ip_pck / pdv_pck_ep.rtnum1), 2) "Prd Hrs",
dts.plts_rcvd "Plts Rcvd",
dts.plts_load "Plts Load",
dts.plts_put "Plts Put",
dts.plts_rpln "Plts Rpln",
dts.fp_pck "FP Pick",
dts.cp_pck "CP Pick",
dts.cp_pck_loc "CP Locs",
dts.ip_pck "IP Pick"
FROM (SELECT 1 srt_ord,
dtd.wh_id,
dtd.usr_id,
uv.last_name || ', ' || uv.first_name usr_nm,
min(dtd.min_dtm) min_dtm,
max(dtd.max_dtm) max_dtm,
sum(dtd.plts_rcvd) plts_rcvd,
sum(dtd.plts_load) plts_load,
sum(dtd.plts_put) plts_put,
sum(dtd.plts_rpln) plts_rpln,
sum(dtd.fp_pck) fp_pck,
sum(dtd.cp_pck) cp_pck,
sum(dtd.cp_pck_loc) cp_pck_loc,
sum(dtd.ea_pck) ip_pck
FROM (SELECT dt.wh_id,
dt.usr_id,
min(dt.trndte) min_dtm,
max(dt.trndte) max_dtm,
count(distinct dt.lodnum) plts_rcvd,
0 plts_load,
0 plts_put,
0 plts_rpln,
0 fp_pck,
0 cp_pck,
0 cp_pck_loc,
0 ea_pck
FROM dlytrn dt
WHERE dt.wh_id = '8325'
and trunc(dt.trndte - (3 / 24)) = trunc(sysdate - (3 / 24))
and exists(SELECT 'x'
FROM aremst am
WHERE am.wh_id = dt.wh_id
and am.arecod = dt.fr_arecod
and am.expflg = 1
and am.wip_expflg = 0)
and exists(SELECT 'x'
FROM aremst am
WHERE am.wh_id = dt.wh_id
and am.arecod = dt.to_arecod
and am.rdtflg = 1)
GROUP BY dt.wh_id,
dt.usr_id
UNION ALL
SELECT dt.wh_id,
dt.usr_id,
min(dt.trndte) min_dtm,
max(dt.trndte) max_dtm,
0 plts_rcvd,
count(distinct dt.lodnum) plts_load,
0 plts_put,
0 plts_rpln,
0 fp_pck,
0 cp_pck,
0 cp_pck_loc,
0 ea_pck
FROM dlytrn dt
WHERE dt.wh_id = '8325'
and dt.oprcod = 'LOD'
and dt.actcod = 'SHIPLOAD'
and trunc(dt.trndte - (3 / 24)) = trunc(sysdate - (3 / 24))
and exists(SELECT 'x'
FROM aremst am
WHERE am.wh_id = dt.wh_id
and am.arecod = dt.to_arecod
and am.shpflg = 1)
GROUP BY dt.wh_id,
dt.usr_id
UNION ALL
SELECT dt.wh_id,
dt.usr_id,
min(dt.trndte) min_dtm,
max(dt.trndte) max_dtm,
0 plts_rcvd,
0 plts_load,
count(distinct dt.lodnum) plts_put,
0 plts_rpln,
0 fp_pck,
0 cp_pck,
0 cp_pck_loc,
0 ea_pck
FROM dlytrn dt
WHERE dt.wh_id = '8325'
and trunc(dt.trndte - (3 / 24)) = trunc(sysdate - (3 / 24))
and exists(SELECT 'x'
FROM aremst am
WHERE am.wh_id = dt.wh_id
and am.arecod = dt.to_arecod
and am.rdtflg = 1)
and exists(SELECT 'x'
FROM aremst am
WHERE am.wh_id = dt.wh_id
and am.arecod = dt.fr_arecod
and am.rcv_stgflg = 1)
GROUP BY dt.wh_id,
dt.usr_id
UNION ALL
SELECT dt.wh_id,
dt.usr_id,
min(dt.trndte) min_dtm,
max(dt.trndte) max_dtm,
0 plts_rcvd,
0 plts_load,
0 plts_put,
count(distinct dt.lodnum) plts_rpln,
0 fp_pck,
0 cp_pck,
0 cp_pck_loc,
0 ea_pck
FROM dlytrn dt
WHERE dt.wh_id = '8325'
and trunc(dt.trndte - (3 / 24)) = trunc(sysdate - (3 / 24))
and exists(SELECT 'x'
FROM aremst am
WHERE am.wh_id = dt.wh_id
and am.arecod = dt.fr_arecod
and am.rdtflg = 1)
and ((dt.actcod in ('GENMOV', 'LD_XFR') and dt.to_arecod = 'CSPICK1') or (dt.oprcod != 'UPK' and dt.actcod = 'PALPCK'))
GROUP BY dt.wh_id,
dt.usr_id
UNION ALL
SELECT dt.wh_id,
dt.usr_id,
min(dt.trndte) min_dtm,
max(dt.trndte) max_dtm,
0 plts_rcvd,
0 plts_load,
0 plts_put,
0 plts_rpln,
count(distinct dt.lodnum) fp_pck,
0 cp_pck,
0 cp_pck_loc,
0 ea_pck
FROM dlytrn dt
WHERE dt.wh_id = '8325'
and trunc(dt.trndte - (3 / 24)) = trunc(sysdate - (3 / 24))
and dt.oprcod = 'UPK'
and dt.actcod = 'PALPCK'
and exists(SELECT 'x'
FROM aremst am
WHERE am.wh_id = dt.wh_id
and am.arecod = dt.fr_arecod
and am.sigflg = 1)
and exists(SELECT 'x'
FROM aremst am
WHERE am.wh_id = dt.wh_id
and am.arecod = dt.to_arecod
and am.rdtflg = 1)
GROUP BY dt.wh_id,
dt.usr_id
UNION ALL
SELECT dt.wh_id,
dt.usr_id,
min(dt.trndte) min_dtm,
max(dt.trndte) max_dtm,
0 plts_rcvd,
0 plts_load,
0 plts_put,
0 plts_rpln,
0 fp_pck,
sum(ceil(dt.trnqty / pfd.untqty)) cp_pck,
count(distinct dt.frstol || dt.to_lodnum) cp_pck_loc,
0 ea_pck
FROM dlytrn dt
join prtftp pf
on dt.wh_id = pf.wh_id
and dt.prtnum = pf.prtnum
and pf.defftp_flg = 1
join prtftp_dtl pfd
on pf.wh_id = pfd.wh_id
and pf.prt_client_id = pfd.prt_client_id
and pf.prtnum = pfd.prtnum
and pf.ftpcod = pfd.ftpcod
and pfd.cas_flg = 1
WHERE dt.wh_id = '8325'
and dt.actcod = 'CASPCK'
and trunc(dt.trndte - (3 / 24)) = trunc(sysdate - (3 / 24))
and exists(SELECT 'x'
FROM aremst am
WHERE am.wh_id = dt.wh_id
and am.arecod = dt.to_arecod
and am.rdtflg = 1)
GROUP BY dt.wh_id,
dt.usr_id
UNION ALL
SELECT dt.wh_id,
dt.usr_id,
min(dt.trndte) min_dtm,
max(dt.trndte) max_dtm,
0 plts_rcvd,
0 plts_load,
0 plts_put,
0 plts_rpln,
0 fp_pck,
0 cp_pck,
0 cp_pck_loc,
sum(dt.trnqty) ea_pck
FROM dlytrn dt
join prtftp pf
on dt.wh_id = pf.wh_id
and dt.prtnum = pf.prtnum
and pf.defftp_flg = 1
join prtftp_dtl pfd
on pf.wh_id = pfd.wh_id
and pf.prt_client_id = pfd.prt_client_id
and pf.prtnum = pfd.prtnum
and pf.ftpcod = pfd.ftpcod
and pfd.cas_flg = 1
WHERE dt.wh_id = '8325'
and dt.actcod = 'OTRPCK'
and trunc(dt.trndte - (3 / 24)) = trunc(sysdate - (3 / 24))
and exists(SELECT 'x'
FROM aremst am
WHERE am.wh_id = dt.wh_id
and am.arecod = dt.to_arecod
and am.rdtflg = 1)
GROUP BY dt.wh_id,
dt.usr_id) dtd
join users_view uv
on dtd.usr_id = uv.usr_id
GROUP BY dtd.wh_id,
dtd.usr_id,
uv.last_name || ', ' || uv.first_name
HAVING (sum(dtd.plts_rcvd) + sum(dtd.plts_load) + sum(dtd.plts_put) + sum(dtd.fp_pck) + sum(dtd.cp_pck) + sum(dtd.ea_pck)) > 0
UNION ALL
SELECT 2,
dtd.wh_id,
'TOTAL',
null,
null,
null,
sum(dtd.plts_rcvd),
sum(dtd.plts_load),
sum(dtd.plts_put),
sum(dtd.plts_rpln),
sum(dtd.fp_pck),
sum(dtd.cp_pck),
sum(dtd.cp_pck_loc),
sum(dtd.ea_pck)
FROM (SELECT dt.wh_id,
dt.usr_id,
count(distinct dt.lodnum) plts_rcvd,
0 plts_load,
0 plts_put,
0 plts_rpln,
0 fp_pck,
0 cp_pck,
0 cp_pck_loc,
0 ea_pck
FROM dlytrn dt
WHERE dt.wh_id = '8325'
and trunc(dt.trndte - (3 / 24)) = trunc(sysdate - (3 / 24))
and exists(SELECT 'x'
FROM aremst am
WHERE am.wh_id = dt.wh_id
and am.arecod = dt.fr_arecod
and am.expflg = 1
and am.wip_expflg = 0)
and exists(SELECT 'x'
FROM aremst am
WHERE am.wh_id = dt.wh_id
and am.arecod = dt.to_arecod
and am.rdtflg = 1)
GROUP BY dt.wh_id,
dt.usr_id
UNION ALL
SELECT dt.wh_id,
dt.usr_id,
0 plts_rcvd,
count(distinct dt.lodnum) plts_load,
0 plts_put,
0 plts_rpln,
0 fp_pck,
0 cp_pck,
0 cp_pck_loc,
0 ea_pck
FROM dlytrn dt
WHERE dt.wh_id = '8325'
and dt.oprcod = 'LOD'
and dt.actcod = 'SHIPLOAD'
and trunc(dt.trndte - (3 / 24)) = trunc(sysdate - (3 / 24))
and exists(SELECT 'x'
FROM aremst am
WHERE am.wh_id = dt.wh_id
and am.arecod = dt.to_arecod
and am.shpflg = 1)
GROUP BY dt.wh_id,
dt.usr_id
UNION ALL
SELECT dt.wh_id,
dt.usr_id,
0 plts_rcvd,
0 plts_load,
count(distinct dt.lodnum) plts_put,
0 plts_rpln,
0 fp_pck,
0 cp_pck,
0 cp_pck_loc,
0 ea_pck
FROM dlytrn dt
WHERE dt.wh_id = '8325'
and trunc(dt.trndte - (3 / 24)) = trunc(sysdate - (3 / 24))
and exists(SELECT 'x'
FROM aremst am
WHERE am.wh_id = dt.wh_id
and am.arecod = dt.to_arecod
and am.rdtflg = 1)
and exists(SELECT 'x'
FROM aremst am
WHERE am.wh_id = dt.wh_id
and am.arecod = dt.fr_arecod
and am.rcv_stgflg = 1)
GROUP BY dt.wh_id,
dt.usr_id
UNION ALL
SELECT dt.wh_id,
dt.usr_id,
0 plts_rcvd,
0 plts_load,
0 plts_put,
count(distinct dt.lodnum) plts_rpln,
0 fp_pck,
0 cp_pck,
0 cp_pck_loc,
0 ea_pck
FROM dlytrn dt
WHERE dt.wh_id = '8325'
and trunc(dt.trndte - (3 / 24)) = trunc(sysdate - (3 / 24))
and exists(SELECT 'x'
FROM aremst am
WHERE am.wh_id = dt.wh_id
and am.arecod = dt.fr_arecod
and am.rdtflg = 1)
and ((dt.actcod in ('GENMOV', 'LD_XFR') and dt.to_arecod = 'CSPICK1') or (dt.oprcod != 'UPK' and dt.actcod = 'PALPCK'))
GROUP BY dt.wh_id,
dt.usr_id
UNION ALL
SELECT dt.wh_id,
dt.usr_id,
0 plts_rcvd,
0 plts_load,
0 plts_put,
0 plts_rpln,
count(distinct dt.lodnum) fp_pck,
0 cp_pck,
0 cp_pck_loc,
0 ea_pck
FROM dlytrn dt
WHERE dt.wh_id = '8325'
and trunc(dt.trndte - (3 / 24)) = trunc(sysdate - (3 / 24))
and dt.oprcod = 'UPK'
and dt.actcod = 'PALPCK'
and exists(SELECT 'x'
FROM aremst am
WHERE am.wh_id = dt.wh_id
and am.arecod = dt.fr_arecod
and am.sigflg = 1)
and exists(SELECT 'x'
FROM aremst am
WHERE am.wh_id = dt.wh_id
and am.arecod = dt.to_arecod
and am.rdtflg = 1)
GROUP BY dt.wh_id,
dt.usr_id
UNION ALL
SELECT dt.wh_id,
dt.usr_id,
0 plts_rcvd,
0 plts_load,
0 plts_put,
0 plts_rpln,
0 fp_pck,
sum(ceil(dt.trnqty / pfd.untqty)) cp_pck,
count(distinct dt.frstol || dt.to_lodnum) cp_pck_loc,
0 ea_pck
FROM dlytrn dt
join prtftp pf
on dt.wh_id = pf.wh_id
and dt.prtnum = pf.prtnum
and pf.defftp_flg = 1
join prtftp_dtl pfd
on pf.wh_id = pfd.wh_id
and pf.prt_client_id = pfd.prt_client_id
and pf.prtnum = pfd.prtnum
and pf.ftpcod = pfd.ftpcod
and pfd.cas_flg = 1
WHERE dt.wh_id = '8325'
and dt.actcod = 'CASPCK'
and trunc(dt.trndte - (3 / 24)) = trunc(sysdate - (3 / 24))
and exists(SELECT 'x'
FROM aremst am
WHERE am.wh_id = dt.wh_id
and am.arecod = dt.to_arecod
and am.rdtflg = 1)
GROUP BY dt.wh_id,
dt.usr_id
UNION ALL
SELECT dt.wh_id,
dt.usr_id,
0 plts_rcvd,
0 plts_load,
0 plts_put,
0 plts_rpln,
0 fp_pck,
0 cp_pck,
0 cp_pck_loc,
sum(dt.trnqty) ea_pck
FROM dlytrn dt
join prtftp pf
on dt.wh_id = pf.wh_id
and dt.prtnum = pf.prtnum
and pf.defftp_flg = 1
join prtftp_dtl pfd
on pf.wh_id = pfd.wh_id
and pf.prt_client_id = pfd.prt_client_id
and pf.prtnum = pfd.prtnum
and pf.ftpcod = pfd.ftpcod
and pfd.cas_flg = 1
WHERE dt.wh_id = '8325'
and dt.actcod = 'OTRPCK'
and trunc(dt.trndte - (3 / 24)) = trunc(sysdate - (3 / 24))
and exists(SELECT 'x'
FROM aremst am
WHERE am.wh_id = dt.wh_id
and am.arecod = dt.to_arecod
and am.rdtflg = 1)
GROUP BY dt.wh_id,
dt.usr_id) dtd
join users_view uv
on dtd.usr_id = uv.usr_id
GROUP BY dtd.wh_id) dts
left
join poldat_view pdv_rcv
on pdv_rcv.wh_id = dts.wh_id
and pdv_rcv.polcod = 'USR-EXEL'
and pdv_rcv.polvar = 'RPT-VIEW'
and pdv_rcv.polval = 'PROD-RATES'
and pdv_rcv.rtstr1 = 'RCV'
left
join poldat_view pdv_load
on pdv_load.wh_id = dts.wh_id
and pdv_load.polcod = 'USR-EXEL'
and pdv_load.polvar = 'RPT-VIEW'
and pdv_load.polval = 'PROD-RATES'
and pdv_load.rtstr1 = 'LOD'
left
join poldat_view pdv_put
on pdv_put.wh_id = dts.wh_id
and pdv_put.polcod = 'USR-EXEL'
and pdv_put.polvar = 'RPT-VIEW'
and pdv_put.polval = 'PROD-RATES'
and pdv_put.rtstr1 = 'PUT'
left
join poldat_view pdv_rpln_fp
on pdv_rpln_fp.wh_id = dts.wh_id
and pdv_rpln_fp.polcod = 'USR-EXEL'
and pdv_rpln_fp.polvar = 'RPT-VIEW'
and pdv_rpln_fp.polval = 'PROD-RATES'
and pdv_rpln_fp.rtstr1 = 'RPLN-FP'
left
join poldat_view pdv_pck_fp
on pdv_pck_fp.wh_id = dts.wh_id
and pdv_pck_fp.polcod = 'USR-EXEL'
and pdv_pck_fp.polvar = 'RPT-VIEW'
and pdv_pck_fp.polval = 'PROD-RATES'
and pdv_pck_fp.rtstr1 = 'PCK-FP'
left
join poldat_view pdv_pck_cp
on pdv_pck_cp.wh_id = dts.wh_id
and pdv_pck_cp.polcod = 'USR-EXEL'
and pdv_pck_cp.polvar = 'RPT-VIEW'
and pdv_pck_cp.polval = 'PROD-RATES'
and pdv_pck_cp.rtstr1 = 'PCK-CP'
left
join poldat_view pdv_pck_ep
on pdv_pck_ep.wh_id = dts.wh_id
and pdv_pck_ep.polcod = 'USR-EXEL'
and pdv_pck_ep.polvar = 'RPT-VIEW'
and pdv_pck_ep.polval = 'PROD-RATES'
and pdv_pck_ep.rtstr1 = 'PCK-EP'
WHERE dts.usr_id != 'SUPER'
ORDER BY dts.srt_ord DESC,
dts.usr_nm