Oracle SQL - inconsistent query results -

i trying write oracle sql query selects date range based on employeeid (different range every employeeid), sums on specific performance metric numbers each employee date range. seem getting inconsistent results same employeeid depending on how select it. here's query:

select c.employeeid, b.assessmentdate, c.startdate, c.enddate, sum(case when employeeproductivitymetric='15' 1 else 0 end) metric1, sum(case when employeeproductivitymetric='20' 1 else 0 end) metric2 assessmenttable b  inner join performancemetrictable c on b.badgeid = c.badgeid b.assessmentdate between c.startdate , c.enddate group c.employeeid, b.assessmentdate, c.startdate, c.enddate order c.employeeid, b.assessmentdate; 

note given employeeid can associated more 1 badgeid.

when select 1 specific employeeid (say, 2) adding following clause:

where c.employeeid=2 

i particular numbers metric1 , metric2:

employeeid  assessmentdate  startdate   enddate    metric1  metric2 2           02-jul-15       01-jul-15   31-jul-15  4        5 

however, when do

where c.employeeid between 1 , 3 

i different numbers employee 2, like:

employeeid  assessmentdate   startdate  enddate    metric1  metric2 2           02-jul-15        01-jul-15  31-jul-15  3        0 

does know why case? there wrong query design?

thank pointers!

natalia

you may debug type of situations retrieving detailed records:

try compare following queries

-- matched records employeeid=2 select c.employeeid, c.badgeid, b.assessmentdate, c.startdate, c.enddate, employeeproductivitymetric assessmenttable b  inner join performancemetrictable c on b.badgeid = c.badgeid b.assessmentdate between c.startdate , c.enddate       , c.employeeid=2 order c.employeeid, c.badgeid, b.assessmentdate; 

and

-- matched records employeeid between 1 , 3 select c.employeeid, c.badgeid, b.assessmentdate, c.startdate, c.enddate, employeeproductivitymetric assessmenttable b  inner join performancemetrictable c on b.badgeid = c.badgeid b.assessmentdate between c.startdate , c.enddate       , c.employeeid between 1 , 3 order c.employeeid, c.badgeid, b.assessmentdate 

most probably, you'll notice difference in badgeids returned each query.