Order by date then Group By by Name / Type before proceeding with the rest of results

Question

I have a data like this that came from two tables.

FirstName  LastName     LeaveType  LeaveDateFrom  LeaveDateTo    
Jane       Doe          VL         2019/01/02     2019/01/02
Joe        Armstrong    BVL        2019/01/02     2019/01/02
Joe        Armstrong    BVL        2019/01/01     2019/01/01
Joe        Armstrong    BVL        2018/12/31     2018/12/31
Joan       Day          VL         2018/12/31     2018/12/31
Ivan       Reyes        VL         2018/12/28     2018/12/28
Joe        Armstrong    BVL        2018/12/27     2018/12/27
John       Doe          VL         2018/12/27     2018/12/27
Jake       Doe          VL         2018/12/27     2018/12/27
Joe        Armstrong    BVL        2018/12/26     2018/12/26

The data above is a result of the following query:

SELECT emp.Emp_Fname as FirstName, 
emp.Emp_Lname as LastName, lt.LeaveType as LeaveType,
CONVERT(VARCHAR(10), lt.TimeIn, 111) as LeaveDateFrom,
CONVERT(VARCHAR(10), lt.TimeIn, 111) as LeaveDateTo
FROM py_leavetable lt
LEFT JOIN py_emp_master emp ON lt.EmpCd = emp.Emp_Cd
WHERE emp.date_resign IS NULL    
AND CONVERT(VARCHAR(10), lt.TimeIn, 112) >= 20181101
AND CONVERT(VARCHAR(10), lt.TimeIn, 112) <= 20190130
ORDER BY lt.TimeIn DESC

I tried adding the name fields in the ORDER BY clause but it didn't change the results. So I tried using GROUP BY. However, I ended up needing to add all the fields because it keeps returning aggregate function error. I tried researching further and it seems I need to do "mathematical operations" for the GROUP BY to work but I don't think it is applicable for the data I have. But, I could be wrong since I'm not really very good at SQL.

Can anyone help me or point me in the right direction on how I can achieve the following results:

FirstName  LastName     LeaveType  LeaveDateFrom  LeaveDateTo
Jane       Doe          VL         2019/01/02     2019/01/02
Joe        Armstrong    BVL        2019/01/02     2019/01/02
Joe        Armstrong    BVL        2019/01/01     2019/01/01
Joe        Armstrong    BVL        2018/12/31     2018/12/31
Joe        Armstrong    BVL        2018/12/27     2018/12/27
Joe        Armstrong    BVL        2018/12/26     2018/12/26
Joan       Day          VL         2018/12/31     2018/12/31
Ivan       Reyes        VL         2018/12/28     2018/12/28
John       Doe          VL         2018/12/27     2018/12/27
Jake       Doe          VL         2018/12/27     2018/12/27

It would even be better if I'm able to do it like this but having the result above should do:

FirstName  LastName     LeaveType  LeaveDateFrom  LeaveDateTo
Jane       Doe          VL         2019/01/02     2019/01/02
Joe        Armstrong    BVL        2018/12/26     2019/01/02
Joan       Day          VL         2018/12/31     2018/12/31
Ivan       Reyes        VL         2018/12/28     2018/12/28
John       Doe          VL         2018/12/27     2018/12/27
Jake       Doe          VL         2018/12/27     2018/12/27

Am I making sense here? Can anyone help me figure this out? Thanks!

Answer 1

When adding fields to an ORDER BY clause, you must put them in the correct sequence. If your order by clause went from

ORDER BY lt.TimeIn DESC

to

ORDER BY lt.TimeIn DESC, emp.Emp_Lname, emp.Emp_Fname

then the result set will be sorted first by the TimeIn value, then by name for those rows that had matching TimeIn values.

Assuming that you want to sort by Last Name, then First Name, then Time In, you would use the order by:

ORDER BY emp.Emp_Lname, emp.Emp_Fname, lt.TimeIn DESC

This will sort first by the Last Name, then First Name, then within those sorted groups you would sort by Time In, giving you the results you are hoping for.

Answer 2

This is not really the answer, but it's too long to put in comment section. What happen if you run this code? Do you get the right MIN and MAX values?

SELECT emp.Emp_Fname as FirstName, 
    emp.Emp_Lname as LastName, lt.LeaveType as LeaveType,
    CONVERT(VARCHAR(10), lt.TimeIn, 111) as LeaveDateFrom,
    CONVERT(VARCHAR(10), lt.TimeIn, 111) as LeaveDateTo
    , MIN(lt.TimeIn) OVER(PARTITION BY emp.FirstName, emp.LastName, lt.LeaveType) AS MinTime
    , MAX(lt.TimeIn) OVER(PARTITION BY emp.FirstName, emp.LastName, lt.LeaveType) AS MaxTime
FROM py_leavetable lt
LEFT JOIN py_emp_master emp ON lt.EmpCd = emp.Emp_Cd
WHERE emp.date_resign IS NULL    
    AND CONVERT(VARCHAR(10), lt.TimeIn, 112) >= '20181101'
    AND CONVERT(VARCHAR(10), lt.TimeIn, 112) <= '20190130'