Oracle Database Discussions

Hi,

4102954 wrote:
Hi All,
I have employee table with 3 fields Empid,Join_Date,Term_Date.I need data based one below conditions on Join_Date and Term_Date.
1)Join_Date(can be latest as 30 days) should satisfy condition as,sysdate>=Join_Date<=sysdate+30 but Join_Date can not be greater than Term_Date
2)Term_Date(can be latest as 30 days)should satisfy condition as,sysdate>=Term_Date<=sysdate+30 but TERM_Date can not be less than Join_Date
I'm using below query.Please let me know is there any alternative way or correct me if it is wrong.I'm just started sql practice.
select emp_id,join_date,term_date
from employee
where join_date<=sysdate+30 and join_date<term_date
and term_date<=sysdate+30 and term_date>join_date

Is there a mistake above?  If
sysdate>=Join_Date          , that means join_date is in the past so
Join_Date<=sysdate+30    will always be true, and if
sysdate>=Term_Date        , then
Term_Date<=sysdate+30   will always be true.

If you want to test that

1. join_date is between 0 and 30 days in the future (that is, SYSDATE <= join_date <= SYSDATE + 30)
2. term_date is between 0 and 30 days in the future, and
3. join_date is not later than term_date

then you can use a condition like

    join_date  BETWEEN SYSDATE
               AND     SYSDATE + 30
AND term_date  BETWEEN SYSDATE
               AND     SYSDATE + 30
AND join_date  <= term_date

A lot of people use TRUNC (SYSDATE) intead of SYSDATE for conditions like this, so the same data will produce the same results all day long.

I hope this answers your question.
If not, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the results you want from that data.
Explain, using specific examples, how you get those results from that data.
Always say what version of Oracle you're using (e.g. 12.1.0.2.0).
See the forum FAQ:

Well, join_date <= term_date is generic condition and has to be enforced as check constraint. Then there wouldn't be a need to add that check to every query. Also, sysdate includes time portion, so most likely you should use trunc(sysdate). And it is not clear what are you looking for. I'll assume you need employees who were hired already or accepted job offer to start in less than 30 days from today and if they left the company then termination date is less than 30 days from today. I'll assume termination date is null for active employees who didn't file for termination. Then use:

select  emp_id,

        join_date,

        term_date

  from  employee

  where join_date <= trunc(sysdate) + 30

    and (

            term_date <= trunc(sysdate) + 30

         or

            term_date is null

        )

/

SY.

Ah, I missed "sysdate>=Join_Date<=sysdate+30 ":

select  emp_id,

        join_date,

        term_date

  from  employee

  where join_date between trunc(sysdate) and trunc(sysdate) + 30

    and term_date between trunc(sysdate) and trunc(sysdate) + 30

/

But such query doesn't make much sense unless you are looking for employees who started and quit today or accepted the offer and then decided not to join.

SY.

There are several problems with your question, the way you asked it.

The following points were already made in the previous replies:

• You wrote   sysdate>=Join_Date<=sysdate+30   which doesn't make much sense. Did you mean the first inequality to be <= just like the second inequality? That would make much more sense.
• You use sysdate+30 everywhere, that means into the future. Is that really what you mean? If you mean "in the past 30 days", then that's different; that is NOT what you wrote.
• If your dates (in the table) have time-of-day component, then comparing to sysdate doesn't make a lot of sense - since sysdate also has time-of-day. Running the query at 11 a.m. might give you a different result than running it at 9 a.m. - almost surely not what is desired.
• Even if the values in the table do not have time-of-day (actually they always do in Oracle; what we really mean is that "if the time-of-day is 00:00:00 in the table"), you may still not want to compare to sysdate. If an employee has Join_Date of today (stored as time-of-day 00:00:00) and you run a query today at 10 a.m. requiring that Join_Date>=sysdate, that row will not be included in the output (because it does not satisfy the inequality). In almost all cases such queries will use trunc(sysdate), not sysdate. (Meaning, "today" but with time-of-day set to 00:00:00.)
• You say "no later than" and "no earlier than" between Join_Date and Term_Date, but you use strict inequality. That is incorrect. The conditions you expressed in words are translated into non-strict inequality:  Join_Date <= Term_Date.

