Query Help

[JustABob]

Sorry for the vague subject line, but I dont know the terms to describe this problem. As I've begun trying to use this data for reporting, I've been running into a few 'issues' as the users did not go for a standardized format. Anyways, I am working on a report and having trouble getting exactly the data I want to come across.

First a glimpse of the data I am working with:
Master Job, Sub Job, Contract_Amount, Billed_JTD, Total_Contract
1001, 1001, 12,000,000, 3,450,000, 12,000,000

2113, 2113, 4,500,000, 0, 0
2113, 2113-02, 750,000, 0, 750,000

2117, 2117, 0, 0, 0
2117, 2117-03, 250,000, 0, 0
2117, 2117-07, 9,250,000, 0, 0

Basically, there are cases where there is just 1 row, other times the job is split into subjobs. Lastly, there can be the case where master = subjob does not hold the main contract amount. In the case of 2113, there is a pre- construction deal to prep the site before the actual contract is signed.

Now, for my report there is a set of criteria that causes the job to appear in 1 of 2 views. First view is Current work. Here I Sum up the columns like Contract Amount, Billed etc and determine if they should show up with the following Having Clause

Having
Sum(Total_Contract) > 1,000,000 and (SUM(Total_Contract) – SUM(Billed_JTD)) > 100

As you can see, job 1001 would show up on this project, which is perfect!

Next up, Future work:
This view is almost identical, except that we want to show

Having
Sum(Total_Contract) > 1,000,000 and (SUM(Total_Contract) – SUM(Billed_JTD)) > 100 AND Sum(Total_Contract) = 0

Here job 2117 would show up which is correct. As you may see, Job 2113 is the problem as it does not fit either set. Its total contract is < 1 million, so its not on Current, and its total contract <> 0 so it is not on the Future.

This job should actually be show on the future report. I would like to use the Having criteria not on the SUM but just on the row with the largest Contract Amount. How would I craft a query to grab this info job by job and return the summed info only if the max contract row meets the criteria?

Thanks!

 

[JustABob]

Is this one a lost cause?

 

[vongrunt]

First question is about this:

Sum(Total_Contract) > 1,000,000 and (SUM(Total_Contract) – SUM(Billed_JTD)) > 100 AND Sum(Total_Contract) = 0

How can sum be equal zero and greater than 1M?

Second question is: where Job 2113 should appear?

------
"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 about that, I should have had:
Sum(Contract_Amount) > 1,000,000 and (SUM(Contract_Amount) – SUM(Billed_JTD)) > 100 AND Sum(Total_Contract) = 0

#2,
I'd like this job to pull up under future. If the query could just consider the line with the largest contract amount when checking Total_Contract = 0, then this would work perfectly.

Here is the full query for the future jobs if that helps.

SELECT distinct
s.Company_Code, s.Master_Job, i.Job_Description
,Sum(s.Contract_Amount) 'Contract_Amount'
,Sum(s.Billed_JTD) 'JTD_Billed'
,(Sum(s.Contract_Amount) - Sum(s.Billed_JTD)) 'Backlog'
,i.est_start_date as 'Complete Date'

FROM Data_Snapshot as s
INNER JOIN All_Job_Info as i on i.Master_Job = s.Master_Job and i.master_job = i.job_Number
WHERE i.Status_code = 'A' AND s.Company_Code = @Company
GROUP BY
s.Company_Code, s.Master_Job, i.Job_Description, i.est_start_date
HAVING
SUM(s.Contract_Amount) > 1000000 and (Sum(s.Contract_Amount) - Sum(s.Billed_JTD)) > 100 and
SUM(s.Total_Contract_Amount) = 0
ORDER BY s.Company_Code, s.Master_Job

 

[vongrunt]

> If the query could just consider the line with the largest contract amount when checking Total_Contract = 0, then this would work perfectly.

So can you use MAX(Contract_Amount) in HAVING clause?

------
"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]

Hmmm, I wasnt having much luck adding the max(contract_Amount) to the having clause. It kept erroring with:
Line 16: Incorrect syntax near ')'.

I havent used the having clause much before, is it pretty straight forward, or am I missing something?

 

[vongrunt]

Post query here...

------
"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)

 

[SQLBill]

If you can't post the query here, run it again and when you get the error double click on the message and it will highlight the line where it believes the error exists. Look at that line and the ones around it for typos, missing parenthesises, etc.

-SQLBill

Posting advice: FAQ481-4875

 

[JustABob]

I figured out what was wrong, I simply had 'and MAX(Contract_Amount)' and didnt put and clause on there like > 1000. The query will now run and looks like:

SELECT distinct
s.Company_Code, s.Master_Job, i.Job_Description
,Sum(s.Contract_Amount) 'Contract_Amount'
,Sum(s.Billed_JTD) 'JTD_Billed'
,(Sum(s.Contract_Amount) - Sum(s.Billed_JTD)) 'Backlog'
,i.est_start_date as 'Complete Date'

FROM Data_Snapshot as s
INNER JOIN All_Job_Info as i on i.Master_Job = s.Master_Job and i.master_job = i.job_Number
WHERE i.Status_code = 'A' AND s.Company_Code = @Company
GROUP BY
s.Company_Code, s.Master_Job, i.Job_Description, i.est_start_date
HAVING
SUM(s.Contract_Amount) > 1000000 and (Sum(s.Contract_Amount) - Sum(s.Billed_JTD)) > 100 and
SUM(s.Total_Contract_Amount) = 0 and max(s.Contract_Amount) > 10000
ORDER BY s.Company_Code, s.Master_Job

but this isnt quite what I am looking for. I need to check the Total_Contract value in the row that contains the MAX(Contract_Amount). Could this be done with a subquery or something? and would it belong in the where clause or the having?

Thanks again for the help guys!