Databases: SQL query using group by and having

Suppose you have a database containing employee information, where the table structure is roughly:

temployee
employeeid
firstname
lastname
...

tcontract
employeeid
contractnumber
date_end
…

Table “temployee” holds 1 record for every employee, while tcontract holds multiple contracts per employee. If you need to get a result set containing 1 single record per employee and the combined latest date_end per record, use the following SQL query structure:

select employeeid,
firstname,
lastname,
tcontract1.date_end
from temployee,
tcontract tcontract1,
tcontract tcontract2
where temployee.employeeid = tcontract1.employeeid
and temployee.employeeid = tcontract2.employeeid
group by temployee.employeeid,
firstname,
lastname
having tcontract1.date_end = max(tcontract2.date_end)
order by temployee.employeeid

If the date_end is empty for unlimited contracts you will run into a problem with the above query, since only real date values are taken into account for each record. To remedy this use the following query structure (SQL server only, replace “isnull” with “nvl” and “getdate()” with “sysdate” on Oracle):

select employeeid,
firstname,
lastname,
isnull(tcontract1.date_end, getdate() + 1000)
from temployee,
tcontract tcontract1,
tcontract tcontract2
where temployee.employeeid = tcontract1.employeeid
and temployee.employeeid = tcontract2.employeeid
group by temployee.employeeid,
firstname,
lastname
having isnull(tcontract1.date_end, getdate() + 1000) = max(isnull(tcontract2.date_end, getdate() + 1000))
order by temployee.employeeid

• database
• sql