COMPLEX QUERY

[EPNICO]

Hello everyone:

I need to answer this if else question in a sql query:

if status has changed from leave of absence (loa) to active
what is the enddate of the leave of absence (loa).

My MS sql database contains two table ebase and eemploy
there is one record for every employee in ebase and there could be many records for an employee in eemploy.
I'm stuck as to how to code it in sql since the row that I'm interested in eemploy is no longer the current row the active one is. Is there a way to handle this situation in sql.??

Any ideas are welcomed.

 

[rac2]

Maybe, maybe not. Which tables are the status and enddate columns in? When are values inserted and updated? By any chance is the beginning date for each new status stored? Is one of the tables a history table?

 

[EPNICO]

Sorry about that.
eemploy table has a status field and the leave of absence field.
Also in the eemploy table we have a date beginning and ending date of the status. So we would have for epnico if he came back from loa status two records.

example:
username status date beginning ending date
epnico active 06/19/2003 nulls
epnico loa 04/01/2003 06/19/2003
.
.
.

 

[rac2]

It seems that the condition you describe would be equivalent to a leave-of-absence record with the ending date filled. So-

SELECT * FROM eemploy WHERE [ending date] IS NOT NULL AND status = 'loa'

Possibly some employees have more than one leave-of-absence and you need the latest one.

SELECT eemploy.* FROM eemploy
JOIN (
       SELECT username, MAX([ending date]) AS dateEndLastLOA
       FROM eemploy
       WHERE status = 'loa' 
         AND [ending date] IS NOT NULL
      ) LastLOA
   ON eemploy.[ending date] = LastLOA.dateEndLastLOA

Or maybe just for a particular employee.

SELECT ebase.*, eemploy.* FROM eemploy
JOIN (
       SELECT username, MAX([ending date]) AS dateEndLastLOA
       FROM eemploy
       WHERE status = "" 
         AND [ending date] IS NOT NULL
      ) LastLOA
   ON eemploy.[ending date] = LastLOA.dateEndLastLOA
JOIN ebase ON eemploy.username = ebase.username
WHERE ebase.username = 'George'

 

[EPNICO]

Thanks for the information. Your ideas are good ones.

Thanks again.