With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.
select employee_pk
, max(work_start_dt) last_start
, max(work_end_dt) last_end
, max(pk) last_pk
from tbl_Employee_WorkRecords
group by employee_pk;
The query is very close to what is requested in A, except for the fact that the individual results aren't synchronized:select t.*
from tbl_Employee_WorkRecords t
where t.work_start_dt =
( SELECT MAX(i.work_start_dt)
from tbl_Employee_WorkRecords i
where i.employee_pk = t.employee_pk
);
A different syntax you will come across often is this technique:
select t.*
from tbl_Employee_WorkRecords t
join ( select employee_pk, max(work_start_dt) max_start_dt
from tbl_Employee_WorkRecords
group by employee_pk
) i
on i.employee_pk = t.employee_pk
and i.max_start_dt = t.work_start_dt
--- MS SQL Server
select t.*
from tbl_Employee_WorkRecords t
where convert(varchar(10), t.work_start_dt, 120) =
( SELECT convert(varchar(10), MAX(i.work_start_dt), 120)
from tbl_Employee_WorkRecords i
where i.employee_pk = t.employee_pk
)
-- ms sql server
select t.*
from tbl_Employee_WorkRecords t
where t.pk = ( select top 1 i.pk
from tbl_Employee_WorkRecords i
where i.employee_pk = t.employee_pk
order by i.work_start_dt DESC
)
The main problem is that the pk field should only identify the row, and not have any other meaning, like ordering the results.-- oracle => incorrect code
select t.*
from tbl_Employee_WorkRecords t
where t.pk = ( select i.pk
from tbl_Employee_WorkRecords i
where i.employee_pk = t.employee_pk
and rownum = 1
order by i.work_start_dt DESC
)
Reason: Oracle applies the WHERE ROWNUM = 1 before the ORDER BY.-- oracle
select sq.*
from ( SELECT t.*
, ROW_NUMBER() OVER ( PARTITION BY employee_pk ORDER BY work_start_dt DESC ) rn
FROM tbl_Employee_WorkRecords t
) sq
WHERE sq.rn = 1
When you remove the last condition WHERE sq.rn=1, you will see the additional column rn in the results, which should be self-explaining: The same syntax will actually work in MS SQL Server 2005 or higher, but not in SQL 2000 or lower, though.--- MS SQL Server
select t.*
from tbl_Employee_WorkRecords t
where convert(varchar(10), t.work_start_dt, 120) =
( SELECT convert(varchar(10), MAX(i.work_start_dt), 120)
from tbl_Employee_WorkRecords i
where i.employee_pk = t.employee_pk
)
--- MS SQL Server
select t.*
from tbl_Employee_WorkRecords t
-- the work_start_dt needs to be higher than the max(work_start_dt) for this employee, -1 day
where t.work_start_dt >=
( SELECT DATEADD(DAY, -1, MAX(i.work_start_dt))
from tbl_Employee_WorkRecords i
where i.employee_pk = t.employee_pk
)
-- ensure the record(s) are on the same date, actually, and not on the previous day than MAX(work_start_dt)
AND convert(varchar(10), t.work_start_dt, 120) =
( SELECT convert(varchar(10), MAX(i.work_start_dt), 120)
from tbl_Employee_WorkRecords i
where i.employee_pk = t.employee_pk
)
The same technique will work for MySQL, you only need to replace the CONVERT() expression, for example using the TO_DAYS function:--- MySQL
select t.*
from tbl_Employee_WorkRecords t
-- the work_start_dt needs to be higher than the max(work_start_dt) for this employee, -1 day
where t.work_start_dt >=
( SELECT DATEADD(DAY, -1, MAX(i.work_start_dt))
from tbl_Employee_WorkRecords i
where i.employee_pk = t.employee_pk
)
-- ensure the record(s) are on the same date, actually, and not on the previous day than MAX(work_start_dt)
AND TO_DAYS(t.work_start_dt) =
( SELECT TO_DAYS(MAX(i.work_start_dt))
from tbl_Employee_WorkRecords i
where i.employee_pk = t.employee_pk
)
And in Oracle, the function would be called TRUNC(), which leads to a much better solution:--- Oracle
select t.*
from tbl_Employee_WorkRecords t
-- the work_start_dt needs to be higher than the max(work_start_dt) for this employee, -1 day
where t.work_start_dt >=
( SELECT TRUNC(MAX(i.work_start_dt))
from tbl_Employee_WorkRecords i
where i.employee_pk = t.employee_pk
)
MS SQL Server doesn't know how to truncate a date the same way, but we can use for example:--- MS SQL Server
select t.*
from tbl_Employee_WorkRecords t
-- the work_start_dt needs to be higher than the max(work_start_dt) for this employee, -1 day
where t.work_start_dt >=
( SELECT CONVERT(DATETIME, CONVERT(VARCHAR(10), MAX(i.work_start_dt), 120), 120)
from tbl_Employee_WorkRecords i
where i.employee_pk = t.employee_pk
)
To save space, I won't repeat the same method again, just mention the 2 functions you will like to use: str_to_date() and date_format()-- oracle
select sq.*
from ( SELECT t.*
, RANK () OVER ( PARTITION BY employee_pk ORDER BY TRUNC(work_start_dt) DESC ) rn
FROM tbl_Employee_WorkRecords t
) sq
WHERE sq.rn = 1
Explanation: RANK() and DENSE_RANK() will hence give a rn=1 for all the values of the same DATE (as the time will be truncated => ignored).-- oracle
select sq.*, e.Name
from ( SELECT t.*
, ROW_NUMBER() OVER ( PARTITION BY employee_pk ORDER BY work_start_dt DESC ) rn
FROM tbl_Employee_WorkRecords t
) sq
join tbl_Employee e
ON e.pk = sq.employee_fk
WHERE sq.rn = 1
-- oracle
select sq.*, e.Name
from tbl_Employee e
left join ( SELECT t.*
, ROW_NUMBER() OVER ( PARTITION BY t.employee_pk ORDER BY t.work_start_dt DESC ) rn
FROM tbl_Employee e
JOIN tbl_Employee_WorkRecords t
ON e.pk = sq.employee_fk
WHERE e.last_name like 'B%'
) sq
ON e.pk = sq.employee_fk
AND sq.rn = 1
WHERE e.last_name like 'B%'
select sq.*, e.Name
from tbl_Employee e
cross apply ( SELECT TOP 1 *
FROM tbl_Employee_WorkRecords t
WHERE t.employee_fk = e.pk
ORDER BY t.work_start_dt DESC
) sq
Here, the "JOIN condition" is done inside the CROSS APPLY, and instead of using ROW_NUMBER() function, we use the TOP x ... ORDER BY syntax, which will eventually be more readable/understandable for most people.