March 16, 2015 at 11:27 am
Hi
I have a sql query that lists the pay records(time entries) for the employee by Pay Period ID. Issue is that if the employee doesn't have anything entered in the Pay record table for that Pay Period ID, it won't return those employees. BAsically I want to ALSO list employee whose record is not inside Payrecords. My sql is little bit complex because it contains agreegate functions.
SELECT
SUM(Pay_Records.hours) AS Hours,
Pay_Records.rate_type,
Employee.last_name + ',' + Employee.first_name As Name,
SUM(CASE WHEN valid_payroll = 1 THEN Hours ELSE 0 END) AS ValidatedHours,
Pay_Records.employee_no
FROM Pay_Records INNER JOIN Employee
ON Pay_Records.employee_no = Employee.employee_no
WHERE
(Pay_Records.pay_period_id = 544)
GROUP BY Pay_Records.rate_type, Employee.first_name, Employee.last_name,
Pay_Records.employee_no
ORDER BY Name
This will return results for those employees who has an entry inside Pay_records table. How do I list those Employees that aren't inside Pay records table but are inside Employee table. In the hours column it will be 0 for those employees . Do I do a Union? Please advise
Thanks
March 16, 2015 at 12:24 pm
I might have over done the ISNULLs.
SELECT SUM(isnull(Pay_Records.hours,0)) AS Hours, isnull(Pay_Records.rate_type,'NONE') as rate_type,
Employee.last_name + ',' + Employee.first_name As Name,
SUM(CASE WHEN isnull(valid_payroll,0) = 1 THEN Hours ELSE 0 END) AS ValidatedHours,
Employee.employee_no
FROM Employee left JOIN Pay_Records ON Pay_Records.employee_no = Employee.employee_no
WHERE Pay_Records.pay_period_id = 544
GROUP BY isnull(Pay_Records.rate_type,'NONE'), Employee.last_name + ',' + Employee.first_name,
Employee.employee_no
ORDER BY Name
[/code]
March 16, 2015 at 12:48 pm
I wasn't sure which table "valid_payroll" is in, so I guessed "Employee". If it's not, adjust the code accordingly. Btw, always use a table alias on every column in a joined query to avoid such ambiguities.
SELECT isnull(pr.hours,0) AS Hours, isnull(pr.rate_type,'NULL') as rate_type,
e.last_name + ',' + e.first_name As Name,
CASE WHEN valid_payroll = 1 THEN pr.Hours ELSE 0 END AS ValidatedHours,
e.employee_no
FROM Employee e
LEFT JOIN (
SELECT employee_no, rate_type, SUM(hours) AS Hours
FROM Pay_Records
WHERE pay_period_id = 544 --AND valid_payroll = 1
GROUP BY employee_no, rate_type
) AS pr
ON pr.employee_no = e.employee_no
ORDER BY Name
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 16, 2015 at 12:53 pm
MADAdmin. Thanks for your reply
That didn't work for me. I'm getting same result set after left joining. I'm not getting the employees from the other table that don't exist in Pay records table. This what I did so far and it looks like I'm over doing it and there is a easier method. I did a union. The problem is that I want to set order by Name but also want to place all the employees at the bottom of who has 0 hrs (or doesn't exist in Pay Record table)
SELECT
SUM(Pay_Records.hours) AS Hours,
Pay_Records.rate_type,
Employee.last_name + ',' + Employee.first_name As Name,
Employee.department,
Employee.status,
Employee.pay_type,
SUM(CASE WHEN valid_payroll = 1 THEN Hours ELSE 0 END) AS ValidatedHours,
Pay_Records.employee_no
FROM Pay_Records INNER JOIN Employee
ON Pay_Records.employee_no = Employee.employee_no
WHERE
(Pay_Records.pay_period_id = 545) AND Pay_Type=1
GROUP BY Pay_Records.rate_type, Employee.first_name, Employee.last_name,
Employee.department, Employee.status, Employee.pay_type,Pay_Records.employee_no
Union
SELECT
0 AS Hours, 'None' as RateType,
Employee.last_name + ',' + Employee.first_name As Name,
Employee.department, Employee.status,
Employee.pay_type,
0 as ValidatedHours,
Employee.employee_no
FROM Employee WHERE Pay_Type=1 AND Employee.status='A'
AND Employee.employee_no
NOT IN(select Employee_no from Pay_Records Where
Pay_Records.pay_period_id = 545)
GROUP BY Employee.first_name,
Employee.last_name,
Employee.pay_type,Employee.department, Employee.status, employee_no
ORDER BY Name,Rate_type
March 16, 2015 at 1:11 pm
This looks really good. Much simpler. Question is how do I show the employees at the bottom of the list whose hours are 0. I want to sort by Name for those whose records are found in Pay records table. After that I want to show employees with 0 hours
Thank YOU!
March 16, 2015 at 1:14 pm
ScottPletcher (3/16/2015)
I wasn't sure which table "valid_payroll" is in, so I guessed "Employee". If it's not, adjust the code accordingly. Btw, always use a table alias on every column in a joined query to avoid such ambiguities.
SELECT isnull(pr.hours,0) AS Hours, isnull(pr.rate_type,'NULL') as rate_type,
e.last_name + ',' + e.first_name As Name,
CASE WHEN valid_payroll = 1 THEN pr.Hours ELSE 0 END AS ValidatedHours,
e.employee_no
FROM Employee e
LEFT JOIN (
SELECT employee_no, rate_type, SUM(hours) AS Hours
FROM Pay_Records
WHERE pay_period_id = 544 --AND valid_payroll = 1
GROUP BY employee_no, rate_type
) AS pr
ON pr.employee_no = e.employee_no
ORDER BY Name
This looks really good. Much simpler. Question is how do I show the employees at the bottom of the list whose hours are 0. I want to sort by Name for those whose records are found in Pay records table. After that I want to show employees with 0 hours. How do I seperate the list out?
Thank YOU!
March 16, 2015 at 1:29 pm
ORDER BY CASE WHEN pr.hours > 0 THEN 1 ELSE 2 END, Name
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 16, 2015 at 1:59 pm
ScottPletcher (3/16/2015)
...
ORDER BY CASE WHEN pr.hours > 0 THEN 1 ELSE 2 END, Name
Excellent.
One final Question. The Employee table also has status. Active or Terminated. I want to show all the Employees "Terminated" and "Active" for a particular pay period, BUT don't want to show "Terminated" ones that don't have pay records. The reason for this is the employee table has thousands of terminated employees. it doesn't make sense to show them, only when they have a pay record during a pay period ID.
Here is what I got so far.
SELECT isnull(pr.hours,0) AS Hours, isnull(pr.rate_type,'NULL') as rate_type,
e.last_name + ',' + e.first_name As Name,
e.employee_no, isnull(pr.hours,0) AS ValidatedHours, e.department, e.status
FROM Employee e
LEFT JOIN (
SELECT employee_no, rate_type, SUM(hours) AS Hours, SUM(CASE WHEN valid_payroll = 1 THEN Hours ELSE 0 END) AS ValidatedHours
FROM Pay_Records
WHERE pay_period_id = 543
GROUP BY employee_no, rate_type, valid_payroll
) AS pr
ON pr.employee_no = e.employee_no
Where e.status='A' and e.pay_type=1
ORDER BY CASE WHEN pr.hours > 0 THEN 1 ELSE 2 END, Name
This query will show all "Active" ones. I want to show Terminated ones as well that has Pay records for that particular Pay Period.
March 16, 2015 at 2:05 pm
Forgot Quote, see next post.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 16, 2015 at 2:06 pm
shezi (3/16/2015)
ScottPletcher (3/16/2015)
...
ORDER BY CASE WHEN pr.hours > 0 THEN 1 ELSE 2 END, Name
Excellent.
One final Question. The Employee table also has status. Active or Terminated. I want to show all the Employees "Terminated" and "Active" for a particular pay period, BUT don't want to show "Terminated" ones that don't have pay records. The reason for this is the employee table has thousands of terminated employees. it doesn't make sense to show them, only when they have a pay record during a pay period ID.
Here is what I got so far.
SELECT isnull(pr.hours,0) AS Hours, isnull(pr.rate_type,'NULL') as rate_type,
e.last_name + ',' + e.first_name As Name,
e.employee_no, isnull(pr.hours,0) AS ValidatedHours, e.department, e.status
FROM Employee e
LEFT JOIN (
SELECT employee_no, rate_type, SUM(hours) AS Hours, SUM(CASE WHEN valid_payroll = 1 THEN Hours ELSE 0 END) AS ValidatedHours
FROM Pay_Records
WHERE pay_period_id = 543
GROUP BY employee_no, rate_type, valid_payroll
) AS pr
ON pr.employee_no = e.employee_no
Where e.status='A' and e.pay_type=1
ORDER BY CASE WHEN pr.hours > 0 THEN 1 ELSE 2 END, Name
This query will show all "Active" ones. I want to show Terminated ones as well that has Pay records for that particular Pay Period.
...
Where (e.status='A' or pr.Hours > 0) and e.pay_type=1
...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 16, 2015 at 3:54 pm
...
Where (e.status='A' or pr.Hours > 0) and e.pay_type=1
...[/quote]
I came across another snag. 🙁 There is also another column "Validated hour" which is just a sum of hours (Pay record hours) when valid_payrol is 1 (true).
"valid_payrol" is a boolean field set in Pay records table.
After running the query, I get Sum of hours and Sum of validated hours identical. It should differentiate at times.
HEre is my final code.
SELECT isnull(pr.hours,0) AS Hours, isnull(pr.rate_type,'NULL') as rate_type,
e.last_name + ',' + e.first_name As Name,
e.employee_no, isnull(pr.hours,0) AS ValidatedHours, e.department, e.status
FROM Employee e
LEFT JOIN (
SELECT employee_no, rate_type, SUM(hours) AS Hours, SUM(CASE WHEN valid_payroll = 1 THEN Hours ELSE 0 END) AS ValidatedHours
FROM Pay_Records
WHERE pay_period_id = 544
GROUP BY employee_no, rate_type
) AS pr
ON pr.employee_no = e.employee_no
Where (e.Status='A' or pr.Hours > 0) AND pay_type=1
ORDER BY CASE WHEN pr.hours > 0 THEN 1 ELSE 2 END, Name
March 16, 2015 at 3:57 pm
shezi (3/16/2015)
...
Where (e.status='A' or pr.Hours > 0) and e.pay_type=1
...
I came across another snag. 🙁 There is also another column "Validated hour" which is just a sum of hours (Pay record hours) when valid_payrol is 1 (true).
"valid_payrol" is a boolean field set in Pay records table.
After running the query, I get Sum of hours and Sum of validated hours identical. It should differentiate at times.
HEre is my final code.
SELECT isnull(pr.hours,0) AS Hours, isnull(pr.rate_type,'NULL') as rate_type,
e.last_name + ',' + e.first_name As Name,
e.employee_no, isnull(pr.hours,0) AS ValidatedHours, e.department, e.status
FROM Employee e
LEFT JOIN (
SELECT employee_no, rate_type, SUM(hours) AS Hours, SUM(CASE WHEN valid_payroll = 1 THEN Hours ELSE 0 END) AS ValidatedHours
FROM Pay_Records
WHERE pay_period_id = 544
GROUP BY employee_no, rate_type
) AS pr
ON pr.employee_no = e.employee_no
Where (e.Status='A' or pr.Hours > 0) AND pay_type=1
ORDER BY CASE WHEN pr.hours > 0 THEN 1 ELSE 2 END, Name
[/quote]
SELECT isnull(pr.hours,0) AS Hours, isnull(pr.rate_type,'NULL') as rate_type,
e.last_name + ',' + e.first_name As Name,
e.employee_no,
isnull(pr.NonValidatedHours,0) AS NonValidatedHours,
isnull(pr.ValidatedHours,0) AS ValidatedHours,
e.department, e.status
FROM Employee e
LEFT JOIN (
SELECT employee_no, rate_type,
SUM(CASE WHEN valid_payroll = 1 THEN 0 ELSE Hours END) AS NonValidatedHours,
SUM(CASE WHEN valid_payroll = 1 THEN Hours ELSE 0 END) AS ValidatedHours
FROM Pay_Records
WHERE pay_period_id = 544
GROUP BY employee_no, rate_type
) AS pr
ON pr.employee_no = e.employee_no
Where (e.Status='A' or pr.Hours > 0) AND pay_type=1
ORDER BY CASE WHEN pr.ValidatedHours > 0 THEN 1 ELSE 2 END, Name
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 16, 2015 at 4:35 pm
Thank you so much for taking out the time. Everything works perfectly!
Thanks again!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply