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!
GROUP BY
. You don't have any aggregation.