Skip to content
Advertisement

Sort Records comparing sums across multiple tables

In SQL Server, I am wanting to bring back all jobs where

(
  SUM(Order.InvoicesReceived) > (SUM(Estimate.GrossValue) + SUM (AdditionalEstimate.GrossValue))
) OR ( 
    SUM(Order.ContractGiven) > (SUM(Estimate.GrossValue) + SUM (AdditionalEstimate.GrossValue))
)

CREATE TABLE Job (id INT, userid INT)
INSERT INTO Job ( id ,userid)VALUES  ( 1,1)    
INSERT INTO Job ( id ,userid)VALUES  ( 2,1)    
INSERT INTO Job ( id ,userid)VALUES  ( 3,2)  
INSERT INTO Job ( id ,userid)VALUES  ( 4,2)  
INSERT INTO Job ( id ,userid)VALUES  ( 5,1)  

CREATE TABLE [User] (id INT, UserName NVARCHAR (30))
INSERT INTO [User] ( id ,UserName)VALUES  ( 1,'Richard')
INSERT INTO [User] ( id ,UserName)VALUES  ( 2,'Jane')    

CREATE Table Estimate (id INT, [job] INT, [GrossValue] DECIMAL (18,2))
INSERT INTO Estimate ( id ,[job], GrossValue)VALUES  ( 1,3, 100)  
INSERT INTO Estimate ( id ,[job], GrossValue)VALUES  ( 2,4, 100)  
INSERT INTO Estimate ( id ,[job], GrossValue)VALUES  ( 3,5, 200)  
INSERT INTO Estimate ( id ,[job], GrossValue)VALUES  ( 4,5, 200)  

CREATE Table AdditionalEstimate (id INT, [job] INT, [GrossValue] DECIMAL (18,2))  
INSERT INTO AdditionalEstimate ( id ,[job], GrossValue)VALUES  ( 1,1, 100)  
INSERT INTO AdditionalEstimate ( id ,[job], GrossValue)VALUES  ( 2,2, 100)  
INSERT INTO AdditionalEstimate ( id ,[job], GrossValue)VALUES  ( 3,5, 100)  
INSERT INTO AdditionalEstimate ( id ,[job], GrossValue)VALUES  ( 4,5, 100)

CREATE Table [Order] (id INT, [job] INT, ContractGiven DECIMAL (18,2), InvoicesReceived DECIMAL (18,2))
INSERT INTO [Order] ( id ,[job], ContractGiven,InvoicesReceived)VALUES  ( 1,1, 50, 0)  
INSERT INTO [Order] ( id ,[job], ContractGiven,InvoicesReceived)VALUES  ( 2,2, 150, 0)  
INSERT INTO [Order] ( id ,[job], ContractGiven,InvoicesReceived)VALUES  ( 3,3, 50, 0)  
INSERT INTO [Order] ( id ,[job], ContractGiven,InvoicesReceived)VALUES  ( 4,4, 150, 0)  
INSERT INTO [Order] ( id ,[job], ContractGiven,InvoicesReceived)VALUES  ( 5,5, 400, 0)  
INSERT INTO [Order] ( id ,[job], ContractGiven,InvoicesReceived)VALUES  ( 6,5, 100, 0)  

To make it easy to see what results I should get I added the following table and updated the inserts into tables.

JOB Estimate AddEstimate Order  
1   Null     100         50    
2   Null     100         150  
3   100      NULL        50  
4   100      NULL        150  
5   200      100         500  
5   200      100         NA  

InvoicedRecieved is ignored for simplicity. Jobs 2,4 Should be returned.

Richard 1, Jane 1

I also need another statement bring back the number of jobs per user that met the above criteria.

Advertisement

Answer

Gordon was correct about the need to sum before comparing, due to the Cartesian product. However as his query isn’t producing the correct result here is the query which does.

select J.id [Job]
  , coalesce(O.InvoicesReceived,0) InvoicesReceived
  , coalesce(O.ContractGiven,0) ContractGiven
  , coalesce(E.GrossValue,0) + coalesce(A.GrossValue,0) TotalQuoteCost
from Job J
left join (select Job, SUM(O.InvoicesReceived) InvoicesReceived, SUM(O.ContractGiven) ContractGiven from [Order] O group by Job) O on O.Job = J.id
left join (select Job, SUM(E.GrossValue) GrossValue from Estimate E group by Job) E on E.Job = J.id
left join (select Job, SUM(A.GrossValue) GrossValue from AdditionalEstimate A group by Job) A on A.Job = J.id
where (
  coalesce(O.InvoicesReceived,0) > (coalesce(E.GrossValue,0) + coalesce(A.GrossValue,0))
) OR ( 
  coalesce(O.ContractGiven,0) > (coalesce(E.GrossValue,0) + coalesce(A.GrossValue,0))
)

select [User Name], count(*)
from (
  select U.UserName [User Name], J.id
    , coalesce(O.InvoicesReceived,0) InvoicesReceived
    , coalesce(O.ContractGiven,0) ContractGiven
    , coalesce(E.GrossValue,0) + coalesce(A.GrossValue,0) TotalQuoteCost
  from Job J
  inner join [User] U on U.id = J.UserId
  left join (select Job, SUM(O.InvoicesReceived) InvoicesReceived, SUM(O.ContractGiven) ContractGiven from [Order] O group by Job) O on O.Job = J.id
  left join (select Job, SUM(E.GrossValue) GrossValue from Estimate E group by Job) E on E.Job = J.id
  left join (select Job, SUM(A.GrossValue) GrossValue from AdditionalEstimate A group by Job) A on A.Job = J.id
  where (
    coalesce(O.InvoicesReceived,0) > (coalesce(E.GrossValue,0) + coalesce(A.GrossValue,0))
  ) OR ( 
    coalesce(O.ContractGiven,0) > (coalesce(E.GrossValue,0) + coalesce(A.GrossValue,0))
  )
) x
group by [User Name]

DB Fiddle

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement