question about sums.

[JustABob]

I am having some difficulties with this query and was wondering if anyone know what was wrong. The data I am working with can be setup 2 ways -

Master job only - all data on one line, perfect!

Sub Jobs and master job -
Data can be in the MJ line and the SJ line. I need to roll up all data into a single line.

First off, I added the SUM in the Where clause knowing it would fail - I'd like the query to only ignore jobs whos total contract amount is less then 300k and not skip a sub job that is smaller then 300k.

If that SUM is removed, the code sorta works - The GAP, billed_jtd and Contract sums will total just fine. The problem is that when I try and use a formula the result is close, but not quite right. Is there a way to correct this, or do some sort of subquery to get these totals and then preform the computations on the result? This will be placed into a stored procedure if that helps.

SUM(a.billed_jtd)/ SUM(a.contract_amount) * 100 'percent' -- problem line.

Thanks!

select distinct
a.company_code,
a.master_job,
b.job_description,
b.Project_Manager,
sum(a.contract_Amount) 'Contract_Amount',
cast(sum(a.billed_jtd)/ sum(a.contract_amount) * 100 as int) 'percent',
sum(a.GAP) 'GAP'

FROM Metadata a
INNER JOIN job_info b ON a.master_job = b.master_job AND a.master_job = b.job_Number
WHERE
a.Master_Job >= 2000 AND SUM(a.Contract_Amount) > 300000
AND a.Company_Code <>'004'
GROUP BY a.company_code, a.master_job, b.job_description, b.Project_Manager

 

[vongrunt]

Leave a.Master_Job >= 200 as is, and move SUM(a.Contrat_Amount) > 300000 into HAVING clause. And remove DISTINCT - GROUP BY already does that.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[JustABob]

I gave that a shot, and it still had the numbers off by a little bit. Thanks for the tip about the having though!

 

[vongrunt]

Define "off by a little bit". Last decimal off here and there, or something more noticeable?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[JustABob]

Sorry, I was refering to a differnt calculation I was working with. The main number I am trying to calculate is called GAP, I just used % for simplicity eariler. This is the code pre rollup that worked fine.
GAP =
CASE
WHEN Projected_Cost = '0' THEN 0
WHEN Actual_Cost >= Projected_Cost
THEN Contract_Amount - Actual_Cost - Gross_Profit_Recognized
ELSE ROUND((Actual_Cost / Projected_Cost) * Contract_Amount, 1) - Actual_Cost - Gross_Profit_Recognized
END

In the new version I just tack Sum(....) around every parameter and when I check this gap against another master job that does not have any subjobs the numbers are different. - even through there is nothing to sum up since all the data is on a single row.

Now I also returned the sum of each of the parameters,
sum(projected_cost) as PC, sum(actual_cost) as AC, etc and preform the formula by hand and get the correct number. It is just when preforming it throgh the code that its wrong.

and by off by a little bit, I mean that the numbers are different by up to 8 on a few 100k for the ones that I tested.

 

[vongrunt]

My first guess is about either ROUND() or accumulated data loss during division (/) to fixed number of decimals... or even both.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[JustABob]

alright, thanks for the help!