For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.
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_Date2)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_DateI'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_datefrom employeewhere join_date<=sysdate+30 and join_date<term_dateand term_date<=sysdate+30 and term_date>join_date
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? Ifsysdate>=Join_Date , that means join_date is in the past soJoin_Date<=sysdate+30 will always be true, and ifsysdate>=Term_Date , thenTerm_Date<=sysdate+30 will always be true.
If you want to test that
then you can use a condition like
join_date BETWEEN SYSDATE AND SYSDATE + 30AND term_date BETWEEN SYSDATE AND SYSDATE + 30AND 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
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 ":
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.
There are several problems with your question, the way you asked it.
The following points were already made in the previous replies:
Here are a couple more points to consider:
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_datefrom employeeswhere 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 ;
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
;
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
select ....
from .....
where term_date <= trunc(sysdate) + 30
and join_date <= term date -- only needed if it's not a constraint on the table
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: