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.


10 Years
Discussion Span
Last Post by 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.


This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.