Determining which Staff has been here longest

Question

So I have this table. How can I know which Staff has been here the longest?

create table Staff (
    StaffId     char(5) not null,
    StaffName   varchar(20) not null,
    StaffPhone  varchar(13),
    StaffIc     char(14),
    JoinDate    date,
    primary key(StaffId),
    constraint chk_StaffIc check (REGEXP_LIKE(StaffIc,'\d{6}\-\d{2}\-
    \d{4}')),
    constraint chk_StaffPhone check (REGEXP_LIKE(StaffPhone,'^01[0-9]-
    \d{7}|\d{8}$'))
);

The title and the text of the question makes no sense together. Dou you want to retrieve the staff that have been there the longest or create an object? — litelite, Aug 08 '17 at 14:33
Replying to Unlockedluca : I'm still trying to find how to compare the JoinDate of the staff and today's date as I'm still fresh to SQL. — Jason Hew, Aug 08 '17 at 14:35
Replying to litelite : Sorry I forgot to change the topic hahaha. I apologize for the mistake made. — Jason Hew, Aug 08 '17 at 14:36
you don't need to compare `JoinDate` to today's date, you only want the row with the "lowest" `JoinDate`. — René Vogt, Aug 08 '17 at 14:38
What if I want to calculate how long has it been for the staff to work at the place? — Jason Hew, Aug 08 '17 at 14:39
see here for time between two dates: [link](https://stackoverflow.com/questions/13577898/sql-time-difference-between-two-dates-result-in-hhmmss) — Icculus018, Aug 08 '17 at 14:41
@JasonHew you should use an arobase when replying to people, it notifies them of your message, which they might otherwise miss. — Aaron, Aug 08 '17 at 14:51
What happens when one stuff is no longer stuff? delete? or add a field for endDate. Do you need to confirm is stuff is still stuff? — Horaciux, Aug 08 '17 at 14:54
@Aaron I see. Thank you. Still exploring StackOverflow hahah — Jason Hew, Aug 08 '17 at 14:55

score 1 · Answer 1 · answered Aug 08 '17 at 14:59

For the longest, use top 1 and order byJoinDate ascending. Shortest would be descending:

select      top 1
            StaffId,
            StaffName,
            StaffPhone,
            StaffIc,
            JoinDate,
from        Staff
order by    JoinDate asc

score 0 · Answer 2 · answered Aug 08 '17 at 14:59

0

This will tell you how long in days, it could also be month, years, etc. See DATEDIFF()

select top 1 staffid, staffname, DATEDIFF(day,joindate,getdate()) from Staff
order by joindate

This use GETDATE() to obtain current day

answered Aug 08 '17 at 14:59

Horaciux

6,322
2
22
41

Determining which Staff has been here longest

2 Answers2