1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Help with date/time

Discussion in 'SQL PL/SQL' started by killerthun, Jul 21, 2016.

  1. killerthun

    killerthun Starter

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    TN
    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
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Try changing all occurrences of

    and trunc(dt.trndte - (3 / 24)) = trunc(sysdate - (3 / 24))

    to

    and trunc(dt.trndte - (17 / 24)) = trunc(sysdate - (6 / 24))

    You may need to 'play' with it to get the correct interval.