user3490622 February 2016

Oracle SQL - inconsistent query results

I am trying to write an Oracle SQL query that selects a certain date range based on an employeeID (different range for every employeeID), then sums over specific performance metric numbers for each employee for that date range. I seem to be getting inconsistent results for the same employeeID depending on how I select it. Here's my query:

select c.employeeID, b.assessmentDate, c.startDate, c.endDate,
sum(case when employeeProductivityMetric='15' then 1 else 0 end) as Metric1,
sum(case when employeeProductivityMetric='20' then 1 else 0 end) as Metric2
from assessmentTable b 
inner join performanceMetricTable c on b.badgeID = c.badgeID
where b.assessmentDate between c.startDate and c.endDate
group by c.employeeID, b.assessmentDate, c.startDate, c.endDate
order by c.employeeID, b.assessmentDate;

Note that a given employeeID can be associated with more than one badgeID.

When I select one specific employeeID (say, 2) by adding the following to the where clause:

where c.employeeID=2

I get some particular numbers for Metric1 and Metric2:

employeeID  assessmentDate  startDate   endDate    Metric1  Metric2
2           02-Jul-15       01-Jul-15   31-Jul-15  4        5

however, when I do

where c.employeeID between 1 and 3

I get different numbers for employee 2, something like:

employeeID  assessmentDate   startDate  endDate    Metric1  Metric2
2           02-Jul-15        01-Jul-15  31-Jul-15  3        0

Does anyone know why that would be the case? Is there something wrong with my query design?

Thank you for any pointers!

Natalia

Answers


Mehdi El Fadil February 2016

You may debug this type of situations by retrieving the detailed records:

Try to compare the following queries

-- matched records for employeeID=2
select c.employeeID, c.badgeID, b.assessmentDate, c.startDate, c.endDate,
employeeProductivityMetric
from assessmentTable b 
inner join performanceMetricTable c on b.badgeID = c.badgeID
where b.assessmentDate between c.startDate and c.endDate
      and c.employeeID=2
order by c.employeeID, c.badgeID, b.assessmentDate;

and

-- matched records for employeeID between 1 and 3
select c.employeeID, c.badgeID, b.assessmentDate, c.startDate, c.endDate,
employeeProductivityMetric
from assessmentTable b 
inner join performanceMetricTable c on b.badgeID = c.badgeID
where b.assessmentDate between c.startDate and c.endDate
      and c.employeeID between 1 and 3
order by c.employeeID, c.badgeID, b.assessmentDate

Most probably, you'll notice a difference in the badgeIds returned by each query.

Post Status

Asked in February 2016
Viewed 2,544 times
Voted 5
Answered 1 times

Search




Leave an answer