SQL query to find employees entry and exits or transfers by dates [closed]

by

How to display employee entry, exists, transfers, absences in a single row rather than multiple rows in Oracle?

Employee id Entry/Exitcode Date Emp_office Description Text
001 E1 01-apr-23 Hawaii Joining Code Entry
001 E2 04-apr-23 Hawaii Leave Exit
001 E6 30-apr-23 Nevada Transfer Entry
001 E8 05-may-23 Nevada Left the company Exit

Expected output:

Employee id Entry Date Exit Date
001 01-apr-23 04-apr-23
001 30-apr-23 05-may-23

  • Help us help you – share some sample data and the result you’re expecting to get.

    – 

  • What entry/exit codes exist? Are there any other codes in the table that we must ignore? Do you consider a ‘Transfer’ always an entry or can it also be an exit? Does an exit always immediately follow an entry? Please describe the rule how to exactly find an entry/exit pair.

    – 

  • In the above example transfer will be an Entry but it can be an exit in another situation.An exit can be followed by another exit or entry.Hope this helps.

    – 

  • Oracle has LAG and LEAD functions. Have you done any research and attempt? Edit question to show SQL.

    – 




  • Again: Please give us a precise rule by which we can tell an entry from an exit. If I look at a row in the table: How to know whether it is an entry or an exit? And how to find its counterpart (i.e. the associated exit for an entry and the associated entry for an exit). Describe exactly, think of every constellation, leave no doubts. (This is the first step before writing a query anyway.)

    – 




Leave a Comment