Oracle SQL for employees starting today (STARTDATE = SYSDATE??)

hi guys,

I have the following to get information about employees. Im having difficulty finding employees who start today... My thinking was EMPSTARTDATE = SYSDATE which does work, but it also returns an employee who started on 11/05/2015 as well as the 13/05/2015.


I simply want this to find any employees starting today. Its probably very simple.

with
cte_TEST
as
(
select 
e.PERSON_REF,
e.EMPLOYEE_NUMBER,
coalesce(rtrim(ltrim(known_as)),per.FIRST_FORNAME)  as FORENAME,
per.SURNAME as SURNAME,
per.NI_NO NINO,
per.INITIALS INITS,
per.TITLE TITLE,
position_status,
post.number_R as POST_NUMBER,
post.long_desc as POST_LONG_DESC,
e.START_DATE EMPSTARTDATE,
e.END_DATE EMPENDDATE,
CGrp.ID CGRPID,
CGrp.LONG_DESC CGRPDESC,
PGrp.ID PGRPID,
PGrp.LONG_DESC PGRPDESC,
narr_occtype.long_desc as OCC_TYPE,
narr_joinreason.long_desc as POST_JOIN_REASON,
narr_joinreason2.long_desc as EMP_JOIN_REASON,
ph.projected_end_date
from D550M 
e
left outer join D500M per on e.PERSON_REF = per.PERSON_REF
left outer join D100M CGrp on e.LEVEL1_PAY_STR_REF = CGrp.REF
left outer join D100M PGrp on e.PAY_STR_REF = PGrp.REF 
left outer join D580M ph on e.PERSON_REF = ph.PERSON_REF and
( ph.start_date <= sysdate and (ph.end_date is null or ph.end_Date >= sysdate))
left outer join D200M post on ph.ref  = post.ref
left outer join D800M narr_occtype on ph.occ_type = narr_occtype.narrative_code and narr_occtype.narrative_category = 'OCCTYP'
left outer join D800M narr_joinreason on ph.join_reason= narr_joinreason.narrative_code and narr_joinreason.narrative_category = 'RSNCHG'
left outer join D800M narr_joinreason2 on e.orig_how_joined = narr_joinreason2.narrative_code and narr_joinreason2.narrative_category = 'RSNCHG'

)
select distinct
--PERSON_REF,
EMPLOYEE_NUMBER,
TITLE,
FORENAME,
SURNAME,
projected_end_date,
position_status,
EMPSTARTDATE,
from 
cte_TEST

where 
POSITION_STATUS  = 'CONTRA' OR 
OCC_TYPE like 'Contractor' AND

EMPSTARTDATE between sysdate and sysdate  
 
      

Open in new window

Richiep86Asked:
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.

slightwv (䄆 Netminder) Commented:
>>but it also returns an employee who started on 11/05/2015 as well as the 13/05/2015.

Really not possible just based on the date comparison.

Look at he AND/OR logic:
where
POSITION_STATUS  = 'CONTRA' OR
OCC_TYPE like 'Contractor' AND
EMPSTARTDATE between sysdate and sysdate  

A OR B AND C will return a row for a true match on A and ignore everything else.


Also: Oracle dates have a time portion built in so I doubt " EMPSTARTDATE = SYSDATE " will return anything since sysdate is the down to the exact second you call it.
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
sdstuberCommented:
Do you mean you want to find people hired during the 24 hour period of today's date?

If so then try this...

(EMPSTARTDATE  >= trunc(sysdate) and start_date < trunc(sysdate)+1)

this should be an efficient query condition


or,  do you mean you want people hired on today's date, regardless of the year?  i.e.  All people ever hired on May 13.  If so then try this...

to_char(EMPSTARTDATE ,'mmdd') = to_char(sysdate,'mmdd')

this is not an efficient query condition in most cases, but, if needed, can be helped with a function-based index on  to_char(EMPSTARTDATE ,'mmdd')



