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

Recommended Answers

All 2 Replies

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

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.