Here are a couple more points to consider:

• If you require Join_Date <= Term_Date, either by way of a constraint (as Solomon suggested) - which is by far the better way to do it - or in the WHERE clause of your query, you don't need to check five inequalities - you only need three. If sysdate <= Join_Date AND Join_Date <= Term_Date AND Term_Date <= sysdate + 30, then automatically sysdate <= Term_Date and also Join_Date <= sysdate + 30. You know this (or you don't, but regardless, it's true - this property is called "transitivity"), but Oracle isn't as smart; if you write five conditions in the WHERE clause, it will check all five, even if two of them are redundant. Best to only write as few conditions as necessary - but see a big exception right below this.
• With that said, IF you have an index on Join_Date (as perhaps you already do, or perhaps as you should, depending on how often you query your data based on Join_Date), then it is still a good idea to require four conditions. Namely, by all means, write both conditions for Join_Date:  Join_Date between sysdate and sysdate + 30,  or - much more likely and more common - Join_Date between trunc(sysdate) and trunc(sysdate) + 30. This way, the condition is (possibly) much more selective, and the optimizer may choose to use the index for accessing rows.

So, with all that said, and assuming that you really want a 30-day window in the FUTURE, not the PAST (which is still not entirely clear from your post), you probably want something like

select emp_id, join_date, term_date
from   employees
where  join_date between trunc(sysdate) and trunc(sysdate) + 30  --  or just trunc(sysdate) <= join_date if join_date is not indexed.
  and  join_date <= term_date    --  you don't need this, and you shouldn't put it in the query, if it is already a constraint.
  and  term_date <= trunc(sysdate) + 30 
;

Hi All,

Thanks for your reply.In my first condition i need data including past joining data and including sysdate+30 so i need data  between past and sysdate+30.so i used in script where join_date<=sysdate+30 and join_date<term_date.same way term_date also have past date as well.

But in above script shared by you will not return past join_date records?Please correct me if im wrong.

The problem is probably your English, not your logic.

So, you need to include all data from the past (no matter how old), as well as from the future, up to 30 days from today? This makes sense, and matches the code you wrote; but you confused us all when you wrote things like

sysdate>=Join_Date<=sysdate+30

What is the first part of that condition (highlighted in red) supposed to mean?

=   =   =   =   =   =   =   =

So - if the conditions are join_date <= trunc(sysdate) + 30 and term_date <= trunc(sysdate) + 30 and join_date <= term_date, then:  First of all, you don't need the first condition (because it follows from the other two). Second, if join_date <= term_date is already a constraint in your table (rows are not allowed to exist in the table if they don't already satisfy this condition), then the last inequality is not needed in the query either. (Note: In the table, even with the constraint join_date <= term_date, term_date is allowed to be NULL. But in your query, if you require term_date <= trunc(sysdate) + 30, then term_date will in particular be non-NULL. The constraint is also satisfied if join_date is NULL, but presumably the business requirements are that join_date should be NOT NULL to begin with.)

So the query could simply be

select ....  
from   .....
where  term_date <= trunc(sysdate) + 30
  and  join_date <= term date    -- only needed if it's not a constraint on the table

Hi,

4102954 wrote:
Hi All,
Thanks for your reply.In my first condition i need data including past joining data and including sysdate+30 so i need data between past and sysdate+30.so i used in script where join_date<=sysdate+30 and join_date<term_date.same way term_date also have past date as well.
But in above script shared by you will not return past join_date records?Please correct me if im wrong.

Sorry, it's not clear what you want.  Please post a little sample data (something people can actually run, like CREATE TABLE and INSERT statements), and the exact results you want from that data.

See the Forum FAQ: