Complex Nested Selects

•

What is DaniWeb IT Discussion Community?

You're currently browsing the MS SQL section within the Web Development category of DaniWeb, a massive community of 456,536 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 3,073 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Please support our MS SQL advertiser: Programming Forums

Thread Tools Display Modes

Views: 1471 | Replies: 2

•

Join Date: Oct 2007

Posts: 2

Reputation: scottgreen is an unknown quantity at this point

Rep Power: 0

Solved Threads: 0

scottgreen scottgreen is offline

Offline

Newbie Poster

Complex Nested Selects

Oct 12th, 2007

Hello, I am trying to write a single query to run a report. I have it working for a single farm, but I need it to work for a list of farms. I am having trouble linking the farm number for the inner query to the farm number for the outer query. Anyone have any ideas on how to make this work. I have racked my brain and cannot make it work.

Basically I need to get the average mortality of the last 6 flocks, and the APC from the current flock out of a table for this report.

Single farm query that works:

Help with Code Tags

(Toggle Plain Text)

select * from 
(select Top 1 t1.farmno, avg(t1.Mort0Pct) * 100 as SixFlockAvgMort0, min((AvgAdjustedPrimeCost2 - AdjustedPrimeCost) * 100) as APCRating from
(select top 6 zst.FarmNo, (cast(zst.MortalityWeek0 as float) / cast(zst.HeadPlaced as float)) as Mort0Pct, zst.settledate
FROM zrstSettle zst
where zst.MinGuaranteeFlockExcludeFlag = 0
AND zst.ApprovedFlag = 1
and zst.farmno = '2073'
order by zst.settledate desc) t1 
Inner join zrstSettle on t1.FarmNo = zrstSettle.FarmNo
where zrstSettle.farmno = '2073' AND zrstSettle.MinGuaranteeFlockExcludeFlag = 0
AND zrstSettle.ApprovedFlag = 1
group by t1.farmno, zrstSettle.settledate 
order by SixFlockAvgMort0 desc) t2
where farmno = '2073'
order by t2.SixFlockAvgMort0 desc

But whenever I replace the hard coded value of '2073' with the farmno from the outer select I get no records because it cannot get the correct values for the TOP specifications in the inner queries. I really need to do this with a single query if possible to plug into the report, otherwise I will have to scrap the report and code all of this. If there is is no other option, then I don't mind writing the code; but using the reporting enigine to do this would be nice.

Any ideas would be greatly appreciated. Thanks in advance for any help.

Scott

•

Join Date: Aug 2006

Location: Egypt

Posts: 763

Reputation: RamyMahrous is on a distinguished road

Rep Power: 4

Solved Threads: 59

RamyMahrous RamyMahrous is offline

Offline

Master Poster

Re: Complex Nested Selects

Oct 14th, 2007

if you used SP, it'd be better, as you could use variables and so on, and also for performance

B.Sc Computer Science, Helwan University
Microsoft Student Partner
Personal blog http://ramymahrous.blogspot.com/
Arabic technical blog http://fci-h-ar.blogspot.com/
English technical blog http://fci-h.blogspot.com/

•

Join Date: Oct 2007

Posts: 2

Reputation: scottgreen is an unknown quantity at this point

Rep Power: 0

Solved Threads: 0

scottgreen scottgreen is offline

Offline

Newbie Poster

Re: Complex Nested Selects

Oct 15th, 2007

•

Originally Posted by RamyMahrous

if you used SP, it'd be better, as you could use variables and so on, and also for performance

Thanks RamyMahrous. Unfortunately the reporting engine only works with a SQL statement. I went ahead and just coded the report. It was fairly easy to manipulate in code. I appreciate the reply.

Scott