oracle date question

I am running a script  on every Wednesday. So on this coming Wednesday,  January 8th the report should include Friday, January 5th through Monday, January 8th.

Starting Wednesday (10-Jan-2017) and every weekday morning thereafter run the query for that day and the prior 3 days. For example on Monday, January 8th the report should include Friday, January 5th through Monday, January 8th.

 SELECT ship.id ship_id
            ,ship.shipper_no
            ,shptrk.id shptrk_id
            ,shptrk.waybill
            ,shptrk.estimated_freight
            ,shptrk.actual_freight
            ,shptrk.dt_created
        FROM dss.shipments ship
            ,dss.shipment_trackings shptrk
        WHERE ship.order_id = in_order_id
          AND shptrk.ship_id = ship.id
          AND shptrk.dt_created BETWEEN -----

Open in new window


shptrk.dt_created as my requirement. Thanks in advance for the help.
LVL 6
anumosesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewIT / Software Engineering ConsultantCommented:
If shptrk.dt_created just contains a date (no time) then you should be able to do:

 SELECT ship.id ship_id
            ,ship.shipper_no
            ,shptrk.id shptrk_id
            ,shptrk.waybill
            ,shptrk.estimated_freight
            ,shptrk.actual_freight
            ,shptrk.dt_created
        FROM dss.shipments ship
            ,dss.shipment_trackings shptrk
        WHERE ship.order_id = in_order_id
          AND shptrk.ship_id = ship.id
          AND shptrk.dt_created BETWEEN TRUNC(SYSDATE-3) AND  TRUNC(SYSDATE)

Open in new window


If it contains time infothen you could do:

 SELECT ship.id ship_id
            ,ship.shipper_no
            ,shptrk.id shptrk_id
            ,shptrk.waybill
            ,shptrk.estimated_freight
            ,shptrk.actual_freight
            ,shptrk.dt_created
        FROM dss.shipments ship
            ,dss.shipment_trackings shptrk
        WHERE ship.order_id = in_order_id
          AND shptrk.ship_id = ship.id
          AND shptrk.dt_created > TRUNC(SYSDATE-3) 
          AND shptrk.dt_created < TRUNC(SYSDATE+1)

Open in new window


»bp
0
awking00Information Technology SpecialistCommented:
where shptrk_dt_created >= trunc(sysdate - 5) and shptrk_dt_created < trunc(sysdate - 1)
0
awking00Information Technology SpecialistCommented:
Bill Prew, I posted my comment before I saw yours but you still need to take into account the two-day lag between Wednesday when the query is run and the Monday before. So this -
AND shptrk.dt_created > TRUNC(SYSDATE-3)
          AND shptrk.dt_created < TRUNC(SYSDATE+1)
should be this -
AND shptrk.dt_created > TRUNC(SYSDATE-5)
          AND shptrk.dt_created < TRUNC(SYSDATE-1)
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Bill PrewIT / Software Engineering ConsultantCommented:
It wasn't clear to me what the correct interpretation of the question text was, there were some inconsistencies in it.  SO I went off of the last line:

For example on Monday, January 8th the report should include Friday, January 5th through Monday, January 8th.


»bp
0
awking00Information Technology SpecialistCommented:
I've read the question and re-read it and it's become less and less clear what the correct interpretation actually is. A lot of the statements are contradictory.

>>I am running a script  on every Wednesday.<<
>>Starting Wednesday (10-Jan-2017) and every weekday morning thereafter run the query<<
Is it every Wednesday or every weekday?

>>So on this coming Wednesday,  January 8th<<
This coming Wednesday is January 10th
>>on Monday, January 8th the report should include Friday, January 5th through Monday, January 8th.<<
Is this meant to be on Wednesday, January 10th, the report should include Friday, January 5th through Monday, January 8th.?

anumoses, please clarify your intent.
0
anumosesAuthor Commented:
sorry. It will be today and 3 days prior.
0
johnsoneSenior Oracle DBACommented:
BETWEEN TRUNC(SYSDATE)-3 AND TRUNC(SYSDATE) + 1 - (1/86400)

That should give you today and the previous 3 days.  Date math is pretty easy and you can always easily check what you want by using DUAL.

select TRUNC(SYSDATE)-3 start_dt, TRUNC(SYSDATE) + 1 - (1/86400) end_dt from dual;

TRUNC(SYSDATE)+1 in a BETWEEN is incorrect as it includes one second into tomorrow.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Geert GOracle dbaCommented:
why mess with that between so much ?

where shptrk.dt_created >= TRUNC(SYSDATE-3)  AND shptrk.dt_created < TRUNC(SYSDATE+1)

Open in new window


test sample:
with tbl_samples as (
  select sysdate-10 + level/10 as dt_created from dual connect by level < 20*10)
select dt_created, sysdate as this_is_now
from tbl_samples
where dt_created >= trunc(sysdate)-3 
  and dt_created < trunc(sysdate)+1;  

Open in new window

0
johnsoneSenior Oracle DBACommented:
What's wrong with BETWEEN?  It is perfectly legal syntax.  Not sure if this is still true, but the optimizer used to look at a BETWEEN more favorably than <= and >=.  It is the difference between a bounded range scan and an unbounded range scan.  May not do it anymore, but people who have been using Oracle as long as I have were taught to use BETWEEN and not <= and >=.
0
slightwv (䄆 Netminder) Commented:
There is nothing wrong with between by itself.  It has been my experience that the optimizer rewrites it as >= and <= behind the scenes.

