CODEWITHSUNDEEP
sql
Андрей Тоббо
Андрей Тоббо

Reputation: 3

SQL Multitable query with two sum statements

great sql experts! :) My boss wants month/year/day report of his employee's work. I figured most of SQL querys i need, but the last one is above my comprehension.

I have severas tables: Persons, Services, Plans and ProvidedServices. Table Plans contains inividual month plan for each person on each service. So i need a query that returns total value of services, provided by each person in particular time period on particular service AND summary value of planned work amount in same period on same service. Now i have something like this:

SELECT  SUM(BoRRenderedServices.ServiceCount), 
       BoREmployee.Name, 
       BoRServices.Service,
       SUM(BoRTargets.Amount)
FROM BoRRenderedServices, BoREmployee, BoRServices, BoRTargets
WHERE (BoRTargets.EmployeeID = BoREmployee.ID) 
AND (BoRTargets.ServiceID = BoRServices.ID) 
AND (BoRRenderedServices.Date BETWEEN '2014-1-1' AND '2014-9-19') 
AND (BoREmployee.DepartmentID = 'cc42cac9-5ac7-4614-9b7b-ef931a9a132b')
AND (BoRRenderedServices.EmployeeID = BoREmployee.ID) 
AND (BoRRenderedServices.ServiceID = BoRServices.ID) 
AND (BoRRenderedServices.ServiceID = '0fbf68bf-ace8-4ecb-ba07-7049046c0215') 
GROUP BY BoREmployee.Name, 
       BoRServices.Service

But this query makes double sum, it summarizes amounts of provided services twice, the same is for plans. I mean, each time it founds provided service maching the WHERE conditions - it summarizes plans too, instead of adding only provided service into first sum statement.

Person 1, provided Service1 three times in August 1st , one time in August 3rd, and 4 times in July 4th. The plan for Person1 for August is three, and for July is four.

Desired output:
--------------Plan Fact Service1 1----------1 Service2 1----------2 Service3 1----------3
Actual output:
--------------Plan Fact Service1 3----------3 Service2 3----------6 Service3 3----------9

Upvotes: 0

Views: 100

Answers (1)

Max
Max

Reputation: 7100

I change the query. You can create as view or your sql database.

Check on SqlFiddle

with tbl1 
as
(
  SELECT 
        b.EmployeeId
       ,b.ServiceId
       ,c.Service
       ,d.Name
       ,Sum(b.Amount) Amount
FROM 
    BoRTargets b
        inner join BoRServices c
    on b.ServiceId = c.Id
        inner join BoREmployee d
    on b.EmployeeId = d.Id
WHERE (b.Date BETWEEN '2014-09-01' AND '2014-09-26') 
 AND (d.DepartmentID = 'cc42cac9-5ac7-4614-9b7b-ef931a9a132b')
GROUP BY b.EmployeeId, b.ServiceId, c.Service, d.Name
)
,

tbl2
as
(
  SELECT 
      a.EmployeeId
     ,a.ServiceId
     ,c.Service
     ,d.Name
     ,SUM(a.ServiceCount) ServiceCount
FROM 
    BoRRenderedServices a 
        inner join BoRServices c
    on a.ServiceId = c.Id
        inner join BoREmployee d
    on a.EmployeeId = d.Id
WHERE EXISTS(
    SELECT 1 
    FROM BorTargets b 
     where (a.EmployeeId = b.EmployeeId and a.ServiceId = b.ServiceId)
     AND (b.Date BETWEEN '2014-09-01' AND '2014-09-26'))
 AND (d.DepartmentID = 'cc42cac9-5ac7-4614-9b7b-ef931a9a132b')
GROUP BY a.EmployeeId, a.ServiceId, c.Service, d.Name
)

select coalesce(a.Service, b.Service) Service, coalesce(a.Name, b.Name) Name, a.Amount, b.ServiceCount
from tbl1 a full join tbl2 b
 on a.EmployeeId = b.EmployeeId 
 and a.ServiceId = b.ServiceId

Upvotes: 1

Related Questions