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.