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.
coalesce(rtrim(ltrim(known_as)),per.FIRST_FORNAME) as FORENAME,
per.SURNAME as SURNAME,
post.number_R as POST_NUMBER,
post.long_desc as POST_LONG_DESC,
narr_occtype.long_desc as OCC_TYPE,
narr_joinreason.long_desc as POST_JOIN_REASON,
narr_joinreason2.long_desc as EMP_JOIN_REASON,
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'
POSITION_STATUS = 'CONTRA' OR
OCC_TYPE like 'Contractor' AND
EMPSTARTDATE between sysdate and sysdate