We help IT Professionals succeed at work.
Get Started

Query not returning second row for almost identical time entry

151 Views
Last Modified: 2015-03-03
I have a report that displays the hours submitted by employees for the selected team during a given time period and breaks the hours down by service codes so I can group and total by billable and non-billable.  We noticed that the numbers were not adding up and what seemed to happen is that the employee entered two half-hour entries under the same code for the same day but the query only returned one of the rows.  I am not restricting the code by a DISTINCT so I don't understand why it would ignore the second row.  I included the PK ID and the missing entry showed up.  The code is a mess pulling from multiple tables but even if there are two or more rows with the identical entries it should not omit one unless I specified it to be unique or DISTINCT (?).  Adding the PK has resolve the issue but now I worry that other reports might also be affected if SSRS is just omitting rows.  I can't see anything in the code that would specify this behavior so I am assuming it is something with SSRS, a odd behavior I don't understand and have to do a workaround to avoid it.

Any insight into why this has happened so I can eliminate the potential for future errors would be greatly appreciated!!

SELECT     EmpCustom.EmpCustValue AS Team, Employee.Emplevel AS Level, Employee.Empfname + ' ' + WIP.WEmpLName AS Employee, WIP.WCltName AS Project, 
                      WIP.WCodeCat AS ServiceCategory, WIP.WCodeSub AS SubCategory, WIP.WCodeSer AS Service, ISNULL(WIP.Whours, 0) AS Hours, ISNULL(WIP.Wfee, 0) AS Amount,
                       ISNULL(WIP.Wdate, 0) AS Date, Department.DeptName AS TeamDept, EmpCustom.EmpCustValue AS TeamColour, WIP.ID
FROM         WIP RIGHT OUTER JOIN
                      Employee ON WIP.WempID = Employee.ID INNER JOIN
                      Department ON Employee.Empdept = Department.DeptID INNER JOIN
                      EmpCustom ON Employee.ID = EmpCustom.EmpCustEmpId
WHERE     (WIP.Wdate >= @StartDate) AND (Employee.ID > 1) AND (EmpCustom.EmpCustValue = @TeamColour) AND (WIP.Windicator <> 'D') AND (WIP.Wdate <= @StartDate + 6)
GROUP BY EmpCustom.EmpCustValue, Employee.Emplevel, Employee.Empfname, WIP.WEmpLName, WIP.WCltName, ISNULL(WIP.Wdate, 0), WIP.Whours, ISNULL(WIP.Wfee,
                       0), Department.DeptName, WIP.WCodeCat, WIP.WCodeSer, WIP.WCodeSub, WIP.ID
ORDER BY TeamColour, Level, Employee, ServiceCategory, Date DESC

Open in new window


Comparison of results before and after adding the PK to results
Comment
Watch Question
EE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013
Commented:
This problem has been solved!
Unlock 2 Answers and 5 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE