Complex Nested Selects

[scottgreen]

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:

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

[Ramy Mahrous]

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

[scottgreen]

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