Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
eg10013
Starting Member
3 Posts |
 Posted - 2014-10-24 : 14:04:00
|
| I am not a sql programmer. Our sql guy left recently. I got a request to modify a query to return exempt full time employees who also has a second job. The current query returns all active full time employee (tbl_emp status = 'A' or 'a'). Now the new request is to return (1) tbl_emp status = 'j' // second jobAND(2) tbl_emp status = 'A' or 'a' AND tbl_emptype = 'Y' //separated row - who also is a full time exempt employee HERE IS THE ORIGIAN QUERY.SELECT e.[person],e.[location] AS locationcode,e.[department] AS departmentcode ,e.[title] AS titlecode,e.[phone],e.[supervisor] AS supervisorcode ,e.[userid],e.[compa],e.[emp_type], e.[status] AS status, e.[user_password] ,RTRIM(p.fname)+' '+RTRIM(p.lname) AS empname ,RTRIM(s.fname)+' '+RTRIM(s.lname) AS svpname ,d.[description] AS department, t.[description] AS title ,l.[description] AS location,sE.userid as superuserid FROM [time_sheet].[dbo].[tbl_emp] e FULL OUTER JOIN [time_sheet].[dbo].[tbl_person] p ON e.[person] = p.[recid] FULL OUTER JOIN [time_sheet].[dbo].[tbl_person] s ON e.[supervisor] = s.[recid] FULL OUTER JOIN [time_sheet].[dbo].[tbl_department] d ON e.[department] = d.[recid] FULL OUTER JOIN [time_sheet].[dbo].[tbl_title] t ON e.[title] = t.[recid] FULL OUTER JOIN [time_sheet].[dbo].[tbl_location] l ON e.[location] = l.[recid] FULL OUTER JOIN [time_sheet].[dbo].[tbl_emp] sE ON sE.[recid] = e.[supervisor] WHERE e.[status] = 'A' AND UPPER(p.status) = 'A' AND UPPER(s.status) = 'A' AND sE.status = 'a' ORDER BY e.[person]I really appreciate any help. Many thanksI still not get the sql guy yet. I really I appreciate if you can help me out. |
|
|
lazerath
Constraint Violating Yak Guru
343 Posts |
Posted - 2014-10-24 : 15:12:53
|
Where are you located? The default collation is case insensitive in the US, so checking for 'A' or 'a' would have the same result as just 'A' or just 'a'. Also, you are wrapping a function around the column UPPER() and it means SQL can't use an index for that condition, so if your collation is case insensitive you can eliminate that for performance gains. This principal is called SARGable / non-SARGable and stands for Search Argument able. Read about it on wikipedia.Other than that, I am having some trouble understanding what you want. You say "modify a query to return exempt full time employees who also has a second job". Does that mean "instead of" or "in addition to" what is already provided? If you are adding this to the resultset, just change your where clause to:WHERE e.[status] IN('A','J') AND p.status = 'A' AND s.status = 'A' AND sE.status = 'a' I am not quite clear on how tbl_emptype = 'Y' plays into it. If that column is declared as NOT NULL and you only want it to be applied when e.status = 'A', you could do this: WHERE e.[status] IN('A','J') AND p.status = 'A' AND s.status = 'A' AND sE.status = 'a' AND e.type = CASE e.status WHEN 'A' THEN 'Y' ELSE e.type ENDThis will be pretty fast but it won't work if NULLs are present in the data. To solve for that, you could do:WHERE e.[status] IN('A','J') AND p.status = 'A' AND s.status = 'A' AND sE.status = 'a' AND COALESCE(e.type,'') = CASE e.status WHEN 'A' THEN 'Y' ELSE COALESCE(e.type,'') ENDThe downside is that Type is now non-SARGable, so SQL can't seek to an index with it. Since you have other criteria such as status, it may be a moot point, but it's still not an ideal practice.The other way you could handle this that would have maximum performance in this case is to just split the query into two with explicit criteria defined and then use UNION ALL to combine them.Keep in mind that you also probably don't need all those FULL OUTER JOINS, especially when your where clause contradicts it. All those column = 'value' means the resulting row must be present, so the FULL OUTER JOIN should really just be INNER JOIN for those tables. |
 |
|
|
eg10013
Starting Member
3 Posts |
Posted - 2014-10-24 : 15:36:14
|
| Thank you lazerath for your response.(1) I am in US so I may not need UPPER function.(2) I need to return employee second job row (tbl_emp statu = 'j') if the employee also has a primary job (tbl_emp status = 'a') AND the employee type for the primary job is exempt (tbl_emp emp_type = 'Y'). |
|
|
|
lazerath
Constraint Violating Yak Guru
343 Posts |
Posted - 2014-10-24 : 16:30:57
|
| So, for clarity, your data will have two rows in [tbl_emp] for an employee with two jobs. One row will have status = 'j' and the other will have status = 'a' with emp_type = 'y'. You want both rows in your output, but only if the the criteria on the second row is met?If that is the case, use UNION ALL. Copy the original query for the second part and then just modify the where clause to reflect your criteria (e.status = 'j', etc..). To include this second row if the first condition is met, you will need to use EXISTS(SELECT * FROM tbl_emp AS e2 WHERE e.[employee primary key] = e2.[employee primary key] AND e2.[status] = 'A' AND e2.type = 'Y'). There are a couple other ways to do it, but that should work for you. |
|
|
|
eg10013
Starting Member
3 Posts |
Posted - 2014-10-24 : 18:06:29
|
| Thanks again. I just want the second job row to be return but it is ok if both rows are return. As I mentioned I am not familiar with complex SQL query and not really understand your suggestion. Do you mind put the query together for. Many thanks. |
|
|
|
|
|
|
|
|
Need help on query filter