The issue comes with users not understanding Oracle dates with time stamps and between being inclusive on the boundaries.  Many forget to add the complete time stamp on the end range of the between statement and can miss data or include rows they don't want.
0
anumosesAuthor Commented:
What is the impact when I use
 
 shptrk.dt_created > TRUNC(SYSDATE-5);

for the day I am running the report and the prior 3 days.
0
Geert GOracle dbaCommented:
there is nothing wrong with using between when you need >= and <=

but it's odd doing extra mathematical sideways calculations when needing >= and < like in this case
nobody has every said, the date column type will remain on second precision ...
i do admit, it will break a lot of things if oracle changes that second precision to something else
0
Geert GOracle dbaCommented:
if you don't have any future data, then that where clause is sufficient
0
johnsoneSenior Oracle DBACommented:
For the purposes of comparison, it probably does look at it as >= and <=.

However, back when I started with Oracle, there was actually a difference in the plan when you used BETWEEN.  I'm not sure if there still is and it is hidden to us or not.  You used to be able to see RANGE SCAN UNBOUNDED and RANGE SCAN BOUNDED in the plan.  BETWEEN would give you BOUNDED and would be weighted differently by the optimizer.  Maybe it doesn't look at that anymore, but there definitely was a difference at one time.  It could be seen in the plan, and it would make a difference to the optimizer.  Changing a >= and <= to a BETWEEN would sometimes give you a better plan.
0
Bill PrewIT / Software Engineering ConsultantCommented:
shptrk.dt_created > TRUNC(SYSDATE-5);

TRUNC() removes any time component from the current date, leaving you with just todays date.  -5 calcs the date 5 days before today.  Then, since you use greater than, the result will include all days greater than 5 days before today, so all days after 4 days ago, inclusive.


»bp
0
anumosesAuthor Commented:
when I run my script using these two dates

where shptrk.dt_created >= trunc(sysdate)-3
  and shptrk.dt_created < trunc(sysdate)+1;

Results in 1187 records

and

shptrk.dt_created > TRUNC(SYSDATE-5);

results in 2801 records.
0
slightwv (䄆 Netminder) Commented:
>>when I run my script using these two dates

and?  You are pulling in more rows because the date range is different.

You know your data and what is correct to return.  We can only tell you what your date math is doing.

>>TRUNC() removes any time component from the current date

Technically it zero's it.  The results are the same.  Just wanted to point out the small distinction.
0
Geert GOracle dbaCommented:
list the differences like this:
with a case you can see when a criteria is met or not

select dt_created, 
  case 
    when dt_created >= trunc(sysdate)-3 and dt_created < trunc(sysdate)+1 then 'CLAUSE >-3 to <+1'
  else 
    'CLAUSE >-5'
  end clause
from shptrk 
where 
  (dt_created >= trunc(sysdate)-3 and dt_created < trunc(sysdate)+1)
  or  
  (dt_created > TRUNC(SYSDATE-5) )

Open in new window


and add an
order by dt_created

Open in new window

at the end
0
awking00Information Technology SpecialistCommented:
The following query will give you the dt_created count for each of the past 5 full days plus a count for today's date so far, and you can see where the difference comes from -
select trunc(shptrk.dt_created) dt, count(*)
from shipments ship, shipment_trackings shptrk
where ship.ship_id = shptrk.ship_id
and ship_order_id = in_order_id
and trunc(shptrk.dt_created) = trunc(sysdate - 5)
group by trunc(shptrk.dt_created)
union all
select trunc(shptrk.dt_created) dt, count(*)
from shipments ship, shipment_trackings shptrk
where ship.ship_id = shptrk.ship_id
and ship_order_id = in_order_id
and trunc(shptrk.dt_created) = trunc(sysdate - 4)
group by trunc(shptrk.dt_created)
union all
select trunc(shptrk.dt_created) dt, count(*)
from shipments ship, shipment_trackings shptrk
where ship.ship_id = shptrk.ship_id
and ship_order_id = in_order_id
and trunc(shptrk.dt_created) = trunc(sysdate - 3)
group by trunc(shptrk.dt_created)
union all
select trunc(shptrk.dt_created) dt, count(*)
from shipments ship, shipment_trackings shptrk
where ship.ship_id = shptrk.ship_id
and ship_order_id = in_order_id
and trunc(shptrk.dt_created) = trunc(sysdate - 2)
group by trunc(shptrk.dt_created)
union all
select trunc(shptrk.dt_created) dt, count(*)
from shipments ship, shipment_trackings shptrk
where ship.ship_id = shptrk.ship_id
and ship_order_id = in_order_id
and trunc(shptrk.dt_created) = trunc(sysdate - 1)
group by trunc(shptrk.dt_created)
union all
select trunc(shptrk.dt_created) dt, count(*)
from shipments ship, shipment_trackings shptrk
where ship.ship_id = shptrk.ship_id
and ship_order_id = in_order_id
and trunc(shptrk.dt_created) = trunc(sysdate)
group by trunc(shptrk.dt_created)
order by trunc(shptrk.dt_created);
0
Geert GOracle dbaCommented:
accessing the same table with a union ?

just delete this line : and trunc(shptrk.dt_created) = trunc(sysdate)
and add a range for the dt_created like dt_created > trunc(sysdate)-5

or just add an extra column to see the offset:
sample query:
with tbl_samples as (
  select sysdate-10 + level/10 as dt_created from dual connect by level < 20*10)
select trunc(dt_created), trunc(dt_created) - trunc(sysdate) delta_sysdate, count(*)total 
from tbl_samples
group by trunc(dt_created), trunc(sysdate)-trunc(dt_created)
order by 1 ;

Open in new window

0
anumosesAuthor Commented:
thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.