HSI_guelph
asked on
Trying to grab all employees whether they posted hours or not
Could someone please help me with my query here:
SELECT Employee.Empfname + ' ' + WIP.WEmpLName AS Employee, WIP.WCltName AS Project, WIP.WSCDesc AS Service, SUM(WIP.Whours) AS Hours,
WIP.Wfee AS Amount, WIP.Wdate AS Date, Department.DeptName AS Team, WIP.WCodeCat AS ServiceCategory
FROM WIP RIGHT OUTER JOIN
Employee ON WIP.WempID = Employee.ID INNER JOIN
Department ON Employee.Empdept = Department.DeptID
WHERE (WIP.Wdate BETWEEN @StartDate AND @EndDate) AND (WIP.WCodeCat IN ('Admin', 'Bill')) AND (Employee.ID > 1)
GROUP BY Employee.Empfname, WIP.WEmpLName, WIP.WCltName, WIP.WSCDesc, WIP.Wdate, WIP.Wfee, Department.DeptName, WIP.WCodeCat
ORDER BY Date, Employee, ServiceCategory
This returns the results I'm looking for except for any employee who hasn't posted their hours. So there might not be a record in the WIP table for each employee but I still want that employee to show up with 0 hours and 0 fee/pay. Does it have something to do with the WIP.WempID = Employee.ID in the FROM clause? We're sending this out tomorrow and would like people to be able to see when they haven't entered any hours yet.
SELECT Employee.Empfname + ' ' + WIP.WEmpLName AS Employee, WIP.WCltName AS Project, WIP.WSCDesc AS Service, SUM(WIP.Whours) AS Hours,
WIP.Wfee AS Amount, WIP.Wdate AS Date, Department.DeptName AS Team, WIP.WCodeCat AS ServiceCategory
FROM WIP RIGHT OUTER JOIN
Employee ON WIP.WempID = Employee.ID INNER JOIN
Department ON Employee.Empdept = Department.DeptID
WHERE (WIP.Wdate BETWEEN @StartDate AND @EndDate) AND (WIP.WCodeCat IN ('Admin', 'Bill')) AND (Employee.ID > 1)
GROUP BY Employee.Empfname, WIP.WEmpLName, WIP.WCltName, WIP.WSCDesc, WIP.Wdate, WIP.Wfee, Department.DeptName, WIP.WCodeCat
ORDER BY Date, Employee, ServiceCategory
This returns the results I'm looking for except for any employee who hasn't posted their hours. So there might not be a record in the WIP table for each employee but I still want that employee to show up with 0 hours and 0 fee/pay. Does it have something to do with the WIP.WempID = Employee.ID in the FROM clause? We're sending this out tomorrow and would like people to be able to see when they haven't entered any hours yet.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks now I can see ppl with 0 hours and null fee/date/etc, but I'm still not seeing one specific employee who doesn't enter his hours on time. He shows up in the employee table so he's there and active.
Plus your query keeps getting rearranged to
WHERE (WIP.Wdate BETWEEN @StartDate AND @EndDate) AND (Employee.ID > 1) AND (WIP.WCodeCat IN ('Admin', 'Bill')) OR
(Employee.ID > 1) AND (WIP.WempID IS NULL)
I'm thinking this might be the problem and I'm annoyed with the query designer for changing things like this without my permission. Is it due to the order of my Select and From statements?
SELECT Employee.Empfname + ' ' + Employee.Emplname AS Employee, WIP.WCltName AS Project, WIP.WCodeSer AS ServiceCode, ServiceCodes.SCDesc AS Service,
Department.DeptName AS Team, SUM(ISNULL(WIP.Whours, 0)) AS Hours, WIP.Wfee AS Fee, CONVERT(varchar, WIP.Wdate, 101) AS Date
FROM ServiceCodes INNER JOIN
WIP ON ServiceCodes.ID = WIP.WCodeID RIGHT OUTER JOIN
Employee INNER JOIN
Department ON Employee.Empdept = Department.DeptID ON WIP.WempID = Employee.ID
WHERE (WIP.Wdate BETWEEN @StartDate AND @EndDate) AND (Employee.ID > 1) AND (WIP.WCodeCat IN ('Admin', 'Bill')) OR
(Employee.ID > 1) AND (WIP.WempID IS NULL)
GROUP BY Department.DeptName, Employee.Empfname, Employee.Emplname, WIP.WCltName, WIP.WCodeSer, ServiceCodes.SCDesc, WIP.Wfee, WIP.Wdate
ORDER BY Team, Employee, Date
Also if I do something like remove Team from the order by (so I can get alphabetical employees to find the specific one I'm looking for) then the 0 hours null fee/date/etc no longer shows!
I can remove the Sums and do that in the report if that would help then I don't need a Group By clause. I could probably do this easily in a stored procedure but neither I nor my supervisor have created stored procedures before (I've written them before but the Web Master always put them into the database).
Plus your query keeps getting rearranged to
WHERE (WIP.Wdate BETWEEN @StartDate AND @EndDate) AND (Employee.ID > 1) AND (WIP.WCodeCat IN ('Admin', 'Bill')) OR
(Employee.ID > 1) AND (WIP.WempID IS NULL)
I'm thinking this might be the problem and I'm annoyed with the query designer for changing things like this without my permission. Is it due to the order of my Select and From statements?
SELECT Employee.Empfname + ' ' + Employee.Emplname AS Employee, WIP.WCltName AS Project, WIP.WCodeSer AS ServiceCode, ServiceCodes.SCDesc AS Service,
Department.DeptName AS Team, SUM(ISNULL(WIP.Whours, 0)) AS Hours, WIP.Wfee AS Fee, CONVERT(varchar, WIP.Wdate, 101) AS Date
FROM ServiceCodes INNER JOIN
WIP ON ServiceCodes.ID = WIP.WCodeID RIGHT OUTER JOIN
Employee INNER JOIN
Department ON Employee.Empdept = Department.DeptID ON WIP.WempID = Employee.ID
WHERE (WIP.Wdate BETWEEN @StartDate AND @EndDate) AND (Employee.ID > 1) AND (WIP.WCodeCat IN ('Admin', 'Bill')) OR
(Employee.ID > 1) AND (WIP.WempID IS NULL)
GROUP BY Department.DeptName, Employee.Empfname, Employee.Emplname, WIP.WCltName, WIP.WCodeSer, ServiceCodes.SCDesc, WIP.Wfee, WIP.Wdate
ORDER BY Team, Employee, Date
Also if I do something like remove Team from the order by (so I can get alphabetical employees to find the specific one I'm looking for) then the 0 hours null fee/date/etc no longer shows!
I can remove the Sums and do that in the report if that would help then I don't need a Group By clause. I could probably do this easily in a stored procedure but neither I nor my supervisor have created stored procedures before (I've written them before but the Web Master always put them into the database).
ASKER
SELECT Employee.Empdept AS Team, Employee.Empfname + ' ' + Employee.Emplname AS Employee, Employee.Empstatus, Employee.ID
FROM Employee INNER JOIN
WIP ON Employee.ID = WIP.ID
WHERE (Employee.ID > 1) OR
(WIP.WempID IS NULL)
ORDER BY Employee
Gets me all employees but now if I try to grab data from the WIP table will the query return each row in WIP with the employee data? (ie I'll see multiple instances of the same employee for each service/hours they entered?)
OH!!!! It turns out that WIP.ID is equal to Employee.ID (which it really shouldn't be since that is the primary key in WIP but I can't do anything about that). Perhaps that is where I'm getting the wrong results from?
FROM Employee INNER JOIN
WIP ON Employee.ID = WIP.ID
WHERE (Employee.ID > 1) OR
(WIP.WempID IS NULL)
ORDER BY Employee
Gets me all employees but now if I try to grab data from the WIP table will the query return each row in WIP with the employee data? (ie I'll see multiple instances of the same employee for each service/hours they entered?)
OH!!!! It turns out that WIP.ID is equal to Employee.ID (which it really shouldn't be since that is the primary key in WIP but I can't do anything about that). Perhaps that is where I'm getting the wrong results from?
ASKER
Great help, problem lies within the database setup and data.
ASKER
Should I nest Selects? I don't need SUMs in the query since I can do that on the report and removing it showed employees multiple times (for each row in WIP they have) but still no luck with late submission employees.
Here is code plummet posted to an earlier question:
Select
Employee,
Project,
Service,
Team,
HoursPTD,
AmountPTD,
HoursYTD,
AmountYTD,
WCodeSub,
Wdate
from
(SELECT
Employee.Empfname + ' ' + Employee.Emplname AS Employee,
WIP.WCltName AS Project,
WIP.WCodeSer as ServiceCode,
ServiceCodes.SCDesc AS Service,
Department.DeptName AS Team,
SUM(WIP.Whours) AS HoursYTD,
WIP.Wfee AS AmountYTD, -- Should this be a SUM?
WIP.WCodeSub,
WIP.Wdate -- Is this going to stop the YTD total working? If the Wdate holds different dates then it will prevent YTD
FROM ServiceCodes
INNER JOIN WIP
ON ServiceCodes.ID = WIP.WCodeID
RIGHT OUTER JOIN Employee
ON WIP.WempID = Employee.ID
INNER JOIN Department
ON Employee.Empdept = Department.DeptID
WHERE WIP.WempID > 1
AND Employee.Empstatus = 'A'
AND WIP.WCodeSer IN ('PE1', 'PE2', 'PE3', 'PE4', '01', '02', '03', '04', '05')
AND WIP.Wpdate > (SELECT DATEADD(day, - 365, (SELECT MAX(Wpdate) AS Expr1 FROM WIP AS WIP_1)) AS [-365 Days])
GROUP BY
Department.DeptName,
Employee.Empfname,
Employee.Emplname,
WIP.WCltName,
WIP.WCodeSer,
ServiceCodes.SCDesc,
WIP.Wfee,
WIP.WCodeSub,
WIP.Wdate -- Note as above
--ORDER BY
-- Team,
-- Employee
) YTD
left join
(SELECT
Employee.Empfname + ' ' + Employee.Emplname AS Employee,
WIP.WCltName AS Project,
WIP.WCodeSer AS ServiceCode,
ServiceCodes.SCDesc AS Service,
Department.DeptName AS Team,
SUM(WIP.Whours) AS HoursPTD,
WIP.Wfee AS FeePTD, -- Should this be a SUM?
CONVERT(varchar, WIP.Wdate, 101) AS Date,
WIP.WempID,
WIP.ID,
WIP.WCltID
FROM ServiceCodes
INNER JOIN WIP
ON ServiceCodes.ID = WIP.WCodeID
RIGHT OUTER JOIN Employee
ON WIP.WempID = Employee.ID
INNER JOIN Department
ON Employee.Empdept = Department.DeptID
WHERE WIP.WempID > 1
AND Employee.Empstatus = 'A'
AND WIP.WCodeSer IN ('PE1', 'PE2', 'PE3', 'PE4', '01', '02', '03', '04', '05')
AND WIP.Wdate BETWEEN @StartDate AND @EndDate
GROUP BY
Department.DeptName,
Employee.Empfname,
Employee.Emplname,
WIP.WCltName,
WIP.WCodeSer,
ServiceCodes.SCDesc,
WIP.Wfee,
WIP.Wdate,
WIP.WempID,
WIP.ID,
WIP.WCltID
--ORDER BY Team, Employee, Date
) PTD
on PTD.Employee = YTD.Employee
and PTD.Project = YTD.Project
and PTD.ServiceCode = YTD.ServiceCode
and PTD.Service = YTD.Service
It was to help get both Pay-to-date and Year-to-date data from within one query.