0

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!

6
  • StackExchange is not a free coding service website. You should try to solve your problem. If it doesn't work, we can try to help. – Eric Jan 29 '20 at 18:26
  • @Eric the first table was a result of combination from multiple tables. I was trying to make it show better results. Initially, I tried simply by adding the names in the ORDER BY clause. But it didn't change the result. Next thing I did was introduce group by but I'm not very good at SQL and I keep encountering aggregate function errors. I'm not sure if aggregating is the way to go since I don't think I can aggregate by name. I could be wrong, though. – ericute Jan 29 '20 at 18:34
  • If possible could your post some examples of the SQL statements that you have tried and raw sample data. – armitage Jan 29 '20 at 19:10
  • Hi, @armitage! I'll update the question with sample query. – ericute Jan 29 '20 at 19:17
  • @ericute I don't see why would you need a GROUP BY. You don't have any aggregation. – Eric Jan 29 '20 at 20:22
0

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.

1
  • This is the first thing I tried but it didn't accomplish what I was hoping it would. There are entries between the Joe Armstrong one. – ericute Jan 29 '20 at 19:47
0

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'
2
  • I tried executing the above code but it gave me confusing MinTime and MaxTime. Thanks for your input! – ericute Jan 30 '20 at 11:24
  • "Confusing MinTime and MaxTime" doesn't really help us help you. So what happen to the above sample data when you apply my query? What do you get? – Eric Jan 30 '20 at 16:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.