Solved

Trying to grab all employees whether they posted hours or not

Posted on 2012-03-26
6
320 Views
Last Modified: 2012-03-29
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.
0
Comment
Question by:HSI_guelph
  • 4
6 Comments
 
LVL 21

Assisted Solution

by:Jason Yousef, MS
Jason Yousef, MS earned 250 total points
ID: 37767673
Try that..

SELECT     Employee.Empfname + ' ' + WIP.WEmpLName AS Employee, 
WIP.WCltName AS Project, WIP.WSCDesc AS Service, COALESCE(SUM(WIP.Whours),0) AS Hours,
COALESCE(WIP.Wfee, 0) AS Amount, WIP.Wdate AS Date, Department.DeptName AS Team, WIP.WCodeCat AS ServiceCategory
FROM         WIP Left 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

Open in new window

0
 

Author Comment

by:HSI_guelph
ID: 37767725
Thanks but its still not showing employees who haven't posted hours yet (I have a name of an employee who is usually late with submissions to use as an example).  He's in the employee table but he's not showing up in this report (he shows up reports where I specify an earlier date range but he should show up in last week's reports).

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.
0
 
LVL 37

Accepted Solution

by:
ValentinoV earned 250 total points
ID: 37770034
The reason that you're not getting the employees without hours is because the WHERE clause is filtering them out.  It needs to take into account that WIP only applies for employees with hours.

Try this instead:

WHERE (((WIP.Wdate BETWEEN @StartDate AND @EndDate)  
            AND (WIP.WCodeCat IN ('Admin', 'Bill')))
            OR WIP.WempID is null)
      AND Employee.ID > 1
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:HSI_guelph
ID: 37771791
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).
0
 

Author Comment

by:HSI_guelph
ID: 37772376
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?
0
 

Author Closing Comment

by:HSI_guelph
ID: 37782398
Great help, problem lies within the database setup and data.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
convert in derived column 7 30
T-SQL: "HAVING CASE" Clause 1 25
T-SQL:  Collapsing 9 25
SQL - Use results of SELECT DISTINCT in a JOIN 4 20
Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question