if you mean something else,  then please post some sample data with expected results.
0
Richiep86Author Commented:
Thanks for the response...


So basically, this is a:

- scheduled query which will run every day
- It should find contractors who are starting in the company today.

So if my EMPSTARTDATE was today, i would get an email stating there was a contractor starting in the company today.

Here is the output of my query:

      0000002      MR      RICHARD      AULTWO                   CONTRA                11/05/2015      
      0000006      MR      LOYALTY       FIVEYEARS            CONTRA                 13/05/2010

Maybe i need to convert/trim the sysdate to get rid of the timestamp attached.

Ill give that a go.

Thanks for that!

Rich
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

sdstuberCommented:
don't do something like this...


trunc(empstartdate) = trunc(sysdate)  -- this is bad

instead,  do as I showed in the previous post

(EMPSTARTDATE  >= trunc(sysdate) and start_date < trunc(sysdate)+1)   -- this is good
0
Richiep86Author Commented:
select distinct
EMPLOYEE_NUMBER,
TITLE,
FORENAME,
SURNAME,
projected_end_date,
position_status,
EMPSTARTDATE

from 
cte_lv

where 
POSITION_STATUS  = 'CONTRA' OR 
OCC_TYPE like 'Contractor' AND


(EMPSTARTDATE  >= trunc(sysdate) and EMPSTARTDATE  < trunc(sysdate)+1)

 

Open in new window

This still returns an employee who started 5 years ago!

      0000002      MR      RICHARD      AULTWO                    CONTRA         11/05/2015      
     0000006            LOYALTY       FIVEYEARS            CONTRA          13/05/2010      

Maybe something is stored in the cache.. i cant work out why this doesnt work.
0
Richiep86Author Commented:
Even:

trunc(empstartdate) = trunc(sysdate)

Open in new window


Returns someone who started 5 years ago. Im going to kill the app and start again....
0
Richiep86Author Commented:
to_char(EMPSTARTDATE ,'mmdd') = to_char(sysdate,'mmdd')

Open in new window


So does this!
0
sdstuberCommented:
you have an OR condition breaking your logic

simplify to test...

select * from dual where 1=1 or 1=2 and 2=3


wrap your OR condition in () to make sure you aren't creating a shortcut in the logic

where
(POSITION_STATUS  = 'CONTRA' OR OCC_TYPE like 'Contractor')  
AND
(EMPSTARTDATE  >= trunc(sysdate) and EMPSTARTDATE  < trunc(sysdate)+1)
0
sdstuberCommented:
>>> to_char(EMPSTARTDATE ,'mmdd') = to_char(sysdate,'mmdd')

as noted in my first post  this SHOULD return previous years -  notice the explict removal of YEAR from the formatting.


but, as desribed in my previous post,  the problem isn't the date comparisons,  the problem is your use of OR in your other conditions
0
Richiep86Author Commented:
Iv been lookin at this too long and my eyes are deceiving me (even tho i pasted the output 2,3 times)

where
POSITION_STATUS  = 'CONTRA' 
AND
to_char(EMPSTARTDATE ,'mmddyy') = to_char(sysdate,'mmddyy')

Open in new window


works.

sdsuber - thank you for all your help and being so patient.

Much appreciated. x
0
slightwv (䄆 Netminder) Commented:
>>sdsuber - thank you for all your help and being so patient.

FYI:  I pointed out the logic issue in the very first post.

>>to_char(EMPSTARTDATE ,'mmddyy') = to_char(sysdate,'mmddyy')

I also second the suggestion by sdstuber to NOT do it that way.  Use the code he provided.  That is unless there is no index on empstartdate.
0
Richiep86Author Commented:
Excellent support.

Thank you so much for being patient with me.

Rich
0
slightwv (䄆 Netminder) Commented:
It appears the primary issue was the OR logic.  The secondary issue was the time portion of the date.

Any reason there wasn't a split of the points?
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.