Query not returning second row for almost identical time entry

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
                      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
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
When comparing datetime, or even dates as far as I'm concerned, use >= and < the next day, rather than <= the actual end value.

Code below assume you've already stripped the time off ...:

WHERE     (WIP.Wdate >= @StartDate) AND ...
    (WIP.Wdate < @StartDate + 7)

... if not, strip it off as part of the WHERE:

WHERE     (WIP.Wdate >= DATEADD(DAY, DATEDIFF(DAY, 0, @startdate), 0)) AND ...
    (WIP.Wdate < DATEADD(DAY, DATEDIFF(DAY, 0, @startdate), 0) + 7)
HSI_guelphAuthor Commented:
I haven't stripped the time off, time of day is the same for every entry, would this affect the duplicate row (when no id is included) not showing up?  I actually had a BETWEEN there but changed it up today because I remember an article on EE that spoke about how it was better to avoid using BETWEENs.
PortletPaulEE Topic AdvisorCommented:
>>"I am not restricting the code by a DISTINCT"

Not true, you are just using GROUP BY to achieve the exact same impact.
(i.e. You could discard the group by and use distinct instead and the result would be identical.)

So; including that additional column has stopped the GROUP BY (or DISTINCT) from evaluating the row as a duplicate.

Regarding BETWEEN, yes do not use that for date ranges, As Scott recommends use >= with < instead.

While I am here you are not gaining anything by using that right outer join because the where clause contains this:
     AND (Employee.ID > 1)

that predicate discards any unmatched rows from WIP and you get the same effect as an INNER JOIN
(can be known as "implied inner join" or "implicit inner join")

So use INNER JOIN --<< my guess is this is what you should do
allow NULLS
      AND (Employee.ID > 1 OR Employee.ID IS NULL)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HSI_guelphAuthor Commented:
The Employee.ID > 1 is because expenses are entered under Employee ID 1 (admin).  
I think at some point I was grouping the hours but I don't see a sum so I must have changed that.  When I removed the Group By I now get 38 results instead of 37.  

Thank you both for the assist in cleaning up my code.
HSI_guelphAuthor Commented:
Removing the Group By returned the correct number of results.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.