I apologize if this is the wrong forum for this kind of thing.
I have inherited the following stored procedure (of some 1000 lines) and
would like to rationalize, tune, optimize or just completely re-architect
it.
Some thoughts or guidance would be appreciated.
What I'm looking for is suggestions like... Chop bits like blah, blah into
smaller procs. and stuff like that.
Cheers, Simon.
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
CREATE procedure [dbo].[spNmlFMS]
as
set nocount on
set ansi_warnings off
set arithabort off
set arithignore on
declare @.PrevPeriod as tinyint
declare @.PrevPeriod_Month as tinyint
declare @.PrevPeriod_Year as smallint
declare @.PrevPeriod_Season as smallint
declare @.ThisPeriod as tinyint
declare @.ThisPeriod_Month as tinyint
declare @.ThisPeriod_Year as smallint
declare @.NextPeriod as tinyint
declare @.NextPeriod_Month as tinyint
declare @.NextPeriod_Year as smallint
declare @.ThisSeason as smallint
----
----
---
select @.ThisPeriod = (select distinct Period from drvFMS)
select @.ThisSeason = (select distinct Season from drvFMS)
select @.ThisPeriod_Month =
case @.ThisPeriod
when 1 then 6
when 2 then 7
when 3 then 8
when 4 then 9
when 5 then 10
when 6 then 11
when 7 then 12
when 8 then 1
when 9 then 2
when 10 then 3
when 11 then 4
when 12 then 5
end
select @.ThisPeriod_Year =
case @.ThisPeriod
when 1 then (@.ThisSeason - 1)
when 2 then (@.ThisSeason - 1)
when 3 then (@.ThisSeason - 1)
when 4 then (@.ThisSeason - 1)
when 5 then (@.ThisSeason - 1)
when 6 then (@.ThisSeason - 1)
when 7 then (@.ThisSeason - 1)
when 8 then @.ThisSeason
when 9 then @.ThisSeason
when 10 then @.ThisSeason
when 11 then @.ThisSeason
when 12 then @.ThisSeason
end
set @.PrevPeriod = @.ThisPeriod - 1
set @.PrevPeriod_Month = @.ThisPeriod_Month - 1
if @.PrevPeriod < 1
begin
set @.PrevPeriod = 12
end
if @.PrevPeriod_Month < 1
begin
set @.PrevPeriod_Month = 12
end
select @.PrevPeriod_Year =
case @.PrevPeriod
when 1 then (@.ThisSeason - 1)
when 2 then (@.ThisSeason - 1)
when 3 then (@.ThisSeason - 1)
when 4 then (@.ThisSeason - 1)
when 5 then (@.ThisSeason - 1)
when 6 then (@.ThisSeason - 1)
when 7 then (@.ThisSeason - 1)
when 8 then @.ThisSeason
when 9 then @.ThisSeason
when 10 then @.ThisSeason
when 11 then @.ThisSeason
when 12 then (@.ThisSeason - 1)
end
select @.PrevPeriod_Season =
case @.PrevPeriod
when 1 then @.ThisSeason
when 2 then @.ThisSeason
when 3 then @.ThisSeason
when 4 then @.ThisSeason
when 5 then @.ThisSeason
when 6 then @.ThisSeason
when 7 then @.ThisSeason
when 8 then @.ThisSeason
when 9 then @.ThisSeason
when 10 then @.ThisSeason
when 11 then @.ThisSeason
when 12 then (@.ThisSeason - 1)
end
select @.NextPeriod =
case @.ThisPeriod
when 1 then 2
when 2 then 3
when 3 then 4
when 4 then 5
when 5 then 6
when 6 then 7
when 7 then 8
when 8 then 9
when 9 then 10
when 10 then 11
when 11 then 12
when 12 then 1
end
select @.NextPeriod_Month =
case @.NextPeriod
when 1 then 6
when 2 then 7
when 3 then 8
when 4 then 9
when 5 then 10
when 6 then 11
when 7 then 12
when 8 then 1
when 9 then 2
when 10 then 3
when 11 then 4
when 12 then 5
end
----
----
---
/* aMthEndAvgCover, aPastureGrowth */
select drvFMS.FarmId, drvFMS.Season, drvFMS.Period,
tblActualPastureData.Cover as ActualCover, tblForecastPastureData.Cover as
ForecastCover
into #A1
from drvFMS
inner join tblActualPastureData on (tblActualPastureData.FarmId =
drvFMS.FarmId) and (tblActualPastureData.Season = drvFMS.Season) and
(tblActualPastureData.Period = drvFMS.Period) and
(month(tblActualPastureData.[Date]) = @.ThisPeriod_Month) and
(day(tblActualPastureData.[Date]) = 21)
inner join tblForecastPastureData on (tblForecastPastureData.FarmId =
drvFMS.FarmId) and (tblForecastPastureData.Season = drvFMS.Season) and
(tblForecastPastureData.Period = drvFMS.Period) and
(month(tblForecastPastureData.[Date]) = @.ThisPeriod_Month) and
(day(tblForecastPastureData.[Date]) = 21)
select #A1.*, tblForecastPastureData.Growth
into #A2
from #A1
inner join tblForecastPastureData on (tblForecastPastureData.FarmId =
#A1.FarmId) and (tblForecastPastureData.Season = #A1.Season) and
(tblForecastPastureData.Period = #A1.Period) and
(month(tblForecastPastureData.[Date]) = @.ThisPeriod_Month)
select #A2.FarmId, #A2.Season, #A2.Period, #A2.ActualCover,
#A2.ForecastCover, avg(cast(#A2.Growth as decimal(9,3))) as aPastureGrowth
into #A3
from #A2
group by #A2.FarmId, #A2.Season, #A2.Period, #A2.ActualCover,
#A2.ForecastCover
declare @.F as varchar(7)
declare @.S as smallint
declare @.P as tinyint
declare @.ActualCover as smallint
declare @.ForecastCover as smallint
declare @.Cover as smallint
declare @.aPastureGrowth as decimal(9,3)
declare a_Cursor cursor for
select FarmId, Season, Period, ActualCover, ForecastCover, aPastureGrowth
from #A3
open a_Cursor
fetch next from a_Cursor
into @.F, @.S, @.P, @.ActualCover, @.ForecastCover, @.aPastureGrowth
while @.@.fetch_status = 0
begin
if @.ActualCover >= 1000
Set @.Cover = @.ActualCover
else
if @.ForecastCover >= 1000
Set @.Cover = @.ForecastCover
else
Set @.Cover = 0
if exists (select FarmId, Season, Period from nmlFMS where (FarmId = @.F)
and (Season = @.S) and (Period = @.P))
begin
update nmlFMS set aMthEndAvgCover = @.Cover, aPastureGrowth =
@.aPastureGrowth
where (FarmId = @.F) and (Season = @.S) and (Period = @.P)
end
else
begin
insert into nmlFMS (FarmId, Season, Period, aMthEndAvgCover,
aPastureGrowth) values (@.F, @.S, @.P, @.Cover, @.aPastureGrowth)
end
fetch next from a_Cursor
into @.F, @.S, @.P, @.ActualCover, @.ForecastCover, @.aPastureGrowth
end
close a_Cursor
deallocate a_Cursor
----
----
---
/* aAvgCowsMilked, aDmc1, aDmc2, aDmc3, aDmc4, aDmc5, aDmc6, aDmc7, aCrops,
aTotalDMConsumed, dmc1Description, dmc2Description, dmc3Description,
dmc4Description, dmc5Description, dmc6Description, dmc7Description */
select drvFMS.FarmId, drvFMS.Season, drvFMS.Period,
avg(cast(tblActualMilkingCowData.NoOfCows as decimal(9,3))) as
aAvgCowsMilked, avg(cast(tblActualMilkingCowData.dmc1 as decimal(9,3))) as
aDmc1avg, avg(cast(tblActualMilkingCowData.dmc2 as decimal(9,3))) as
aDmc2avg, avg(cast(tblActualMilkingCowData.dmc3 as decimal(9,3))) as
aDmc3avg, avg(cast(tblActualMilkingCowData.dmc4 as decimal(9,3))) as
aDmc4avg, avg(cast(tblActualMilkingCowData.dmc5 as decimal(9,3))) as
aDmc5avg, avg(cast(tblActualMilkingCowData.dmc6 as decimal(9,3))) as
aDmc6avg, avg(cast(tblActualMilkingCowData.dmc7 as decimal(9,3))) as
aDmc7avg, avg(cast(tblActualMilkingCowData.Crop1 as decimal(9,3))) as
aCrop1avg, avg(cast(tblActualMilkingCowData.Crop2 as decimal(9,3))) as
aCrop2avg, avg(cast(tblActualMilkingCowData.Crop3 as decimal(9,3))) as
aCrop3avg, avg(cast(tblActualMilkingCowData.Crop4 as decimal(9,3))) as
aCrop4avg, avg(cast(tblActualMilkingCowData.Crop5 as decimal(9,3))) as
aCrop5avg
into #B1
from tblActualMilkingCowData
inner join drvFMS on (tblActualMilkingCowData.FarmId = drvFMS.FarmId) and
(tblActualMilkingCowData.Season = drvFMS.Season) and
(tblActualMilkingCowData.Period = drvFMS.Period)
where (month(tblActualMilkingCowData.[Date]) = @.ThisPeriod_Month)
group by drvFMS.FarmId, drvFMS.Season, drvFMS.Period
select #B1.FarmId, #B1.Season, #B1.Period, aAvgCowsMilked, aDmc1avg,
aDmc2avg, aDmc3avg, aDmc4avg, aDmc5avg, aDmc6avg, aDmc7avg, (aCrop1avg +
aCrop2avg + aCrop3avg + aCrop4avg + aCrop5avg) as aCrops, (round(aDmc1avg,1)
+ round(aDmc2avg,1) + round(aDmc3avg,1) + round(aDmc4avg,1) +
round(aDmc5avg,1) + round(aDmc6avg,1) + round(aDmc7avg,1) +
round(aCrop1avg,1) + round(aCrop2avg,1) + round(aCrop3avg,1) +
round(aCrop4avg,1) + round(aCrop5avg,1)) as aTotalDMConsumed
into #B2
from #B1
select #B2.*, vwFarmAdditionalInfo.dmc1Description,
vwFarmAdditionalInfo.dmc2Description, vwFarmAdditionalInfo.dmc3Description,
vwFarmAdditionalInfo.dmc4Description, vwFarmAdditionalInfo.dmc5Description,
vwFarmAdditionalInfo.dmc6Description, vwFarmAdditionalInfo.dmc7Description
into #B3
from #B2
inner join vwFarmAdditionalInfo
on (vwFarmAdditionalInfo.FarmId = #B2.FarmId)
declare @.aAvgCowsMilked as smallint
declare @.aDmc1 as decimal(9,3)
declare @.aDmc2 as decimal(9,3)
declare @.aDmc3 as decimal(9,3)
declare @.aDmc4 as decimal(9,3)
declare @.aDmc5 as decimal(9,3)
declare @.aDmc6 as decimal(9,3)
declare @.aDmc7 as decimal(9,3)
declare @.aCrops as decimal(9,3)
declare @.aTotalDMConsumed as decimal(9,3)
declare @.dmc1Desc as varchar(80)
declare @.dmc2Desc as varchar(80)
declare @.dmc3Desc as varchar(80)
declare @.dmc4Desc as varchar(80)
declare @.dmc5Desc as varchar(80)
declare @.dmc6Desc as varchar(80)
declare @.dmc7Desc as varchar(80)
declare a_Cursor cursor for
select * from #B3
open a_Cursor
fetch next from a_Cursor
into @.F, @.S, @.P, @.aAvgCowsMilked, @.aDmc1, @.aDmc2, @.aDmc3, @.aDmc4, @.aDmc5,
@.aDmc6, @.aDmc7, @.aCrops, @.aTotalDMConsumed, @.dmc1Desc, @.dmc2Desc, @.dmc3Desc,
@.dmc4Desc, @.dmc5Desc, @.dmc6Desc, @.dmc7Desc
while @.@.fetch_status = 0
begin
update nmlFMS set aAvgCowsMilked = @.aAvgCowsMilked, aDmc1 = @.aDmc1, aDmc2 =
@.aDmc2, aDmc3 = @.aDmc3, aDmc4 = @.aDmc4, aDmc5 = @.aDmc5, aDmc6 = @.aDmc6,
aDmc7 = @.aDmc7, aCrops = @.aCrops, aTotalDMConsumed = @.aTotalDMConsumed,
lblDmc1 = @.dmc1Desc, lblDmc2 = @.dmc2Desc, lblDmc3 = @.dmc3Desc, lblDmc4 =
@.dmc4Desc, lblDmc5 = @.dmc5Desc, lblDmc6 = @.dmc6Desc, lblDmc7 = @.dmc7Desc
where (FarmId = @.F) and (Season = @.S) and (Period = @.P)
fetch next from a_Cursor
into @.F, @.S, @.P, @.aAvgCowsMilked, @.aDmc1, @.aDmc2, @.aDmc3, @.aDmc4, @.aDmc5,
@.aDmc6, @.aDmc7, @.aCrops, @.aTotalDMConsumed, @.dmc1Desc, @.dmc2Desc, @.dmc3Desc,
@.dmc4Desc, @.dmc5Desc, @.dmc6Desc, @.dmc7Desc
end
close a_Cursor
deallocate a_Cursor
----
----
---
/* aSeasonToDateMS, aMonthTotalMS */
select drvFMS.FarmId, drvFMS.Season, drvFMS.Period,
month(tblActualProductionData.[Date]) as [Month],
(tblActualProductionData.Fat + tblActualProductionData.Protein) as
MilkSolids
into #C1
from tblActualProductionData
inner join drvFMS on (tblActualProductionData.FarmId = drvFMS.FarmId) and
(tblActualProductionData.Season = drvFMS.Season) and
(tblActualProductionData.Period = drvFMS.Period)
select #C1.FarmId, #C1.Season, #C1.Period, sum(#C1.MilkSolids) as
aMonthTotalMS
into #C2
from #C1
where (#C1.[Month] = @.ThisPeriod_Month)
group by FarmId, Season, Period
select #C1.FarmId, #C1.Season, #C1.Period, sum(#C1.MilkSolids) as
aSeasonToDateMS
into #C3
from #C1
where (#C1.[Month] <= @.ThisPeriod_Month)
group by FarmId, Season, Period
select #C2.FarmId, #C2.Season, #C2.Period, #C2.aMonthTotalMS,
#C3.aSeasonToDateMS
into #C4
from #C2
inner join #C3 on (#C3.FarmId = #C2.FarmId)
declare @.aMonthTotalMS as decimal(9,3)
declare @.aSeasonToDateMS as decimal(9,3)
declare a_Cursor cursor for
select * from #C4
open a_Cursor
fetch next from a_Cursor
into @.F, @.S, @.P, @.aMonthTotalMS, @.aSeasonToDateMS
while @.@.fetch_status = 0
begin
update nmlFMS set aMonthTotalMS = @.aMonthTotalMS, aSeasonToDateMS =
@.aSeasonToDateMS
where (FarmId = @.F) and (Season = @.S) and (Period = @.P)
fetch next from a_Cursor
into @.F, @.S, @.P, @.aMonthTotalMS, @.aSeasonToDateMS
end
close a_Cursor
deallocate a_Cursor
----
----
---
/* aFeedConvEff, [aFeedAlloc/LiveWeight], [aMS/Cow/Day], [aMthMS/Ha] */
select #B3.FarmId, #B3.Season, #B3.Period, #B3.aTotalDMConsumed,
sum(tblActualProductionData.Days) as [Days]
into #D1
from #B3
inner join tblActualProductionData on (tblActualProductionData.FarmId =
#B3.FarmId) and (tblActualProductionData.Season = #B3.Season) and
(tblActualProductionData.Period = #B3.Period) and
(month(tblActualProductionData.[Date]) = @.ThisPeriod_Month)
group by #B3.FarmId, #B3.Season, #B3.Period, #B3.aTotalDMConsumed
select #D1.FarmId, #D1.Season, #D1.Period, #D1.aTotalDMConsumed, #D1.[Days],
avg(cast(tblActualPastureData.FarmSize as decimal(9,3))) as aAverageArea
into #D2
from #D1
inner join tblActualPastureData on (tblActualPastureData.FarmId =
#D1.FarmId) and (tblActualPastureData.Season = #D1.Season) and
(tblActualPastureData.Period = #D1.Period) and
(month(tblActualPastureData.[Date]) = @.ThisPeriod_Month)
group by #D1.FarmId, #D1.Season, #D1.Period, #D1.aTotalDMConsumed,
#D1.[Days]
select #D2.*, #B1.aAvgCowsMilked, #C4.aMonthTotalMS,
tblActualFarmData.FarmSize as 'aAvailableArea',
tblFarmAdditionalInfo.Liveweight
into #D3
from #D2
inner join #B1 on (#B1.FarmId = #D2.FarmId) and (#B1.Season = #D2.Season)
and (#B1.Period = #D2.Period)
inner join #C4 on (#C4.FarmId = #D2.FarmId) and (#C4.Season = #D2.Season)
and (#C4.Period = #D2.Period)
inner join tblActualFarmData on (tblActualFarmData.FarmId = #D2.FarmId) and
(tblActualFarmData.Season = #D2.Season) and (tblActualFarmData.Period =
#D2.Period)
inner join tblFarmAdditionalInfo on (tblFarmAdditionalInfo.FarmId =
#D2.FarmId)
select #D3.FarmId, #D3.Season, #D3.Period, ((#D3.aTotalDMConsumed * #D3.Days
* (#D3.aAvgCowsMilked / #D3.aAverageArea)) / (#D3.aMonthTotalMS /
#D3.aAvailableArea)) as aFeedConvEff, (#D3.aTotalDMConsumed / #D3.Liveweight
* 100) as [aFeedAlloc/LiveWeight], (#D3.aMonthTotalMS / #D3.aAvgCowsMilked /
#D3.Days) as [aMS/Cow/Day], (#D3.aMonthTotalMS / #D3.aAvailableArea) as
[aMthMS/Ha]
into #D4
from #D3
declare @.aFeedConvEff as decimal(9,3)
declare @.aFeedAllocLiveWeight as decimal(9,3)
declare @.aMSCowDay as decimal (9,3)
declare @.aMthMSHa as decimal(9,3)
declare a_Cursor cursor for
select * from #D4
open a_Cursor
fetch next from a_Cursor
into @.F, @.S, @.P, @.aFeedConvEff, @.aFeedAllocLiveWeight, @.aMSCowDay, @.aMthMSHa
while @.@.fetch_status = 0
begin
update nmlFMS set aFeedConvEff = cast(@.aFeedConvEff as decimal(6,4)),
[aFeedAlloc/LiveWeight] = cast(@.aFeedAllocLiveWeight as decimal(6,4)),
[aMS/Cow/Day] = cast(@.aMSCowDay as decimal(6,4)), [aMthMS/Ha] =
cast(@.aMthMSHa as decimal(9,3))
where (FarmId = @.F) and (Season = @.S) and (Period = @.P)
fetch next from a_Cursor
into @.F, @.S, @.P, @.aFeedConvEff, @.aFeedAllocLiveWeight, @.aMSCowDay,
@.aMthMSHa
end
close a_Cursor
deallocate a_Cursor
----
----
---
/* [aMthFeedCosts/KgMS], rSeasonMS, rSeasonSuppUsed, rSeasonFeedCosts,
rSeasonGrossMargin */
select #D1.FarmId, #D1.Season, #D1.Period, #D1.Days,
cast(avg(tblActualMilkingCowData.NoOfCows) as smallint) as aAvgCowsMilked,
avg(tblActualMilkingCowData.dmc2 * tblFarmAdditionalInfo.dmc2$) as aDmc2$,
avg(tblActualMilkingCowData.dmc3 * tblFarmAdditionalInfo.dmc3$) as aDmc3$,
avg(tblActualMilkingCowData.dmc4 * tblFarmAdditionalInfo.dmc4$) as aDmc4$,
avg(tblActualMilkingCowData.dmc5 * tblFarmAdditionalInfo.dmc5$) as aDmc5$,
avg(tblActualMilkingCowData.dmc6 * tblFarmAdditionalInfo.dmc6$) as aDmc6$,
avg(tblActualMilkingCowData.dmc7 * tblFarmAdditionalInfo.dmc7$) as aDmc7$,
avg(tblActualMilkingCowData.Crop1 * tblFarmAdditionalInfo.Crop1$) as
aCrop1$, avg(tblActualMilkingCowData.Crop2 * tblFarmAdditionalInfo.Crop2$)
as aCrop2$, avg(tblActualMilkingCowData.Crop3 *
tblFarmAdditionalInfo.Crop3$) as aCrop3$, avg(tblActualMilkingCowData.Crop4
* tblFarmAdditionalInfo.Crop4$) as aCrop4$,
avg(tblActualMilkingCowData.Crop5 * tblFarmAdditionalInfo.Crop5$) as aCrop5$
into #E1
from #D1
inner join tblActualMilkingCowData on (tblActualMilkingCowData.FarmId =
#D1.FarmId) and (tblActualMilkingCowData.Season = #D1.Season) and
(tblActualMilkingCowData.Period = #D1.Period) and
(month(tblActualMilkingCowData.[Date]) = @.ThisPeriod_Month)
inner join tblFarmAdditionalInfo on (tblFarmAdditionalInfo.FarmId =
#D1.FarmId)
group by #D1.FarmId, #D1.Season, #D1.Period, #D1.Days
create index C2_ndx on #C2 (FarmId, Season, Period)
select #E1.FarmId, #E1.Season, #E1.Period, #E1.Days, aAvgCowsMilked,
#C2.aMonthTotalMS, (aDmc2$ + aDmc3$ + aDmc4$ + aDmc5$ + aDmc6$ + aDmc7$ +
aCrop1$ + aCrop2$ + aCrop3$ + aCrop4$ + aCrop5$) as aMthFeedCosts,
tblActualFarmData.Adjistment$ as aGrazingCosts$, (tblForecastFarmData.Fat +
tblForecastFarmData.Protein) as rSeasonMS, (tblForecastFarmData.IOFC$ -
tblForecastFarmData.CowCosts$ - tblForecastFarmData.GrossMargin$) as
aIrrigationCosts$, (tblForecastFarmData.dmc2fed +
tblForecastFarmData.dmc3fed + tblForecastFarmData.dmc4fed +
tblForecastFarmData.dmc5fed + tblForecastFarmData.dmc6fed +
tblForecastFarmData.dmc7fed) as rSeasonSuppUsed,
(tblForecastFarmData.Concentrates$ + tblForecastFarmData.Fodder$ +
tblForecastFarmData.Nitrogen$ + tblForecastFarmData.Adjistment$ +
tblForecastFarmData.Crop1$ + tblForecastFarmData.Crop2$ +
tblForecastFarmData.Crop3$ + tblForecastFarmData.Crop4$ +
tblForecastFarmData.Crop5$) as rSeasonFeedCosts,
cast(tblForecastFarmData.GrossMargin$ as decimal(11,3)) as
rSeasonGrossMargin
into #E2
from #E1
inner join #C2 on (#C2.FarmId = #E1.FarmId) and (#C2.Season = #E1.Season)
and (#C2.Period = #E1.Period)
inner join tblActualFarmData on (tblActualFarmData.FarmId = #E1.FarmId) and
(tblActualFarmData.Season = #E1.Season) and (tblActualFarmData.Period =
#E1.Period)
inner join tblForecastFarmData on (tblForecastFarmData.FarmId = #E1.FarmId)
and (tblForecastFarmData.Season = #E1.Season) and
(tblForecastFarmData.Period = #E1.Period)
select #E2.*, (((#E2.aMthFeedCosts * #E2.aAvgCowsMilked * #E2.Days) /
#E2.aMonthTotalMS) + (#E2.aGrazingCosts$ / #E2.rSeasonMS) +
(#E2.aIrrigationCosts$ / #E2.rSeasonMS)) as aMthFeedCostsKgMS
into #E3
from #E2
declare @.rSeasonMS as integer
declare @.aMthFeedCostsKgMS as decimal(9,4)
declare @.rSeasonSuppUsed as decimal(6,2)
declare @.rSeasonFeedCosts as decimal(8,2)
declare @.rSeasonGrossMargin as decimal(11,3)
declare a_Cursor cursor for
select FarmId, Season, Period, rSeasonMS, aMthFeedCostsKgMS,
rSeasonSuppUsed, rSeasonFeedCosts, rSeasonGrossMargin from #E3
open a_Cursor
fetch next from a_Cursor
into @.F, @.S, @.P, @.rSeasonMS, @.aMthFeedCostsKgMS, @.rSeasonSuppUsed,
@.rSeasonFeedCosts, @.rSeasonGrossMargin
while @.@.fetch_status = 0
begin
update nmlFMS set rSeasonMS = @.rSeasonMS, [aMthFeedCosts/KgMS] =
@.aMthFeedCostsKgMS, rSeasonSuppUsed = @.rSeasonSuppUsed, rSeasonFeedCosts =
@.rSeasonFeedCosts, rSeasonGrossMargin = @.rSeasonGrossMargin
where (FarmId = @.F) and (Season = @.S) and (Period = @.P)
fetch next from a_Cursor
into @.F, @.S, @.P, @.rSeasonMS, @.aMthFeedCostsKgMS, @.rSeasonSuppUsed,
@.rSeasonFeedCosts, @.rSeasonGrossMargin
end
close a_Cursor
deallocate a_Cursor
----
----
---
/* tSeasonMS, tSeasonSuppUsed, tSeasonFeedCosts, tSeasonGrossMargin */
select drvFMS.FarmId, drvFMS.Season, drvFMS.Period,
tblOriginalFarmData.Adjistment$ as oGrazingCosts$, (tblOriginalFarmData.Fat
+ tblOriginalFarmData.Protein) as oSeasonMS, (tblOriginalFarmData.IOFC$ -
tblOriginalFarmData.CowCosts$ - tblOriginalFarmData.GrossMargin$) as
oIrrigationCosts$, (tblOriginalFarmData.dmc2fed +
tblOriginalFarmData.dmc3fed + tblOriginalFarmData.dmc4fed +
tblOriginalFarmData.dmc5fed + tblOriginalFarmData.dmc6fed +
tblOriginalFarmData.dmc7fed) as oSeasonSuppUsed,
(tblOriginalFarmData.Concentrates$ + tblOriginalFarmData.Fodder$ +
tblOriginalFarmData.Nitrogen$ + tblOriginalFarmData.Crop1$ +
tblOriginalFarmData.Crop2$ + tblOriginalFarmData.Crop3$ +
tblOriginalFarmData.Crop4$ + tblOriginalFarmData.Crop5$) as
oSeasonFeedCosts, (cast(tblOriginalFarmData.GrossMargin$ as decimal(11,3)) -
(cast(tblOriginalFarmData.IOFC$ as decimal(11,3)) -
cast(tblOriginalFarmData.CowCosts$ as decimal(11,3)) -
cast(tblOriginalFarmData.GrossMargin$ as decimal(11,3)))) as
oSeasonGrossMargin
into #F1
from drvFMS
inner join tblOriginalFarmData on (tblOriginalFarmData.FarmId =
drvFMS.FarmId) and (tblOriginalFarmData.Season = drvFMS.Season)
select #F1.FarmId, #F1.Season, #F1.Period, #F1.oSeasonMS as tSeasonMS,
#F1.oSeasonSuppUsed as tSeasonSuppUsed, (#F1.oSeasonFeedCosts +
#F1.oGrazingCosts$ + #F1.oIrrigationCosts$) as tSeasonFeedCosts,
#F1.oSeasonGrossMargin as tSeasonGrossMargin
into #F2
from #F1
declare @.tSeasonMS as integer
declare @.tSeasonSuppUsed as decimal(6,2)
declare @.tSeasonFeedCosts as decimal(8,2)
declare @.tSeasonGrossMargin as decimal(11,3)
declare a_Cursor cursor for
select FarmId, Season, Period, tSeasonMS, tSeasonSuppUsed, tSeasonFeedCosts,
tSeasonGrossMargin from #F2
open a_Cursor
fetch next from a_Cursor
into @.F, @.S, @.P, @.tSeasonMS, @.tSeasonSuppUsed, @.tSeasonFeedCosts,
@.tSeasonGrossMargin
while @.@.fetch_status = 0
begin
update nmlFMS set tSeasonMS = @.tSeasonMS, tSeasonSuppUsed =
@.tSeasonSuppUsed, tSeasonFeedCosts = @.tSeasonFeedCosts, tSeasonGrossMargin =
@.tSeasonGrossMargin
where (FarmId = @.F) and (Season = @.S) and (Period = @.P)
fetch next from a_Cursor
into @.F, @.S, @.P, @.tSeasonMS, @.tSeasonSuppUsed, @.tSeasonFeedCosts,
@.tSeasonGrossMargin
end
close a_Cursor
deallocate a_Cursor
----
----
---
/* tMthEndAvgCover, tPastureGrowth */
select drvFMS.FarmId, drvFMS.Season, drvFMS.Period,
tblForecastPastureData.Cover as tMthEndAvgCover
into #G1
from drvFMS
inner join tblForecastPastureData on (tblForecastPastureData.FarmId =
drvFMS.FarmId) and (tblForecastPastureData.Season = drvFMS.Season) and
(tblForecastPastureData.Period = @.PrevPeriod) and
(month(tblForecastPastureData.[Date]) = @.ThisPeriod_Month) and
(day(tblForecastPastureData.[Date]) = 21)
select #G1.*, tblForecastPastureData.Growth
into #G2
from #G1
inner join tblForecastPastureData on (tblForecastPastureData.FarmId =
#G1.FarmId) and (tblForecastPastureData.Season = #G1.Season) and
(tblForecastPastureData.Period = @.PrevPeriod) and
(month(tblForecastPastureData.[Date]) = @.ThisPeriod_Month)
select #G2.FarmId, #G2.Season, #G2.Period, #G2.tMthEndAvgCover,
avg(cast(#G2.Growth as decimal(9,3))) as tPastureGrowth
into #G3
from #G2
group by #G2.FarmId, #G2.Season, #G2.Period, #G2.tMthEndAvgCover
declare @.tMthEndAvgCover as smallint
declare @.tPastureGrowth as decimal(9,3)
declare a_Cursor cursor for
select FarmId, Season, Period, tMthEndAvgCover, tPastureGrowth from #G3
open a_Cursor
fetch next from a_Cursor
into @.F, @.S, @.P, @.tMthEndAvgCover, @.tPastureGrowth
while @.@.fetch_status = 0
begin
update nmlFMS set tMthEndAvgCover = @.tMthEndAvgCover, tPastureGrowth =
@.tPastureGrowth
where (FarmId = @.F) and (Season = @.S) and (Period = @.P)
fetch next from a_Cursor
into @.F, @.S, @.P, @.tMthEndAvgCover, @.tPastureGrowth
end
close a_Cursor
deallocate a_Cursor
----
----
---
/* tAvgCowsMilked, tDmc1, tDmc2, tDmc3, tDmc4, tDmc5, tDmc6, tDmc7, tCrops,
tTotalDMConsumed */
select drvFMS.FarmId, drvFMS.Season, drvFMS.Period,
avg(cast(tblForecastMilkingCowData.NoOfCows as decimal(9,3))) as
tAvgCowsMilked, avg(cast(tblForecastMilkingCowData.dmc1 as decimal(9,3))) as
tDmc1avg, avg(cast(tblForecastMilkingCowData.dmc2 as decimal(9,3))) as
tDmc2avg, avg(cast(tblForecastMilkingCowData.dmc3 as decimal(9,3))) as
tDmc3avg, avg(cast(tblForecastMilkingCowData.dmc4 as decimal(9,3))) as
tDmc4avg, avg(cast(tblForecastMilkingCowData.dmc5 as decimal(9,3))) as
tDmc5avg, avg(cast(tblForecastMilkingCowData.dmc6 as decimal(9,3))) as
tDmc6avg, avg(cast(tblForecastMilkingCowData.dmc7 as decimal(9,3))) as
tDmc7avg, avg(cast(tblForecastMilkingCowData.Crop1 as decimal(9,3))) as
tCrop1avg, avg(cast(tblForecastMilkingCowData.Crop2 as decimal(9,3))) as
tCrop2avg, avg(cast(tblForecastMilkingCowData.Crop3 as decimal(9,3))) as
tCrop3avg, avg(cast(tblForecastMilkingCowData.Crop4 as decimal(9,3))) as
tCrop4avg, avg(cast(tblForecastMilkingCowData.Crop5 as decimal(9,3))) as
tCrop5avg
into #H1
from tblForecastMilkingCowData
inner join drvFMS on (tblForecastMilkingCowData.FarmId = drvFMS.FarmId) and
(tblForecastMilkingCowData.Season = drvFMS.Season) and
(tblForecastMilkingCowData.Period = @.PrevPeriod)
where (month(tblForecastMilkingCowData.[Date]) = @.ThisPeriod_Month)
group by drvFMS.FarmId, drvFMS.Season, drvFMS.Period
select #H1.*, (tCrop1avg + tCrop2avg + tCrop3avg + tCrop4avg + tCrop5avg) as
tCrops, (round(tDmc1avg,1) + round(tDmc2avg,1) + round(tDmc3avg,1) +
round(tDmc4avg,1) + round(tDmc5avg,1) + round(tDmc6avg,1) +
round(tDmc7avg,1) + round(tCrop1avg,1) + round(tCrop2avg,1) +
round(tCrop3avg,1) + round(tCrop4avg,1) + round(tCrop5avg,1)) as
tTotalDMConsumed
into #H2
from #H1
declare @.tAvgCowsMilked as smallint
declare @.tDmc1 as decimal(9,3)
declare @.tDmc2 as decimal(9,3)
declare @.tDmc3 as decimal(9,3)
declare @.tDmc4 as decimal(9,3)
declare @.tDmc5 as decimal(9,3)
declare @.tDmc6 as decimal(9,3)
declare @.tDmc7 as decimal(9,3)
declare @.tCrops as decimal(9,3)
declare @.tTotalDMConsumed as decimal(9,3)
declare a_Cursor cursor for
select FarmId, Season, Period, tAvgCowsMilked, tDmc1avg, tDmc2avg, tDmc3avg,
tDmc4avg, tDmc5avg, tDmc6avg, tDmc7avg, tCrops, tTotalDMConsumed from #H2
open a_Cursor
fetch next from a_Cursor
into @.F, @.S, @.P, @.tAvgCowsMilked, @.tDmc1, @.tDmc2, @.tDmc3, @.tDmc4, @.tDmc5,
@.tDmc6, @.tDmc7, @.tCrops, @.tTotalDMConsumed
while @.@.fetch_status = 0
begin
update nmlFMS set tAvgCowsMilked = @.tAvgCowsMilked, tDmc1 = @.tDmc1, tDmc2 =
@.tDmc2, tDmc3 = @.tDmc3, tDmc4 = @.tDmc4, tDmc5 = @.tDmc5, tDmc6 = @.tDmc6,
tDmc7 = @.tDmc7, tCrops = @.tCrops, tTotalDMConsumed = @.tTotalDMConsumed
where (FarmId = @.F) and (Season = @.S) and (Period = @.P)
fetch next from a_Cursor
into @.F, @.S, @.P, @.tAvgCowsMilked, @.tDmc1, @.tDmc2, @.tDmc3, @.tDmc4, @.tDmc5,
@.tDmc6, @.tDmc7, @.tCrops, @.tTotalDMConsumed
end
close a_Cursor
deallocate a_Cursor
----
----
---
/* tSeasonToDateMS, tMonthTotalMS, [tMS/Cow/Day] */
select drvFMS.FarmId, drvFMS.Season, drvFMS.Period,
month(tblForecastProductionData.[Date]) as [Month],
(tblForecastProductionData.Fat + tblForecastProductionData.Protein) as
MilkSolids, tblForecastProductionData.Days
into #I1
from tblForecastProductionData
inner join drvFMS on (tblForecastProductionData.FarmId = drvFMS.FarmId) and
(tblForecastProductionData.Season = drvFMS.Season) and
(tblForecastProductionData.Period = @.PrevPeriod)
select #I1.FarmId, #I1.Season, #I1.Period, sum(#I1.MilkSolids) as
tMonthTotalMS, sum(#I1.Days) as [Days]
into #I2
from #I1
where (#I1.[Month] = @.ThisPeriod_Month)
group by FarmId, Season, Period
create index I2_ndx on #I2 (FarmId, Season, Period)
create index H2_ndx on #H2 (FarmId, Season, Period)
select #I1.*, dbo.fnMonthToPeriod([Month]) as MonthPeriod,
#I2.tMonthTotalMS, cast((#I2.tMonthTotalMS / #H2.tAvgCowsMilked / #I2.Days)
as decimal(6,4)) as [tMS/Cow/Day]
into #I3
from #I1
inner join #I2 on #I2.FarmId = #I1.FarmId and #I2.Season = #I1.Season and
#I2.Period = #I1.Period
inner join #H2 on #H2.FarmId = #I1.FarmId and #H2.Season = #I1.Season and
#H2.Period = #I1.Period
select #I3.FarmId, #I3.Season, #I3.Period, #I3.tMonthTotalMS, [tMS/Cow/Day],
sum(#I3.MilkSolids) as tSeasonToDateMS
into #I4
from #I3
where #I3.MonthPeriod <= #I3.Period
group by FarmId, Season, Period, tMonthTotalMS, [tMS/Cow/Day]
declare @.tMonthTotalMS as decimal(9,3)
declare @.tSeasonToDateMS as decimal(9,3)
declare @.tMSCowDay as decimal(6,4)
declare a_Cursor cursor for
select * from #I4
open a_Cursor
fetch next from a_Cursor
into @.F, @.S, @.P, @.tMonthTotalMS, @.tMSCowDay, @.tSeasonToDateMS
while @.@.fetch_status = 0
begin
update nmlFMS set tMonthTotalMS = @.tMonthTotalMS, [tMS/Cow/Day] =
@.tMSCowDay, tSeasonToDateMS = @.tSeasonToDateMS
where (FarmId = @.F) and (Season = @.S) and (Period = @.P)
fetch next from a_Cursor
into @.F, @.S, @.P, @.tMonthTotalMS, @.tMSCowDay, @.tSeasonToDateMS
end
close a_Cursor
deallocate a_Cursor
----
----
---
/* tFeedConvEff, [tFeedAlloc/LiveWeight], [tMthMS/Ha] */
select #H2.FarmId, #H2.Season, #H2.Period, #H2.tTotalDMConsumed,
sum(tblForecastProductionData.Days) as [Days]
into #J1
from #H2
inner join tblForecastProductionData on (tblForecastProductionData.FarmId =
#H2.FarmId) and (tblForecastProductionData.Season = #H2.Season) and
(tblForecastProductionData.Period = @.PrevPeriod) and
(month(tblForecastProductionData.[Date]) = @.ThisPeriod_Month)
group by #H2.FarmId, #H2.Season, #H2.Period, #H2.tTotalDMConsumed
select #J1.FarmId, #J1.Season, #J1.Period, #J1.tTotalDMConsumed, #J1.Days,
avg(cast(tblForecastPastureData.FarmSize as decimal(9,3))) as tAverageArea
into #J2
from #J1
inner join tblForecastPastureData on (tblForecastPastureData.FarmId =
#J1.FarmId) and (tblForecastPastureData.Season = #J1.Season) and
(tblForecastPastureData.Period = @.PrevPeriod) and
(month(tblForecastPastureData.[Date]) = @.ThisPeriod_Month)
group by #J1.FarmId, #J1.Season, #J1.Period, #J1.tTotalDMConsumed,
#J1.[Days]
select #J2.*, #H2.tAvgCowsMilked, #I4.tMonthTotalMS,
tblForecastFarmData.FarmSize as 'tAvailableArea',
tblFarmAdditionalInfo.Liveweight
into #J3
from #J2
inner join #H2 on (#H2.FarmId = #J2.FarmId) and (#H2.Season = #J2.Season)
and (#H2.Period = #J2.Period)
inner join #I4 on (#I4.FarmId = #J2.FarmId) and (#I4.Season = #J2.Season)
and (#I4.Period = #J2.Period)
inner join tblForecastFarmData on (tblForecastFarmData.FarmId = #J2.FarmId)
and (tblForecastFarmData.Season = #J2.Season) and
(tblForecastFarmData.Period = #J2.Period)
inner join tblFarmAdditionalInfo on (tblFarmAdditionalInfo.FarmId =
#J2.FarmId)
select #J3.FarmId, #J3.Season, #J3.Period, ((#J3.tTotalDMConsumed * #J3.Days
* (#J3.tAvgCowsMilked / #J3.tAverageArea)) / (#J3.tMonthTotalMS /
#J3.tAvailableArea)) as tFeedConvEff, (#J3.tTotalDMConsumed / #J3.Liveweight
* 100) as [tFeedAlloc/LiveWeight], (#J3.tMonthTotalMS / #J3.tAvailableArea)
as [tMthMS/Ha]
into #J4
from #J3
declare @.tFeedConvEff as decimal(9,3)
declare @.tFeedAllocLiveWeight as decimal(9,3)
declare @.tMthMSHa as decimal(9,3)
declare a_Cursor cursor for
select * from #J4
open a_Cursor
fetch next from a_Cursor
into @.F, @.S, @.P, @.tFeedConvEff, @.tFeedAllocLiveWeight, @.tMthMSHa
while @.@.fetch_status = 0
begin
update nmlFMS set tFeedConvEff = cast(@.tFeedConvEff as decimal(6,4)),
[tFeedAlloc/LiveWeight] = cast(@.tFeedAllocLiveWeight as decimal(6,4)),
[tMthMS/Ha] = @.tMthMSHa
where (FarmId = @.F) and (Season = @.S) and (Period = @.P)
fetch next from a_Cursor
into @.F, @.S, @.P, @.tFeedConvEff, @.tFeedAllocLiveWeight, @.tMthMSHa
end
close a_Cursor
deallocate a_Cursor
----
----
---
/* [tMthFeedCosts/KgMS] */
select #J1.FarmId, #J1.Season, #J1.Period, #J1.Days,
cast(avg(tblForecastMilkingCowData.NoOfCows) as smallint) as tAvgCowsMilked,
avg(tblForecastMilkingCowData.dmc2 * tblFarmAdditionalInfo.dmc2$) as tDmc2$,
avg(tblForecastMilkingCowData.dmc3 * tblFarmAdditionalInfo.dmc3$) as tDmc3$,
avg(tblForecastMilkingCowData.dmc4 * tblFarmAdditionalInfo.dmc4$) as tDmc4$,
avg(tblForecastMilkingCowData.dmc5 * tblFarmAdditionalInfo.dmc5$) as tDmc5$,
avg(tblForecastMilkingCowData.dmc6 * tblFarmAdditionalInfo.dmc6$) as tDmc6$,
avg(tblForecastMilkingCowData.dmc7 * tblFarmAdditionalInfo.dmc7$) as tDmc7$,
avg(tblForecastMilkingCowData.Crop1 * tblFarmAdditionalInfo.Crop1$) as
tCrop1$, avg(tblForecastMilkingCowData.Crop2 * tblFarmAdditionalInfo.Crop2$)
as tCrop2$, avg(tblForecastMilkingCowData.Crop3 *
tblFarmAdditionalInfo.Crop3$) as tCrop3$,
avg(tblForecastMilkingCowData.Crop4 * tblFarmAdditionalInfo.Crop4$) as
tCrop4$, avg(tblForecastMilkingCowData.Crop5 * tblFarmAdditionalInfo.Crop5$)
as tCrop5$
into #K1
from #J1
inner join tblForecastMilkingCowData on (tblForecastMilkingCowData.FarmId =
#J1.FarmId) and (tblForecastMilkingCowData.Season = #J1.Season) and
(tblForecastMilkingCowData.Period = @.PrevPeriod) and
(month(tblForecastMilkingCowData.[Date]) = @.ThisPeriod_Month)
inner join tblFarmAdditionalInfo on (tblFarmAdditionalInfo.FarmId =
#J1.FarmId)
group by #J1.FarmId, #J1.Season, #J1.Period, #J1.Days
select #K1.FarmId, #K1.Season, #K1.Period, #K1.Days, #K1.tAvgCowsMilked,
#I2.tMonthTotalMS, (#K1.tDmc2$ + #K1.tDmc3$ + #K1.tDmc4$ + #K1.tDmc5$ +
#K1.tDmc6$ + #K1.tDmc7$ + #K1.tCrop1$ + #K1.tCrop2$ + #K1.tCrop3$ +
#K1.tCrop4$ + #K1.tCrop5$) as tMthFeedCosts, tblForecastFarmData.Adjistment$
as tGrazingCosts$, (tblForecastFarmData.Fat + tblForecastFarmData.Protein)
as tSeasonMS, (tblForecastFarmData.IOFC$ - tblForecastFarmData.CowCosts$ -
tblForecastFarmData.GrossMargin$) as tIrrigationCosts$,
nmlFMS.rSeasonSuppUsed, nmlFMS.rSeasonFeedCosts
into #K2
from #K1
inner join #I2 on (#I2.FarmId = #K1.FarmId) and (#I2.Season = #K1.Season)
and (#I2.Period = #K1.Period)
inner join tblForecastFarmData on (tblForecastFarmData.FarmId = #K1.FarmId)
and (tblForecastFarmData.Season = #K1.Season) and
(tblForecastFarmData.Period = @.PrevPeriod)
inner join nmlFMS on (nmlFMS.FarmId = #K1.FarmId) and (nmlFMS.Season =
#K1.Season) and (nmlFMS.Period = #K1.Period)
select #K2.*, (((#K2.tMthFeedCosts * #K2.tAvgCowsMilked * #K2.Days) /
#K2.tMonthTotalMS) + (#K2.tGrazingCosts$ / #K2.tSeasonMS) +
(#K2.tIrrigationCosts$ / #K2.tSeasonMS)) as tMthFeedCostsKgMS
into #K3
from #K2
declare @.tMthFeedCostsKgMS as decimal(9,4)
declare a_Cursor cursor for
select FarmId, Season, Period, tMthFeedCostsKgMS from #K3
open a_Cursor
fetch next from a_Cursor
into @.F, @.S, @.P, @.tMthFeedCostsKgMS
while @.@.fetch_status = 0
begin
update nmlFMS set [tMthFeedCosts/KgMS] = @.tMthFeedCostsKgMS
where (FarmId = @.F) and (Season = @.S) and (Period = @.P)
fetch next from a_Cursor
into @.F, @.S, @.P, @.tMthFeedCostsKgMS
end
close a_Cursor
deallocate a_Cursor
----
----
---
/* [tSeasonMS/Ha], [rSeasonMS/Ha], [tSeasonPastureHarvest/Ha],
[rSeasonPastureHarvest/Ha], [tSeasonSuppUsed/Ha], [rSeasonSuppUsed/Ha],
[tSeasonFeedCosts/Ha], [rSeasonFeedCosts/Ha], [tSeasonGrossMargin/Ha],
[rSeasonGrossMargin/Ha] */
select drvFMS.FarmId, drvFMS.Season, drvFMS.Period, (nmlFMS.tSeasonMS /
tblOriginalFarmData.FarmSize) as [tSeasonMS/Ha], (nmlFMS.rSeasonMS /
tblForecastFarmData.FarmSize) as [rSeasonMS/Ha],
tblOriginalFarmData.Dmc1Used as [tSeasonPastureHarvest/Ha],
tblForecastFarmData.Dmc1Used as [rSeasonPastureHarvest/Ha],
(nmlFMS.tSeasonSuppUsed / tblOriginalFarmData.FarmSize) as
[tSeasonSuppUsed/Ha], (nmlFMS.rSeasonSuppUsed /
tblForecastFarmData.FarmSize) as [rSeasonSuppUsed/Ha],
(nmlFMS.tSeasonFeedCosts / tblOriginalFarmData.FarmSize) as
[tSeasonFeedCosts/Ha], (nmlFMS.rSeasonFeedCosts /
tblForecastFarmData.FarmSize) as [rSeasonFeedCosts/Ha],
(nmlFMS.tSeasonGrossMargin / tblOriginalFarmData.FarmSize) as
[tSeasonGrossMargin/Ha], (nmlFMS.rSeasonGrossMargin /
tblForecastFarmData.FarmSize) as [rSeasonGrossMargin/Ha]
into #L1
from drvFMS
inner join nmlFMS on (nmlFMS.FarmId = drvFMS.FarmId) and (nmlFMS.Season =
drvFMS.Season) and (nmlFMS.Period = drvFMS.Period)
inner join tblOriginalFarmData on (tblOriginalFarmData.FarmId =
drvFMS.FarmId) and (tblOriginalFarmData.Season = drvFMS.Season)
inner join tblForecastFarmData on (tblForecastFarmData.FarmId =
drvFMS.FarmId) and (tblForecastFarmData.Season = drvFMS.Season) and
(tblForecastFarmData.Period = drvFMS.Period)
declare @.tSeasonMSHa as integer
declare @.rSeasonMSHa as integer
declare @.tSeasonPastureHarvestHa as decimal(9,3)
declare @.rSeasonPastureHarvestHa as decimal(9,3)
declare @.tSeasonSuppUsedHa as decimal(9,3)
declare @.rSeasonSuppUsedHa as decimal(9,3)
declare @.tSeasonFeedCostsHa as decimal(9,3)
declare @.rSeasonFeedCostsHa as decimal(9,3)
declare @.tSeasonGrossMarginHa as decimal(9,3)
declare @.rSeasonGrossMarginHa as decimal(9,3)
declare a_Cursor cursor for select * from #L1
open a_Cursor
fetch next from a_Cursor
into @.F, @.S, @.P, @.tSeasonMSHa, @.rSeasonMSHa, @.tSeasonPastureHarvestHa,
@.rSeasonPastureHarvestHa, @.tSeasonSuppUsedHa, @.rSeasonSuppUsedHa,
@.tSeasonFeedCostsHa, @.rSeasonFeedCostsHa, @.tSeasonGrossMarginHa,
@.rSeasonGrossMarginHa
while @.@.fetch_status = 0
begin
update nmlFMS set [tSeasonMS/Ha] = @.tSeasonMSHa, [rSeasonMS/Ha] =
@.rSeasonMSHa, [tSeasonPastureHarvest/Ha] = @.tSeasonPastureHarvestHa,
[rSeasonPastureHarvest/Ha] = @.rSeasonPastureHarvestHa, [tSeasonSuppUsed/Ha]
= @.tSeasonSuppUsedHa, [rSeasonSuppUsed/Ha] = @.rSeasonSuppUsedHa,
[tSeasonFeedCosts/Ha] = @.tSeasonFeedCostsHa, [rSeasonFeedCosts/Ha] =
@.rSeasonFeedCostsHa, [tSeasonGrossMargin/Ha] = @.tSeasonGrossMarginHa,
[rSeasonGrossMargin/Ha] = @.rSeasonGrossMarginHa
where (FarmId = @.F) and (Season = @.S) and (Period = @.P)
fetch next from a_Cursor
into @.F, @.S, @.P, @.tSeasonMSHa, @.rSeasonMSHa, @.tSeasonPastureHarvestHa,
@.rSeasonPastureHarvestHa, @.tSeasonSuppUsedHa, @.rSeasonSuppUsedHa,
@.tSeasonFeedCostsHa, @.rSeasonFeedCostsHa, @.tSeasonGrossMarginHa,
@.rSeasonGrossMarginHa
end
close a_Cursor
deallocate a_Cursor
----
----
---
/* [tSeasonMS/Cow], [rSeasonMS/Cow], [tSeasonPastureHarvest/Cow],
[rSeasonPastureHarvest/Cow], [tSeasonSuppUsed/Cow], [rSeasonSuppUsed/Cow],
[tSeasonFeedCosts/Cow], [rSeasonFeedCosts/Cow], [tSeasonGrossMargin/Cow],
[rSeasonGrossMargin/Cow] */
select drvFMS.FarmId, drvFMS.Season, drvFMS.Period,
max(tblForecastMilkingCowData.NoOfCows) as PeakCows
into #M1
from drvFMS
inner join tblForecastMilkingCowData on (tblForecastMilkingCowData.FarmId =
drvFMS.FarmId) and (tblForecastMilkingCowData.Season = drvFMS.Season) and
(tblForecastMilkingCowData.Period = drvFMS.Period)
group by drvFMS.FarmId, drvFMS.Season, drvFMS.Period
order by drvFMS.FarmId, drvFMS.Season, drvFMS.Period
select #M1.*, tblOriginalFarmData.FarmSize
into #M2
from #M1
inner join tblOriginalFarmData on (tblOriginalFarmData.FarmId = #M1.FarmId)
and (tblOriginalFarmData.Season = #M1.Season)
select #M2.FarmId, #M2.Season, #M2.Period, cast(round((cast(nmlFMS.tSeasonMS
as decimal(11,3)) / cast(#M2.PeakCows as decimal(11,3))),0) as integer) as
'tSeasonMS/Cow', cast(round((cast(nmlFMS.rSeasonMS as decimal(11,3)) /
cast(#M2.PeakCows as decimal(11,3))),0) as integer) as 'rSeasonMS/Cow',
(nmlFMS.[tSeasonPastureHarvest/Ha] / (#M2.PeakCows / #M2.FarmSize)) as
'tSeasonPastureHarvest/Cow', (nmlFMS.[rSeasonPastureHarvest/Ha] /
(#M2.PeakCows / #M2.FarmSize)) as 'rSeasonPastureHarvest/Cow',
(nmlFMS.tSeasonSuppUsed / #M2.PeakCows) as 'tSeasonSuppUsed/Cow',
(nmlFMS.rSeasonSuppUsed / #M2.PeakCows) as 'rSeasonSuppUsed/Cow',
(nmlFMS.tSeasonFeedCosts / #M2.PeakCows) as 'tSeasonFeedCosts/Cow',
(nmlFMS.rSeasonFeedCosts / #M2.PeakCows) as 'rSeasonFeedCosts/Cow',
(nmlFMS.tSeasonGrossMargin / #M2.PeakCows) as 'tSeasonGrossMargin/Cow',
(nmlFMS.rSeasonGrossMargin / #M2.PeakCows) as 'rSeasonGrossMargin/Cow'
into #M3
from #M2
inner join nmlFMS on (nmlFMS.FarmId = #M2.FarmId) and (nmlFMS.Season =
#M2.Season) and (nmlFMS.Period = #M2.Period)
declare @.tSeasonMSCow as integer
declare @.rSeasonMSCow as integer
declare @.tSeasonPastureHarvestCow as decimal(9,3)
declare @.rSeasonPastureHarvestCow as decimal(9,3)
declare @.tSeasonSuppUsedCow as decimal(9,3)
declare @.rSeasonSuppUsedCow as decimal(9,3)
declare @.tSeasonFeedCostsCow as decimal(9,3)
declare @.rSeasonFeedCostsCow as decimal(9,3)
declare @.tSeasonGrossMarginCow as decimal(9,3)
declare @.rSeasonGrossMarginCow as decimal(9,3)
declare a_Cursor cursor for select * from #M3
open a_Cursor
fetch next from a_Cursor
into @.F, @.S, @.P, @.tSeasonMSCow, @.rSeasonMSCow, @.tSeasonPastureHarvestCow,
@.rSeasonPastureHarvestCow, @.tSeasonSuppUsedCow, @.rSeasonSuppUsedCow,
@.tSeasonFeedCostsCow, @.rSeasonFeedCostsCow, @.tSeasonGrossMarginCow,
@.rSeasonGrossMarginCow
while @.@.fetch_status = 0
begin
update nmlFMS set [tSeasonMS/Cow] = @.tSeasonMSCow, [rSeasonMS/Cow] =
@.rSeasonMSCow, [tSeasonPastureHarvest/Cow] = @.tSeasonPastureHarvestCow,
[rSeasonPastureHarvest/Cow] = @.rSeasonPastureHarvestCow,
[tSeasonSuppUsed/Cow] = @.tSeasonSuppUsedCow, [rSeasonSuppUsed/Cow] =
@.rSeasonSuppUsedCow, [tSeasonFeedCosts/Cow] = @.tSeasonFeedCostsCow,
[rSeasonFeedCosts/Cow] = @.rSeasonFeedCostsCow, [tSeasonGrossMargin/Cow] =
@.tSeasonGrossMarginCow, [rSeasonGrossMargin/Cow] = @.rSeasonGrossMarginCow
where (FarmId = @.F) and (Season = @.S) and (Period = @.P)
fetch next from a_Cursor
into @.F, @.S, @.P, @.tSeasonMSCow, @.rSeasonMSCow, @.tSeasonPastureHarvestCow,
@.rSeasonPastureHarvestCow, @.tSeasonSuppUsedCow, @.rSeasonSuppUsedCow,
@.tSeasonFeedCostsCow, @.rSeasonFeedCostsCow, @.tSeasonGrossMarginCow,
@.rSeasonGrossMarginCow
end
close a_Cursor
deallocate a_Cursor
----
----
---
/* [tSeasonPastureHarvest/KgMS], [rSeasonPastureHarvest/KgMS],
[tSeasonSuppUsed/KgMS], [rSeasonSuppUsed/KgMS], [tSeasonFeedCosts/KgMS],
[rSeasonFeedCosts/KgMS], [tSeasonGrossMargin/KgMS],
[rSeasonGrossMargin/KgMS] */
select drvFMS.FarmId, drvFMS.Season, drvFMS.Period,
((nmlFMS.[tSeasonPastureHarvest/Ha] * 1000) / nmlFMS.[tSeasonMS/Ha]) as
'tSeasonPastureHarvest/KgMS', ((nmlFMS.[rSeasonPastureHarvest/Ha] * 1000) /
nmlFMS.[rSeasonMS/Ha]) as 'rSeasonPastureHarvest/KgMS',
((nmlFMS.tSeasonSuppUsed * 1000 / tblOriginalFarmData.FarmSize) /
nmlFMS.[tSeasonMS/Ha]) as 'tSeasonSuppUsed/KgMS', ((nmlFMS.rSeasonSuppUsed *
1000 / tblOriginalFarmData.FarmSize) / nmlFMS.[rSeasonMS/Ha]) as
'rSeasonSuppUsed/KgMS', (nmlFMS.tSeasonFeedCosts / nmlFMS.tSeasonMS) as
'tSeasonFeedCosts/KgMS', (nmlFMS.rSeasonFeedCosts / nmlFMS.rSeasonMS) as
'rSeasonFeedCosts/KgMS', (nmlFMS.tSeasonGrossMargin / nmlFMS.tSeasonMS) as
'tSeasonGrossMargin/KgMS', (nmlFMS.rSeasonGrossMargin / nmlFMS.rSeasonMS) as
'rSeasonGrossMargin/KgMS'
into #N1
from drvFMS
inner join nmlFMS on (nmlFMS.FarmId = drvFMS.FarmId) and (nmlFMS.Season =
drvFMS.Season) and (nmlFMS.Period = drvFMS.Period)
inner join tblOriginalFarmData on (tblOriginalFarmData.FarmId =
drvFMS.FarmId) and (tblOriginalFarmData.Season = drvFMS.Season)
declare @.tSeasonPastureHarvestKgMS as decimal(9,3)
declare @.rSeasonPastureHarvestKgMS as decimal(9,3)
declare @.tSeasonSuppUsedKgMS as decimal(9,3)
declare @.rSeasonSuppUsedKgMS as decimal(9,3)
declare @.tSeasonFeedCostsKgMS as decimal(9,3)
declare @.rSeasonFeedCostsKgMS as decimal(9,3)
declare @.tSeasonGrossMarginKgMS as decimal(9,3)
declare @.rSeasonGrossMarginKgMS as decimal(9,3)
declare a_Cursor cursor for select * from #N1
open a_Cursor
fetch next from a_Cursor
into @.F, @.S, @.P, @.tSeasonPastureHarvestKgMS, @.rSeasonPastureHarvestKgMS,
@.tSeasonSuppUsedKgMS, @.rSeasonSuppUsedKgMS, @.tSeasonFeedCostsKgMS,
@.rSeasonFeedCostsKgMS, @.tSeasonGrossMarginKgMS, @.rSeasonGrossMarginKgMS
while @.@.fetch_status = 0
begin
update nmlFMS set [tSeasonPastureHarvest/KgMS] =
@.tSeasonPastureHarvestKgMS, [rSeasonPastureHarvest/KgMS] =
@.rSeasonPastureHarvestKgMS, [tSeasonSuppUsed/KgMS] = @.tSeasonSuppUsedKgMS,
[rSeasonSuppUsed/KgMS] = @.rSeasonSuppUsedKgMS, [tSeasonFeedCosts/KgMS] =
@.tSeasonFeedCostsKgMS, [rSeasonFeedCosts/KgMS] = @.rSeasonFeedCostsKgMS,
[tSeasonGrossMargin/KgMS] = @.tSeasonGrossMarginKgMS,
[rSeasonGrossMargin/KgMS] = @.rSeasonGrossMarginKgMS
where (FarmId = @.F) and (Season = @.S) and (Period = @.P)
fetch next from a_Cursor
into @.F, @.S, @.P, @.tSeasonPastureHarvestKgMS, @.rSeasonPastureHarvestKgMS,
@.tSeasonSuppUsedKgMS, @.rSeasonSuppUsedKgMS, @.tSeasonFeedCostsKgMS,
@.rSeasonFeedCostsKgMS, @.tSeasonGrossMarginKgMS, @.rSeasonGrossMarginKgMS
end
close a_Cursor
deallocate a_Cursor
----
----
---
/* nmMthEndAvgCover, nmPastureGrowth */
select drvFMS.FarmId, drvFMS.Season, drvFMS.Period,
tblForecastPastureData.Cover as nmMthEndAvgCover
into #O1
from drvFMS
inner join tblForecastPastureData on (tblForecastPastureData.FarmId =
drvFMS.FarmId) and (tblForecastPastureData.Season = drvFMS.Season) and
(tblForecastPastureData.Period = drvFMS.Period) and
(month(tblForecastPastureData.[Date]) = @.NextPeriod_Month) and
(day(tblForecastPastureData.[Date]) = 21)
select #O1.*, tblForecastPastureData.Growth
into #O2
from #O1
inner join tblForecastPastureData on (tblForecastPastureData.FarmId =
#O1.FarmId) and (tblForecastPastureData.Season = #O1.Season) and
(tblForecastPastureData.Period = #O1.Period) and
(month(tblForecastPastureData.[Date]) = @.NextPeriod_Month)
select #O2.FarmId, #O2.Season, #O2.Period, #O2.nmMthEndAvgCover,
avg(cast(#O2.Growth as decimal(9,3))) as nmPastureGrowth
into #O3
from #O2
group by #O2.FarmId, #O2.Season, #O2.Period, #O2.nmMthEndAvgCover
declare @.nmMthEndAvgCover as smallint
declare @.nmPastureGrowth as decimal(9,3)
declare a_Cursor cursor for select * from #O3
open a_Cursor
fetch next from a_Cursor
into @.F, @.S, @.P, @.nmMthEndAvgCover, @.nmPastureGrowth
while @.@.fetch_status = 0
begin
update nmlFMS set nmMthEndAvgCover = @.nmMthEndAvgCover, nmPastureGrowth =
@.nmPastureGrowth
where (FarmId = @.F) and (Season = @.S) and (Period = @.P)
fetch next from a_Cursor
into @.F, @.S, @.P, @.nmMthEndAvgCover, @.nmPastureGrowth
end
close a_Cursor
deallocate a_Cursor
----
----
---
/* nmAvgCowsMilked, nmDmc1, nmDmc2, nmDmc3, nmDmc4, nmDmc5, nmDmc6, nmDmc7,
nmCrops, nmTotalDMConsumed */
select drvFMS.FarmId, drvFMS.Season, drvFMS.Period,
avg(cast(tblForecastMilkingCowData.NoOfCows as decimal(9,3))) as
nmAvgCowsMilked, avg(cast(tblForecastMilkingCowData.dmc1 as decimal(9,3)))
as nmDmc1avg, avg(cast(tblForecastMilkingCowData.dmc2 as decimal(9,3))) as
nmDmc2avg, avg(cast(tblForecastMilkingCowData.dmc3 as decimal(9,3))) as
nmDmc3avg, avg(cast(tblForecastMilkingCowData.dmc4 as decimal(9,3))) as
nmDmc4avg, avg(cast(tblForecastMilkingCowData.dmc5 as decimal(9,3))) as
nmDmc5avg, avg(cast(tblForecastMilkingCowData.dmc6 as decimal(9,3))) as
nmDmc6avg, avg(cast(tblForecastMilkingCowData.dmc7 as decimal(9,3))) as
nmDmc7avg, avg(cast(tblForecastMilkingCowData.Crop1 as decimal(9,3))) as
nmCrop1avg, avg(cast(tblForecastMilkingCowData.Crop2 as decimal(9,3))) as
nmCrop2avg, avg(cast(tblForecastMilkingCowData.Crop3 as decimal(9,3))) as
nmCrop3avg, avg(cast(tblForecastMilkingCowData.Crop4 as decimal(9,3))) as
nmCrop4avg, avg(cast(tblForecastMilkingCowData.Crop5 as decimal(9,3))) as
nmCrop5avg
into #P1
from tblForecastMilkingCowData
inner join drvFMS on (tblForecastMilkingCowData.FarmId = drvFMS.FarmId) and
(tblForecastMilkingCowData.Season = drvFMS.Season) and
(tblForecastMilkingCowData.Period = @.ThisPeriod) and
(month(tblForecastMilkingCowData.[Date]) = @.NextPeriod_Month)
group by drvFMS.FarmId, drvFMS.Season, drvFMS.Period
select #P1.*, (nmCrop1avg + nmCrop2avg + nmCrop3avg + nmCrop4avg +
nmCrop5avg) as nmCrops, (round(nmDmc1avg,1) + round(nmDmc2avg,1) +
round(nmDmc3avg,1) + round(nmDmc4avg,1) + round(nmDmc5avg,1) +
round(nmDmc6avg,1) + round(nmDmc7avg,1) + round(nmCrop1avg,1) +
round(nmCrop2avg,1) + round(nmCrop3avg,1) + round(nmCrop4avg,1) +
round(nmCrop5avg,1)) as nmTotalDMConsumed
into #P2
from #P1
declare @.nmAvgCowsMilked as smallint
declare @.nmDmc1 as decimal(9,3)
declare @.nmDmc2 as decimal(9,3)
declare @.nmDmc3 as decimal(9,3)
declare @.nmDmc4 as decimal(9,3)
declare @.nmDmc5 as decimal(9,3)
declare @.nmDmc6 as decimal(9,3)
declare @.nmDmc7 as decimal(9,3)
declare @.nmCrops as decimal(9,3)
declare @.nmTotalDMConsumed as decimal(9,3)
declare a_Cursor cursor for
select FarmId, Season, Period, nmAvgCowsMilked, nmDmc1avg, nmDmc2avg,
nmDmc3avg, nmDmc4avg, nmDmc5avg, nmDmc6avg, nmDmc7avg, nmCrops,
nmTotalDMConsumed from #P2
open a_Cursor
fetch next from a_Cursor
into @.F, @.S, @.P, @.nmAvgCowsMilked, @.nmDmc1, @.nmDmc2, @.nmDmc3, @.nmDmc4,
@.nmDmc5, @.nmDmc6, @.nmDmc7, @.nmCrops, @.nmTotalDMConsumed
while @.@.fetch_status = 0
begin
update nmlFMS set nmAvgCowsMilked = @.nmAvgCowsMilked, nmDmc1 = @.nmDmc1,
nmDmc2 = @.nmDmc2, nmDmc3 = @.nmDmc3, nmDmc4 = @.nmDmc4, nmDmc5 = @.nmDmc5,
nmDmc6 = @.nmDmc6, nmDmc7 = @.nmDmc7, nmCrops = @.nmCrops, nmTotalDMConsumed =
@.nmTotalDMConsumed
where (FarmId = @.F) and (Season = @.S) and (Period = @.P)
fetch next from a_Cursor
into @.F, @.S, @.P, @.nmAvgCowsMilked, @.nmDmc1, @.nmDmc2, @.nmDmc3, @.nmDmc4,
@.nmDmc5, @.nmDmc6, @.nmDmc7, @.nmCrops, @.nmTotalDMConsumed
end
close a_Cursor
deallocate a_Cursor
----
----
---
/* nmSeasonToDateMS, nmMonthTotalMS, [nmMS/Cow/Day] */
select drvFMS.FarmId, drvFMS.Season, drvFMS.Period,
month(tblForecastProductionData.[Date]) as [Month],
(tblForecastProductionData.Fat + tblForecastProductionData.Protein) as
MilkSolids, tblForecastProductionData.Days
into #Q1
from drvFMS
inner join tblForecastProductionData on (tblForecastProductionData.FarmId =
drvFMS.FarmId) and (tblForecastProductionData.Season = drvFMS.Season) and
(tblForecastProductionData.Period = @.ThisPeriod)
select #Q1.FarmId, #Q1.Season, #Q1.Period, sum(#Q1.MilkSolids) as
nmMonthTotalMS, sum(#Q1.Days) as [Days]
into #Q2
from #Q1
where (#Q1.[Month] = @.NextPeriod_Month)
group by FarmId, Season, Period
create index Q2_ndx on #Q2 (FarmId, Season, Period)
create index P2_ndx on #P2 (FarmId, Season, Period)
select #Q1.*, dbo.fnMonthToPeriod([Month]) as MonthPeriod,
#Q2.nmMonthTotalMS, cast((#Q2.nmMonthTotalMS / #P2.nmAvgCowsMilked /
#Q2.Days) as decimal(9,3)) as [nmMS/Cow/Day]
into #Q3
from #Q1
inner join #Q2 on (#Q2.FarmId = #Q1.FarmId) and (#Q2.Season = #Q1.Season)
and (#Q2.Period = #Q1.Period)
inner join #P2 on (#P2.FarmId = #Q1.FarmId) and (#P2.Season = #Q1.Season)
and (#P2.Period = #Q1.Period)
select #Q3.FarmId, #Q3.Season, #Q3.Period, #Q3.nmMonthTotalMS,
[nmMS/Cow/Day], sum(#Q3.MilkSolids) as nmSeasonToDateMS
into #Q4
from #Q3
where #Q3.MonthPeriod <= @.NextPeriod
group by FarmId, Season, Period, nmMonthTotalMS, [nmMS/Cow/Day]
declare @.nmMonthTotalMS as decimal(9,3)
declare @.nmMSCowDay as decimal(9,3)
declare @.nmSeasonToDateMS as decimal(9,3)
declare a_Cursor cursor for select * from #Q4
open a_Cursor
fetch next from a_Cursor
into @.F, @.S, @.P, @.nmMonthTotalMS, @.nmMSCowDay, @.nmSeasonToDateMS
while @.@.fetch_status = 0
begin
update nmlFMS set nmMonthTotalMS = @.nmMonthTotalMS, [nmMS/Cow/Day] =
@.nmMSCowDay, nmSeasonToDateMS = @.nmSeasonToDateMS
where (FarmId = @.F) and (Season = @.S) and (Period = @.P)
fetch next from a_Cursor
into @.F, @.S, @.P, @.nmMonthTotalMS, @.nmMSCowDay, @.nmSeasonToDateMS
end
close a_Cursor
deallocate a_Cursor
----
----
---
/* nmFeedConvEff, [nmFeedAlloc/LiveWeight], [nmMthMS/Ha] */
select #P2.FarmId, #P2.Season, #P2.Period, #P2.nmTotalDMConsumed,
sum(tblForecastProductionData.Days) as [Days]
into #R1
from #P2
inner join tblForecastProductionData on (tblForecastProductionData.FarmId =
#P2.FarmId) and (tblForecastProductionData.Season = #P2.Season) and
(tblForecastProductionData.Period = @.ThisPeriod) and
(month(tblForecastProductionData.[Date]) = @.NextPeriod_Month)
group by #P2.FarmId, #P2.Season, #P2.Period, #P2.nmTotalDMConsumed
select #R1.FarmId, #R1.Season, #R1.Period, #R1.nmTotalDMConsumed, #R1.Days,
avg(cast(tblForecastPastureData.FarmSize as decimal(9,3))) as nmAverageArea
into #R2
from #R1
inner join tblForecastPastureData on (tblForecastPastureData.FarmId =
#R1.FarmId) and (tblForecastPastureData.Season = #R1.Season) and
(tblForecastPastureData.Period = @.ThisPeriod) and
(month(tblForecastPastureData.[Date]) = @.NextPeriod_Month)
group by #R1.FarmId, #R1.Season, #R1.Period, #R1.nmTotalDMConsumed,
#R1.[Days]
select #R2.*, #P2.nmAvgCowsMilked, #Q4.nmMonthTotalMS,
tblForecastFarmData.FarmSize as 'nmAvailableArea',
tblFarmAdditionalInfo.Liveweight
into #R3
from #R2
inner join #P2 on (#P2.FarmId = #R2.FarmId) and (#P2.Season = #R2.Season)
and (#P2.Period = #R2.Period)
inner join #Q4 on (#Q4.FarmId = #R2.FarmId) and (#Q4.Season = #R2.Season)
and (#Q4.Period = #R2.Period)
inner join tblForecastFarmData on (tblForecastFarmData.FarmId = #R2.FarmId)
and (tblForecastFarmData.Season = #R2.Season) and
(tblForecastFarmData.Period = #R2.Period)
inner join tblFarmAdditionalInfo on (tblFarmAdditionalInfo.FarmId =
#R2.FarmId)
select #R3.FarmId, #R3.Season, #R3.Period, ((#R3.nmTotalDMConsumed *
#R3.Days * (#R3.nmAvgCowsMilked / #R3.nmAverageArea)) / (#R3.nmMonthTotalMS
/ #R3.nmAvailableArea)) as nmFeedConvEff, (#R3.nmTotalDMConsumed /
#R3.Liveweight * 100) as [nmFeedAlloc/LiveWeight], (#R3.nmMonthTotalMS /
#R3.nmAvailableArea) as [nmMthMS/Ha]
into #R4
from #R3
declare @.nmFeedConvEff as decimal(9,3)
declare @.nmFeedAllocLiveWeight as decimal(9,3)
declare @.nmMthMSHa as decimal(9,3)
declare a_Cursor cursor for select * from #R4
open a_Cursor
fetch next from a_Cursor
into @.F, @.S, @.P, @.nmFeedConvEff, @.nmFeedAllocLiveWeight, @.nmMthMSHa
while @.@.fetch_status = 0
begin
update nmlFMS set nmFeedConvEff = cast(@.nmFeedConvEff as decimal(6,4)),
[nmFeedAlloc/LiveWeight] = cast(@.nmFeedAllocLiveWeight as decimal(6,4)),
[nmMthMS/Ha] = @.nmMthMSHa
where (FarmId = @.F) and (Season = @.S) and (Period = @.P)
fetch next from a_Cursor
into @.F, @.S, @.P, @.nmFeedConvEff, @.nmFeedAllocLiveWeight, @.nmMthMSHa
end
close a_Cursor
deallocate a_Cursor
----
----
---
/* [nmMthFeedCosts/KgMS] */
select #R1.FarmId, #R1.Season, #R1.Period, #R1.Days,
cast(avg(tblForecastMilkingCowData.NoOfCows) as smallint) as
nmAvgCowsMilked, avg(tblForecastMilkingCowData.dmc2 *
tblFarmAdditionalInfo.dmc2$) as nmDmc2$, avg(tblForecastMilkingCowData.dmc3
* tblFarmAdditionalInfo.dmc3$) as nmDmc3$,
avg(tblForecastMilkingCowData.dmc4 * tblFarmAdditionalInfo.dmc4$) as
nmDmc4$, avg(tblForecastMilkingCowData.dmc5 * tblFarmAdditionalInfo.dmc5$)
as nmDmc5$, avg(tblForecastMilkingCowData.dmc6 *
tblFarmAdditionalInfo.dmc6$) as nmDmc6$, avg(tblForecastMilkingCowData.dmc7
* tblFarmAdditionalInfo.dmc7$) as nmDmc7$,
avg(tblForecastMilkingCowData.Crop1 * tblFarmAdditionalInfo.Crop1$) as
nmCrop1$, avg(tblForecastMilkingCowData.Crop2 *
tblFarmAdditionalInfo.Crop2$) as nmCrop2$,
avg(tblForecastMilkingCowData.Crop3 * tblFarmAdditionalInfo.Crop3$) as
nmCrop3$, avg(tblForecastMilkingCowData.Crop4 *
tblFarmAdditionalInfo.Crop4$) as nmCrop4$,
avg(tblForecastMilkingCowData.Crop5 * tblFarmAdditionalInfo.Crop5$) as
nmCrop5$
into #S1
from #R1
inner join tblForecastMilkingCowData on (tblForecastMilkingCowData.FarmId =
#R1.FarmId) and (tblForecastMilkingCowData.Season = #R1.Season) and
(tblForecastMilkingCowData.Period = @.ThisPeriod) and
(month(tblForecastMilkingCowData.[Date]) = @.NextPeriod_Month)
inner join tblFarmAdditionalInfo on (tblFarmAdditionalInfo.FarmId =
#R1.FarmId)
group by #R1.FarmId, #R1.Season, #R1.Period, #R1.Days
select #S1.FarmId, #S1.Season, #S1.Period, #S1.Days, #S1.nmAvgCowsMilked,
#Q2.nmMonthTotalMS, (#S1.nmDmc2$ + #S1.nmDmc3$ + #S1.nmDmc4$ + #S1.nmDmc5$ +
#S1.nmDmc6$ + #S1.nmDmc7$ + #S1.nmCrop1$ + #S1.nmCrop2$ + #S1.nmCrop3$ +
#S1.nmCrop4$ + #S1.nmCrop5$) as nmMthFeedCosts,
tblForecastFarmData.Adjistment$ as nmGrazingCosts$, (tblForecastFarmData.Fat
+ tblForecastFarmData.Protein) as nmSeasonMS, (tblForecastFarmData.IOFC$ -
tblForecastFarmData.CowCosts$ - tblForecastFarmData.GrossMargin$) as
nmIrrigationCosts$, nmlFMS.rSeasonSuppUsed, nmlFMS.rSeasonFeedCosts
into #S2
from #S1
inner join #Q2 on (#Q2.FarmId = #S1.FarmId) and (#Q2.Season = #S1.Season)
and (#Q2.Period = #S1.Period)
inner join tblForecastFarmData on (tblForecastFarmData.FarmId = #S1.FarmId)
and (tblForecastFarmData.Season = #S1.Season) and
(tblForecastFarmData.Period = @.ThisPeriod)
inner join nmlFMS on (nmlFMS.FarmId = #S1.FarmId) and (nmlFMS.Season =
#S1.Season) and (nmlFMS.Period = #S1.Period)
select #S2.*, (((#S2.nmMthFeedCosts * #S2.nmAvgCowsMilked * #S2.Days) /
#S2.nmMonthTotalMS) + (#S2.nmGrazingCosts$ / #S2.nmSeasonMS) +
(#S2.nmIrrigationCosts$ / #S2.nmSeasonMS)) as nmMthFeedCostsKgMS
into #S3
from #S2
declare @.nmMthFeedCostsKgMS as decimal(9,4)
declare a_Cursor cursor for select FarmId, Season, Period,
nmMthFeedCostsKgMS from #S3
open a_Cursor
fetch next from a_Cursor
into @.F, @.S, @.P, @.nmMthFeedCostsKgMS
while @.@.fetch_status = 0
begin
update nmlFMS set [nmMthFeedCosts/KgMS] = @.nmMthFeedCostsKgMS
where (FarmId = @.F) and (Season = @.S) and (Period = @.P)
fetch next from a_Cursor
into @.F, @.S, @.P, @.nmMthFeedCostsKgMS
end
close a_Cursor
deallocate a_Cursor
----
----
---
EndOf:
Return
GOTrash the lot! is the most constructive advice I can give. Useless
cursor code (actually I gave up, incredulous, after examining the first
10 cursors!) and all those temp tables are just a nightmare. I honestly
don't think the person who wrote this had a clue what he/she was doing.
David Portas
SQL Server MVP
--|||In news:O9XQLNyHFHA.3760@.TK2MSFTNGP12.phx.gbl,
SBeetham <sbeetham@.xtra.co.nz> said:
> I apologize if this is the wrong forum for this kind of thing.
You needn't appologize, although you may wish to s
counselling havingwitnessed the stored procedure you just posted. Of course you'll now be
liable for other usenet readers' damages, suffered whilst reading your post.
;-)
> I have inherited the following stored procedure (of some 1000 lines)
> and would like to rationalize, tune, optimize or just completely
> re-architect it.
> Some thoughts or guidance would be appreciated.
> What I'm looking for is suggestions like... Chop bits like blah, blah
> into smaller procs. and stuff like that.
Chop bits, yes, chop lots and lots of bits. Until there's nothing left.
Then write it properly from a spec of what the proc is supposed to do.
Changing small parts of such a monstrous routine can only lead to heartache.
Steve|||I'd go with Dave on this one; trash it. You have temp tables,
home-grown temporal data types, names that begin with "tbl-", cursors
and procedure over 50 lines long. The only classic bad programming
stunt he missed was dynamic SQL.
Start by putting the reporting periods into a calendar table and
getting the base table to use DATETIME data types. That should remove
hundreds of lines of code.|||Simon,
As frightening as this looks, I think you could begin by working
on pieces. After the first few pages, it's just a bunch of updates to
one table - updates that are written as cursors that can be rewritten
as single queries. For example,
-- This simple update
update nmlFMS set
[tSeasonPastureHarvest/KgMS] = #N2.[tSeasonPastureHarvest/KgMS]
[rSeasonPastureHarvest/KgMS] = #N2.[rSeasonPastureHarvest/KgMS]
[tSeasonSuppUsed/KgMS] = #N2.[tSeasonSuppUsed/KgMS]
[tSeasonSuppUsed/KgMS] = #N2.[tSeasonSuppUsed/KgMS]
[tSeasonFeedCosts/KgMS] = #N2.[tSeasonFeedCosts/KgMS]
[rSeasonFeedCosts/KgMS] = #N2.[rSeasonFeedCosts/KgMS]
[tSeasonGrossMargin/KgMS] = #N2.[tSeasonGrossMargin/KgMS]
[rSeasonGrossMargin/KgMS] = #N2.[rSeasonGrossMargin/KgMS]
from nmlFMS join #N2
on (nmlFMS.FarmId = #N2.FarmId)
and (nmlFMS.Season = #N2.Season)
and (nmlFMS.Period = #N2.Period)
-- should replace this cursor:
declare a_Cursor cursor for select * from #N1
open a_Cursor
fetch next from a_Cursor
into @.F, @.S, @.P, @.tSeasonPastureHarvestKgMS, @.rSeasonPastureHarvestKgMS,
@.tSeasonSuppUsedKgMS, @.rSeasonSuppUsedKgMS, @.tSeasonFeedCostsKgMS,
@.rSeasonFeedCostsKgMS, @.tSeasonGrossMarginKgMS, @.rSeasonGrossMarginKgMS
while @.@.fetch_status = 0
begin
update nmlFMS set [tSeasonPastureHarvest/KgMS] =
@.tSeasonPastureHarvestKgMS, [rSeasonPastureHarvest/KgMS] =
@.rSeasonPastureHarvestKgMS, [tSeasonSuppUsed/KgMS] = @.tSeasonSuppUsedKgMS,
[rSeasonSuppUsed/KgMS] = @.rSeasonSuppUsedKgMS, [tSeasonFeedCosts/KgMS] =
@.tSeasonFeedCostsKgMS, [rSeasonFeedCosts/KgMS] = @.rSeasonFeedCostsKgMS,
[tSeasonGrossMargin/KgMS] = @.tSeasonGrossMarginKgMS,
[rSeasonGrossMargin/KgMS] = @.rSeasonGrossMarginKgMS
where (FarmId = @.F) and (Season = @.S) and (Period = @.P)
fetch next from a_Cursor
into @.F, @.S, @.P, @.tSeasonPastureHarvestKgMS, @.rSeasonPastureHarvestKgMS,
@.tSeasonSuppUsedKgMS, @.rSeasonSuppUsedKgMS, @.tSeasonFeedCostsKgMS,
@.rSeasonFeedCostsKgMS, @.tSeasonGrossMarginKgMS, @.rSeasonGrossMarginKgMS
end
close a_Cursor
deallocate a_Cursor
The very beginning can be simplified also. For example:
-- This
select @.ThisPeriod_Month = 1 + (@.ThisPeriod + 4) % 12
-- Is a simple replacement for this:
select @.ThisPeriod_Month =
case @.ThisPeriod
when 1 then 6
when 2 then 7
when 3 then 8
when 4 then 9
when 5 then 10
when 6 then 11
when 7 then 12
when 8 then 1
when 9 then 2
when 10 then 3
when 11 then 4
when 12 then 5
end
Once you do all this, it should be easier to picture the entire process
more clearly. While the overuse of cursors and temp tables isn't
efficient, I've seen worse - it's at least readable,
and as far as the population of the temp tables goes, maybe more
readable than a really slick set-based query for someone unfamiliar
with the business, since it takes things slowly and stepwise.
Steve Kass
Drew University
SBeetham wrote:
>Hi,
>
>I apologize if this is the wrong forum for this kind of thing.
>
>I have inherited the following stored procedure (of some 1000 lines) and
>would like to rationalize, tune, optimize or just completely re-architect
>it.
>
>Some thoughts or guidance would be appreciated.
>What I'm looking for is suggestions like... Chop bits like blah, blah into
>smaller procs. and stuff like that.
>
>
>Cheers, Simon.
> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
>
>CREATE procedure [dbo].[spNmlFMS]
>as
>set nocount on
>set ansi_warnings off
>set arithabort off
>set arithignore on
>declare @.PrevPeriod as tinyint
>declare @.PrevPeriod_Month as tinyint
>declare @.PrevPeriod_Year as smallint
>declare @.PrevPeriod_Season as smallint
>declare @.ThisPeriod as tinyint
>declare @.ThisPeriod_Month as tinyint
>declare @.ThisPeriod_Year as smallint
>declare @.NextPeriod as tinyint
>declare @.NextPeriod_Month as tinyint
>declare @.NextPeriod_Year as smallint
>declare @.ThisSeason as smallint
>----
-
>----
-
>---
>select @.ThisPeriod = (select distinct Period from drvFMS)
>select @.ThisSeason = (select distinct Season from drvFMS)
>select @.ThisPeriod_Month =
> case @.ThisPeriod
> when 1 then 6
> when 2 then 7
> when 3 then 8
> when 4 then 9
> when 5 then 10
> when 6 then 11
> when 7 then 12
> when 8 then 1
> when 9 then 2
> when 10 then 3
> when 11 then 4
> when 12 then 5
> end
>select @.ThisPeriod_Year =
> case @.ThisPeriod
> when 1 then (@.ThisSeason - 1)
> when 2 then (@.ThisSeason - 1)
> when 3 then (@.ThisSeason - 1)
> when 4 then (@.ThisSeason - 1)
> when 5 then (@.ThisSeason - 1)
> when 6 then (@.ThisSeason - 1)
> when 7 then (@.ThisSeason - 1)
> when 8 then @.ThisSeason
> when 9 then @.ThisSeason
> when 10 then @.ThisSeason
> when 11 then @.ThisSeason
> when 12 then @.ThisSeason
> end
>set @.PrevPeriod = @.ThisPeriod - 1
>set @.PrevPeriod_Month = @.ThisPeriod_Month - 1
>if @.PrevPeriod < 1
> begin
> set @.PrevPeriod = 12
> end
>if @.PrevPeriod_Month < 1
> begin
> set @.PrevPeriod_Month = 12
> end
>select @.PrevPeriod_Year =
> case @.PrevPeriod
> when 1 then (@.ThisSeason - 1)
> when 2 then (@.ThisSeason - 1)
> when 3 then (@.ThisSeason - 1)
> when 4 then (@.ThisSeason - 1)
> when 5 then (@.ThisSeason - 1)
> when 6 then (@.ThisSeason - 1)
> when 7 then (@.ThisSeason - 1)
> when 8 then @.ThisSeason
> when 9 then @.ThisSeason
> when 10 then @.ThisSeason
> when 11 then @.ThisSeason
> when 12 then (@.ThisSeason - 1)
> end
>select @.PrevPeriod_Season =
> case @.PrevPeriod
> when 1 then @.ThisSeason
> when 2 then @.ThisSeason
> when 3 then @.ThisSeason
> when 4 then @.ThisSeason
> when 5 then @.ThisSeason
> when 6 then @.ThisSeason
> when 7 then @.ThisSeason
> when 8 then @.ThisSeason
> when 9 then @.ThisSeason
> when 10 then @.ThisSeason
> when 11 then @.ThisSeason
> when 12 then (@.ThisSeason - 1)
> end
>select @.NextPeriod =
> case @.ThisPeriod
> when 1 then 2
> when 2 then 3
> when 3 then 4
> when 4 then 5
> when 5 then 6
> when 6 then 7
> when 7 then 8
> when 8 then 9
> when 9 then 10
> when 10 then 11
> when 11 then 12
> when 12 then 1
> end
>select @.NextPeriod_Month =
> case @.NextPeriod
> when 1 then 6
> when 2 then 7
> when 3 then 8
> when 4 then 9
> when 5 then 10
> when 6 then 11
> when 7 then 12
> when 8 then 1
> when 9 then 2
> when 10 then 3
> when 11 then 4
> when 12 then 5
> end
>----
-
>----
-
>---
>/* aMthEndAvgCover, aPastureGrowth */
>select drvFMS.FarmId, drvFMS.Season, drvFMS.Period,
>tblActualPastureData.Cover as ActualCover, tblForecastPastureData.Cover as
>ForecastCover
>into #A1
>from drvFMS
>inner join tblActualPastureData on (tblActualPastureData.FarmId =
>drvFMS.FarmId) and (tblActualPastureData.Season = drvFMS.Season) and
>(tblActualPastureData.Period = drvFMS.Period) and
>(month(tblActualPastureData.[Date]) = @.ThisPeriod_Month) and
>(day(tblActualPastureData.[Date]) = 21)
>inner join tblForecastPastureData on (tblForecastPastureData.FarmId =
>drvFMS.FarmId) and (tblForecastPastureData.Season = drvFMS.Season) and
>(tblForecastPastureData.Period = drvFMS.Period) and
>(month(tblForecastPastureData.[Date]) = @.ThisPeriod_Month) and
>(day(tblForecastPastureData.[Date]) = 21)
>select #A1.*, tblForecastPastureData.Growth
>into #A2
>from #A1
>inner join tblForecastPastureData on (tblForecastPastureData.FarmId =
>#A1.FarmId) and (tblForecastPastureData.Season = #A1.Season) and
>(tblForecastPastureData.Period = #A1.Period) and
>(month(tblForecastPastureData.[Date]) = @.ThisPeriod_Month)
>select #A2.FarmId, #A2.Season, #A2.Period, #A2.ActualCover,
>#A2.ForecastCover, avg(cast(#A2.Growth as decimal(9,3))) as aPastureGrowth
>into #A3
>from #A2
>group by #A2.FarmId, #A2.Season, #A2.Period, #A2.ActualCover,
>#A2.ForecastCover
>declare @.F as varchar(7)
>declare @.S as smallint
>declare @.P as tinyint
>declare @.ActualCover as smallint
>declare @.ForecastCover as smallint
>declare @.Cover as smallint
>declare @.aPastureGrowth as decimal(9,3)
>declare a_Cursor cursor for
>select FarmId, Season, Period, ActualCover, ForecastCover, aPastureGrowth
>from #A3
>open a_Cursor
>fetch next from a_Cursor
>into @.F, @.S, @.P, @.ActualCover, @.ForecastCover, @.aPastureGrowth
>while @.@.fetch_status = 0
>begin
> if @.ActualCover >= 1000
> Set @.Cover = @.ActualCover
> else
> if @.ForecastCover >= 1000
> Set @.Cover = @.ForecastCover
> else
> Set @.Cover = 0
> if exists (select FarmId, Season, Period from nmlFMS where (FarmId = @.F)
>and (Season = @.S) and (Period = @.P))
> begin
> update nmlFMS set aMthEndAvgCover = @.Cover, aPastureGrowth =
>@.aPastureGrowth
> where (FarmId = @.F) and (Season = @.S) and (Period = @.P)
> end
> else
> begin
> insert into nmlFMS (FarmId, Season, Period, aMthEndAvgCover,
>aPastureGrowth) values (@.F, @.S, @.P, @.Cover, @.aPastureGrowth)
> end
> fetch next from a_Cursor
> into @.F, @.S, @.P, @.ActualCover, @.ForecastCover, @.aPastureGrowth
>end
>close a_Cursor
>deallocate a_Cursor
>----
-
>----
-
>---
>/* aAvgCowsMilked, aDmc1, aDmc2, aDmc3, aDmc4, aDmc5, aDmc6, aDmc7, aCrops,
>aTotalDMConsumed, dmc1Description, dmc2Description, dmc3Description,
>dmc4Description, dmc5Description, dmc6Description, dmc7Description */
>select drvFMS.FarmId, drvFMS.Season, drvFMS.Period,
>avg(cast(tblActualMilkingCowData.NoOfCows as decimal(9,3))) as
>aAvgCowsMilked, avg(cast(tblActualMilkingCowData.dmc1 as decimal(9,3))) as
>aDmc1avg, avg(cast(tblActualMilkingCowData.dmc2 as decimal(9,3))) as
>aDmc2avg, avg(cast(tblActualMilkingCowData.dmc3 as decimal(9,3))) as
>aDmc3avg, avg(cast(tblActualMilkingCowData.dmc4 as decimal(9,3))) as
>aDmc4avg, avg(cast(tblActualMilkingCowData.dmc5 as decimal(9,3))) as
>aDmc5avg, avg(cast(tblActualMilkingCowData.dmc6 as decimal(9,3))) as
>aDmc6avg, avg(cast(tblActualMilkingCowData.dmc7 as decimal(9,3))) as
>aDmc7avg, avg(cast(tblActualMilkingCowData.Crop1 as decimal(9,3))) as
>aCrop1avg, avg(cast(tblActualMilkingCowData.Crop2 as decimal(9,3))) as
>aCrop2avg, avg(cast(tblActualMilkingCowData.Crop3 as decimal(9,3))) as
>aCrop3avg, avg(cast(tblActualMilkingCowData.Crop4 as decimal(9,3))) as
>aCrop4avg, avg(cast(tblActualMilkingCowData.Crop5 as decimal(9,3))) as
>aCrop5avg
>into #B1
>from tblActualMilkingCowData
>inner join drvFMS on (tblActualMilkingCowData.FarmId = drvFMS.FarmId) and
>(tblActualMilkingCowData.Season = drvFMS.Season) and
>(tblActualMilkingCowData.Period = drvFMS.Period)
>where (month(tblActualMilkingCowData.[Date]) = @.ThisPeriod_Month)
>group by drvFMS.FarmId, drvFMS.Season, drvFMS.Period
>select #B1.FarmId, #B1.Season, #B1.Period, aAvgCowsMilked, aDmc1avg,
>aDmc2avg, aDmc3avg, aDmc4avg, aDmc5avg, aDmc6avg, aDmc7avg, (aCrop1avg +
>aCrop2avg + aCrop3avg + aCrop4avg + aCrop5avg) as aCrops, (round(aDmc1avg,1
)
>+ round(aDmc2avg,1) + round(aDmc3avg,1) + round(aDmc4avg,1) +
>round(aDmc5avg,1) + round(aDmc6avg,1) + round(aDmc7avg,1) +
>round(aCrop1avg,1) + round(aCrop2avg,1) + round(aCrop3avg,1) +
>round(aCrop4avg,1) + round(aCrop5avg,1)) as aTotalDMConsumed
>into #B2
>from #B1
>select #B2.*, vwFarmAdditionalInfo.dmc1Description,
>vwFarmAdditionalInfo.dmc2Description, vwFarmAdditionalInfo.dmc3Description,
>vwFarmAdditionalInfo.dmc4Description, vwFarmAdditionalInfo.dmc5Description,
>vwFarmAdditionalInfo.dmc6Description, vwFarmAdditionalInfo.dmc7Description
>into #B3
>from #B2
>inner join vwFarmAdditionalInfo
>on (vwFarmAdditionalInfo.FarmId = #B2.FarmId)
>declare @.aAvgCowsMilked as smallint
>declare @.aDmc1 as decimal(9,3)
>declare @.aDmc2 as decimal(9,3)
>declare @.aDmc3 as decimal(9,3)
>declare @.aDmc4 as decimal(9,3)
>declare @.aDmc5 as decimal(9,3)
>declare @.aDmc6 as decimal(9,3)
>declare @.aDmc7 as decimal(9,3)
>declare @.aCrops as decimal(9,3)
>declare @.aTotalDMConsumed as decimal(9,3)
>declare @.dmc1Desc as varchar(80)
>declare @.dmc2Desc as varchar(80)
>declare @.dmc3Desc as varchar(80)
>declare @.dmc4Desc as varchar(80)
>declare @.dmc5Desc as varchar(80)
>declare @.dmc6Desc as varchar(80)
>declare @.dmc7Desc as varchar(80)
>declare a_Cursor cursor for
>select * from #B3
>open a_Cursor
>fetch next from a_Cursor
>into @.F, @.S, @.P, @.aAvgCowsMilked, @.aDmc1, @.aDmc2, @.aDmc3, @.aDmc4, @.aDmc5,
>@.aDmc6, @.aDmc7, @.aCrops, @.aTotalDMConsumed, @.dmc1Desc, @.dmc2Desc, @.dmc3Desc
,
>@.dmc4Desc, @.dmc5Desc, @.dmc6Desc, @.dmc7Desc
>while @.@.fetch_status = 0
>begin
> update nmlFMS set aAvgCowsMilked = @.aAvgCowsMilked, aDmc1 = @.aDmc1, aDmc2
=
>@.aDmc2, aDmc3 = @.aDmc3, aDmc4 = @.aDmc4, aDmc5 = @.aDmc5, aDmc6 = @.aDmc6,
>aDmc7 = @.aDmc7, aCrops = @.aCrops, aTotalDMConsumed = @.aTotalDMConsumed,
>lblDmc1 = @.dmc1Desc, lblDmc2 = @.dmc2Desc, lblDmc3 = @.dmc3Desc, lblDmc4 =
>@.dmc4Desc, lblDmc5 = @.dmc5Desc, lblDmc6 = @.dmc6Desc, lblDmc7 = @.dmc7Desc
> where (FarmId = @.F) and (Season = @.S) and (Period = @.P)
> fetch next from a_Cursor
> into @.F, @.S, @.P, @.aAvgCowsMilked, @.aDmc1, @.aDmc2, @.aDmc3, @.aDmc4, @.aDmc5,
>@.aDmc6, @.aDmc7, @.aCrops, @.aTotalDMConsumed, @.dmc1Desc, @.dmc2Desc, @.dmc3Desc
,
>@.dmc4Desc, @.dmc5Desc, @.dmc6Desc, @.dmc7Desc
>end
>close a_Cursor
>deallocate a_Cursor
>----
-
>----
-
>---
>/* aSeasonToDateMS, aMonthTotalMS */
>select drvFMS.FarmId, drvFMS.Season, drvFMS.Period,
>month(tblActualProductionData.[Date]) as [Month],
>(tblActualProductionData.Fat + tblActualProductionData.Protein) as
>MilkSolids
>into #C1
>from tblActualProductionData
>inner join drvFMS on (tblActualProductionData.FarmId = drvFMS.FarmId) and
>(tblActualProductionData.Season = drvFMS.Season) and
>(tblActualProductionData.Period = drvFMS.Period)
>select #C1.FarmId, #C1.Season, #C1.Period, sum(#C1.MilkSolids) as
>aMonthTotalMS
>into #C2
>from #C1
>where (#C1.[Month] = @.ThisPeriod_Month)
>group by FarmId, Season, Period
>select #C1.FarmId, #C1.Season, #C1.Period, sum(#C1.MilkSolids) as
>aSeasonToDateMS
>into #C3
>from #C1
>where (#C1.[Month] <= @.ThisPeriod_Month)
>group by FarmId, Season, Period
>select #C2.FarmId, #C2.Season, #C2.Period, #C2.aMonthTotalMS,
>#C3.aSeasonToDateMS
>into #C4
>from #C2
>inner join #C3 on (#C3.FarmId = #C2.FarmId)
>declare @.aMonthTotalMS as decimal(9,3)
>declare @.aSeasonToDateMS as decimal(9,3)
>declare a_Cursor cursor for
>select * from #C4
>open a_Cursor
>fetch next from a_Cursor
>into @.F, @.S, @.P, @.aMonthTotalMS, @.aSeasonToDateMS
>while @.@.fetch_status = 0
>begin
> update nmlFMS set aMonthTotalMS = @.aMonthTotalMS, aSeasonToDateMS =
>@.aSeasonToDateMS
> where (FarmId = @.F) and (Season = @.S) and (Period = @.P)
> fetch next from a_Cursor
> into @.F, @.S, @.P, @.aMonthTotalMS, @.aSeasonToDateMS
>end
>close a_Cursor
>deallocate a_Cursor
>----
-
>----
-
>---
>/* aFeedConvEff, [aFeedAlloc/LiveWeight], [aMS/Cow/Day], [aMthMS/Ha] */
>select #B3.FarmId, #B3.Season, #B3.Period, #B3.aTotalDMConsumed,
>sum(tblActualProductionData.Days) as [Days]
>into #D1
>from #B3
>inner join tblActualProductionData on (tblActualProductionData.FarmId =
>#B3.FarmId) and (tblActualProductionData.Season = #B3.Season) and
>(tblActualProductionData.Period = #B3.Period) and
>(month(tblActualProductionData.[Date]) = @.ThisPeriod_Month)
>group by #B3.FarmId, #B3.Season, #B3.Period, #B3.aTotalDMConsumed
>select #D1.FarmId, #D1.Season, #D1.Period, #D1.aTotalDMConsumed, #D1.[Days],
>avg(cast(tblActualPastureData.FarmSize as decimal(9,3))) as aAverageArea
>into #D2
>from #D1
>inner join tblActualPastureData on (tblActualPastureData.FarmId =
>#D1.FarmId) and (tblActualPastureData.Season = #D1.Season) and
>(tblActualPastureData.Period = #D1.Period) and
>(month(tblActualPastureData.[Date]) = @.ThisPeriod_Month)
>group by #D1.FarmId, #D1.Season, #D1.Period, #D1.aTotalDMConsumed,
>#D1.[Days]
>select #D2.*, #B1.aAvgCowsMilked, #C4.aMonthTotalMS,
>tblActualFarmData.FarmSize as 'aAvailableArea',
>tblFarmAdditionalInfo.Liveweight
>into #D3
>from #D2
>inner join #B1 on (#B1.FarmId = #D2.FarmId) and (#B1.Season = #D2.Season)
>and (#B1.Period = #D2.Period)
>inner join #C4 on (#C4.FarmId = #D2.FarmId) and (#C4.Season = #D2.Season)
>and (#C4.Period = #D2.Period)
>inner join tblActualFarmData on (tblActualFarmData.FarmId = #D2.FarmId) an
d
>(tblActualFarmData.Season = #D2.Season) and (tblActualFarmData.Period =
>#D2.Period)
>inner join tblFarmAdditionalInfo on (tblFarmAdditionalInfo.FarmId =
>#D2.FarmId)
>
>select #D3.FarmId, #D3.Season, #D3.Period, ((#D3.aTotalDMConsumed * #D3.Day
s
>* (#D3.aAvgCowsMilked / #D3.aAverageArea)) / (#D3.aMonthTotalMS /
>#D3.aAvailableArea)) as aFeedConvEff, (#D3.aTotalDMConsumed / #D3.Liveweigh
t
>* 100) as [aFeedAlloc/LiveWeight], (#D3.aMonthTotalMS / #D3.aAvgCowsMilked /
>#D3.Days) as [aMS/Cow/Day], (#D3.aMonthTotalMS / #D3.aAvailableArea) as
>[aMthMS/Ha]
>into #D4
>from #D3
>declare @.aFeedConvEff as decimal(9,3)
>declare @.aFeedAllocLiveWeight as decimal(9,3)
>declare @.aMSCowDay as decimal (9,3)
>declare @.aMthMSHa as decimal(9,3)
>declare a_Cursor cursor for
>select * from #D4
>open a_Cursor
>fetch next from a_Cursor
>into @.F, @.S, @.P, @.aFeedConvEff, @.aFeedAllocLiveWeight, @.aMSCowDay, @.aMthMSH
a
>while @.@.fetch_status = 0
>begin
> update nmlFMS set aFeedConvEff = cast(@.aFeedConvEff as decimal(6,4)),
>[aFeedAlloc/LiveWeight] = cast(@.aFeedAllocLiveWeight as decimal(6,4)),
>[aMS/Cow/Day] = cast(@.aMSCowDay as decimal(6,4)), [aMthMS/Ha] =
>cast(@.aMthMSHa as decimal(9,3))
> where (FarmId = @.F) and (Season = @.S) and (Period = @.P)
> fetch next from a_Cursor
> into @.F, @.S, @.P, @.aFeedConvEff, @.aFeedAllocLiveWeight, @.aMSCowDay,
>@.aMthMSHa
>end
>close a_Cursor
>deallocate a_Cursor
>----
-
>----
-
>---
>/* [aMthFeedCosts/KgMS], rSeasonMS, rSeasonSuppUsed, rSeasonFeedCosts,
>rSeasonGrossMargin */
>select #D1.FarmId, #D1.Season, #D1.Period, #D1.Days,
>cast(avg(tblActualMilkingCowData.NoOfCows) as smallint) as aAvgCowsMilked,
>avg(tblActualMilkingCowData.dmc2 * tblFarmAdditionalInfo.dmc2$) as aDmc2$,
>avg(tblActualMilkingCowData.dmc3 * tblFarmAdditionalInfo.dmc3$) as aDmc3$,
>avg(tblActualMilkingCowData.dmc4 * tblFarmAdditionalInfo.dmc4$) as aDmc4$,
>avg(tblActualMilkingCowData.dmc5 * tblFarmAdditionalInfo.dmc5$) as aDmc5$,
>avg(tblActualMilkingCowData.dmc6 * tblFarmAdditionalInfo.dmc6$) as aDmc6$,
>avg(tblActualMilkingCowData.dmc7 * tblFarmAdditionalInfo.dmc7$) as aDmc7$,
>avg(tblActualMilkingCowData.Crop1 * tblFarmAdditionalInfo.Crop1$) as
>aCrop1$, avg(tblActualMilkingCowData.Crop2 * tblFarmAdditionalInfo.Crop2$)
>as aCrop2$, avg(tblActualMilkingCowData.Crop3 *
>tblFarmAdditionalInfo.Crop3$) as aCrop3$, avg(tblActualMilkingCowData.Crop4
>* tblFarmAdditionalInfo.Crop4$) as aCrop4$,
>avg(tblActualMilkingCowData.Crop5 * tblFarmAdditionalInfo.Crop5$) as aCrop5
$
>into #E1
>from #D1
>inner join tblActualMilkingCowData on (tblActualMilkingCowData.FarmId =
>#D1.FarmId) and (tblActualMilkingCowData.Season = #D1.Season) and
>(tblActualMilkingCowData.Period = #D1.Period) and
>(month(tblActualMilkingCowData.[Date]) = @.ThisPeriod_Month)
>inner join tblFarmAdditionalInfo on (tblFarmAdditionalInfo.FarmId =
>#D1.FarmId)
>group by #D1.FarmId, #D1.Season, #D1.Period, #D1.Days
>create index C2_ndx on #C2 (FarmId, Season, Period)
>select #E1.FarmId, #E1.Season, #E1.Period, #E1.Days, aAvgCowsMilked,
>#C2.aMonthTotalMS, (aDmc2$ + aDmc3$ + aDmc4$ + aDmc5$ + aDmc6$ + aDmc7$ +
>aCrop1$ + aCrop2$ + aCrop3$ + aCrop4$ + aCrop5$) as aMthFeedCosts,
>tblActualFarmData.Adjistment$ as aGrazingCosts$, (tblForecastFarmData.Fat +
>tblForecastFarmData.Protein) as rSeasonMS, (tblForecastFarmData.IOFC$ -
>tblForecastFarmData.CowCosts$ - tblForecastFarmData.GrossMargin$) as
>aIrrigationCosts$, (tblForecastFarmData.dmc2fed +
>tblForecastFarmData.dmc3fed + tblForecastFarmData.dmc4fed +
>tblForecastFarmData.dmc5fed + tblForecastFarmData.dmc6fed +
>tblForecastFarmData.dmc7fed) as rSeasonSuppUsed,
>(tblForecastFarmData.Concentrates$ + tblForecastFarmData.Fodder$ +
>tblForecastFarmData.Nitrogen$ + tblForecastFarmData.Adjistment$ +
>tblForecastFarmData.Crop1$ + tblForecastFarmData.Crop2$ +
>tblForecastFarmData.Crop3$ + tblForecastFarmData.Crop4$ +
>tblForecastFarmData.Crop5$) as rSeasonFeedCosts,
>cast(tblForecastFarmData.GrossMargin$ as decimal(11,3)) as
>rSeasonGrossMargin
>into #E2
>from #E1
>inner join #C2 on (#C2.FarmId = #E1.FarmId) and (#C2.Season = #E1.Season)
>and (#C2.Period = #E1.Period)
>inner join tblActualFarmData on (tblActualFarmData.FarmId = #E1.FarmId) and
>(tblActualFarmData.Season = #E1.Season) and (tblActualFarmData.Period =
>#E1.Period)
>inner join tblForecastFarmData on (tblForecastFarmData.FarmId = #E1.FarmId)
>and (tblForecastFarmData.Season = #E1.Season) and
>(tblForecastFarmData.Period = #E1.Period)
>select #E2.*, (((#E2.aMthFeedCosts * #E2.aAvgCowsMilked * #E2.Days) /
>#E2.aMonthTotalMS) + (#E2.aGrazingCosts$ / #E2.rSeasonMS) +
>(#E2.aIrrigationCosts$ / #E2.rSeasonMS)) as aMthFeedCostsKgMS
>into #E3
>from #E2
>declare @.rSeasonMS as integer
>declare @.aMthFeedCostsKgMS as decimal(9,4)
>declare @.rSeasonSuppUsed as decimal(6,2)
>declare @.rSeasonFeedCosts as decimal(8,2)
>declare @.rSeasonGrossMargin as decimal(11,3)
>declare a_Cursor cursor for
>select FarmId, Season, Period, rSeasonMS, aMthFeedCostsKgMS,
>rSeasonSuppUsed, rSeasonFeedCosts, rSeasonGrossMargin from #E3
>open a_Cursor
>fetch next from a_Cursor
>into @.F, @.S, @.P, @.rSeasonMS, @.aMthFeedCostsKgMS, @.rSeasonSuppUsed,
>@.rSeasonFeedCosts, @.rSeasonGrossMargin
>while @.@.fetch_status = 0
>begin
> update nmlFMS set rSeasonMS = @.rSeasonMS, [aMthFeedCosts/KgMS] =
>@.aMthFeedCostsKgMS, rSeasonSuppUsed = @.rSeasonSuppUsed, rSeasonFeedCosts =
>@.rSeasonFeedCosts, rSeasonGrossMargin = @.rSeasonGrossMargin
> where (FarmId = @.F) and (Season = @.S) and (Period = @.P)
> fetch next from a_Cursor
> into @.F, @.S, @.P, @.rSeasonMS, @.aMthFeedCostsKgMS, @.rSeasonSuppUsed,
>@.rSeasonFeedCosts, @.rSeasonGrossMargin
>end
>close a_Cursor
>deallocate a_Cursor
>----
-
>----
-
>---
>/* tSeasonMS, tSeasonSuppUsed, tSeasonFeedCosts, tSeasonGrossMargin */
>select drvFMS.FarmId, drvFMS.Season, drvFMS.Period,
>tblOriginalFarmData.Adjistment$ as oGrazingCosts$, (tblOriginalFarmData.Fat
>+ tblOriginalFarmData.Protein) as oSeasonMS, (tblOriginalFarmData.IOFC$ -
>tblOriginalFarmData.CowCosts$ - tblOriginalFarmData.GrossMargin$) as
>oIrrigationCosts$, (tblOriginalFarmData.dmc2fed +
>tblOriginalFarmData.dmc3fed + tblOriginalFarmData.dmc4fed +
>tblOriginalFarmData.dmc5fed + tblOriginalFarmData.dmc6fed +
>tblOriginalFarmData.dmc7fed) as oSeasonSuppUsed,
>(tblOriginalFarmData.Concentrates$ + tblOriginalFarmData.Fodder$ +
>tblOriginalFarmData.Nitrogen$ + tblOriginalFarmData.Crop1$ +
>tblOriginalFarmData.Crop2$ + tblOriginalFarmData.Crop3$ +
>tblOriginalFarmData.Crop4$ + tblOriginalFarmData.Crop5$) as
>oSeasonFeedCosts, (cast(tblOriginalFarmData.GrossMargin$ as decimal(11,3))
-
>(cast(tblOriginalFarmData.IOFC$ as decimal(11,3)) -
>cast(tblOriginalFarmData.CowCosts$ as decimal(11,3)) -
>cast(tblOriginalFarmData.GrossMargin$ as decimal(11,3)))) as
>oSeasonGrossMargin
>into #F1
>from drvFMS
>inner join tblOriginalFarmData on (tblOriginalFarmData.FarmId =
>drvFMS.FarmId) and (tblOriginalFarmData.Season = drvFMS.Season)
>select #F1.FarmId, #F1.Season, #F1.Period, #F1.oSeasonMS as tSeasonMS,
>#F1.oSeasonSuppUsed as tSeasonSuppUsed, (#F1.oSeasonFeedCosts +
>#F1.oGrazingCosts$ + #F1.oIrrigationCosts$) as tSeasonFeedCosts,
>#F1.oSeasonGrossMargin as tSeasonGrossMargin
>into #F2
>from #F1
>declare @.tSeasonMS as integer
>declare @.tSeasonSuppUsed as decimal(6,2)
>declare @.tSeasonFeedCosts as decimal(8,2)
>declare @.tSeasonGrossMargin as decimal(11,3)
>declare a_Cursor cursor for
>select FarmId, Season, Period, tSeasonMS, tSeasonSuppUsed, tSeasonFeedCosts
,
>tSeasonGrossMargin from #F2
>open a_Cursor
>fetch next from a_Cursor
>into @.F, @.S, @.P, @.tSeasonMS, @.tSeasonSuppUsed, @.tSeasonFeedCosts,
>@.tSeasonGrossMargin
>while @.@.fetch_status = 0
>begin
> update nmlFMS set tSeasonMS = @.tSeasonMS, tSeasonSuppUsed =
>@.tSeasonSuppUsed, tSeasonFeedCosts = @.tSeasonFeedCosts, tSeasonGrossMargin
=
>@.tSeasonGrossMargin
> where (FarmId = @.F) and (Season = @.S) and (Period = @.P)
> fetch next from a_Cursor
> into @.F, @.S, @.P, @.tSeasonMS, @.tSeasonSuppUsed, @.tSeasonFeedCosts,
>@.tSeasonGrossMargin
>end
>close a_Cursor
>deallocate a_Cursor
>----
-
>----
-
>---
>/* tMthEndAvgCover, tPastureGrowth */
>select drvFMS.FarmId, drvFMS.Season, drvFMS.Period,
>tblForecastPastureData.Cover as tMthEndAvgCover
>into #G1
>from drvFMS
>inner join tblForecastPastureData on (tblForecastPastureData.FarmId =
>drvFMS.FarmId) and (tblForecastPastureData.Season = drvFMS.Season) and
>(tblForecastPastureData.Period = @.PrevPeriod) and
>(month(tblForecastPastureData.[Date]) = @.ThisPeriod_Month) and
>(day(tblForecastPastureData.[Date]) = 21)
>select #G1.*, tblForecastPastureData.Growth
>into #G2
>from #G1
>inner join tblForecastPastureData on (tblForecastPastureData.FarmId =
>#G1.FarmId) and (tblForecastPastureData.Season = #G1.Season) and
>(tblForecastPastureData.Period = @.PrevPeriod) and
>(month(tblForecastPastureData.[Date]) = @.ThisPeriod_Month)
>select #G2.FarmId, #G2.Season, #G2.Period, #G2.tMthEndAvgCover,
>avg(cast(#G2.Growth as decimal(9,3))) as tPastureGrowth
>into #G3
>from #G2
>group by #G2.FarmId, #G2.Season, #G2.Period, #G2.tMthEndAvgCover
>declare @.tMthEndAvgCover as smallint
>declare @.tPastureGrowth as decimal(9,3)
>declare a_Cursor cursor for
>select FarmId, Season, Period, tMthEndAvgCover, tPastureGrowth from #G3
>open a_Cursor
>fetch next from a_Cursor
>into @.F, @.S, @.P, @.tMthEndAvgCover, @.tPastureGrowth
>while @.@.fetch_status = 0
>begin
> update nmlFMS set tMthEndAvgCover = @.tMthEndAvgCover, tPastureGrowth =
>@.tPastureGrowth
> where (FarmId = @.F) and (Season = @.S) and (Period = @.P)
> fetch next from a_Cursor
> into @.F, @.S, @.P, @.tMthEndAvgCover, @.tPastureGrowth
>end
>close a_Cursor
>deallocate a_Cursor
>----
-
>----
-
>---
>/* tAvgCowsMilked, tDmc1, tDmc2, tDmc3, tDmc4, tDmc5, tDmc6, tDmc7, tCrops,
>tTotalDMConsumed */
>select drvFMS.FarmId, drvFMS.Season, drvFMS.Period,
>avg(cast(tblForecastMilkingCowData.NoOfCows as decimal(9,3))) as
>tAvgCowsMilked, avg(cast(tblForecastMilkingCowData.dmc1 as decimal(9,3))) a
s
>tDmc1avg, avg(cast(tblForecastMilkingCowData.dmc2 as decimal(9,3))) as
>tDmc2avg, avg(cast(tblForecastMilkingCowData.dmc3 as decimal(9,3))) as
>tDmc3avg, avg(cast(tblForecastMilkingCowData.dmc4 as decimal(9,3))) as
>tDmc4avg, avg(cast(tblForecastMilkingCowData.dmc5 as decimal(9,3))) as
>tDmc5avg, avg(cast(tblForecastMilkingCowData.dmc6 as decimal(9,3))) as
>tDmc6avg, avg(cast(tblForecastMilkingCowData.dmc7 as decimal(9,3))) as
>tDmc7avg, avg(cast(tblForecastMilkingCowData.Crop1 as decimal(9,3))) as
>tCrop1avg, avg(cast(tblForecastMilkingCowData.Crop2 as decimal(9,3))) as
>tCrop2avg, avg(cast(tblForecastMilkingCowData.Crop3 as decimal(9,3))) as
>tCrop3avg, avg(cast(tblForecastMilkingCowData.Crop4 as decimal(9,3))) as
>tCrop4avg, avg(cast(tblForecastMilkingCowData.Crop5 as decimal(9,3))) as
>tCrop5avg
>into #H1
>from tblForecastMilkingCowData
>inner join drvFMS on (tblForecastMilkingCowData.FarmId = drvFMS.FarmId) and
>(tblForecastMilkingCowData.Season = drvFMS.Season) and
>(tblForecastMilkingCowData.Period = @.PrevPeriod)
>where (month(tblForecastMilkingCowData.[Date]) = @.ThisPeriod_Month)
>group by drvFMS.FarmId, drvFMS.Season, drvFMS.Period
>select #H1.*, (tCrop1avg + tCrop2avg + tCrop3avg + tCrop4avg + tCrop5avg) a
s
>tCrops, (round(tDmc1avg,1) + round(tDmc2avg,1) + round(tDmc3avg,1) +
>round(tDmc4avg,1) + round(tDmc5avg,1) + round(tDmc6avg,1) +
>round(tDmc7avg,1) + round(tCrop1avg,1) + round(tCrop2avg,1) +
>round(tCrop3avg,1) + round(tCrop4avg,1) + round(tCrop5avg,1)) as
>tTotalDMConsumed
>into #H2
>from #H1
>declare @.tAvgCowsMilked as smallint
>declare @.tDmc1 as decimal(9,3)
>declare @.tDmc2 as decimal(9,3)
>declare @.tDmc3 as decimal(9,3)
>declare @.tDmc4 as decimal(9,3)
>declare @.tDmc5 as decimal(9,3)
>declare @.tDmc6 as decimal(9,3)
>declare @.tDmc7 as decimal(9,3)
>declare @.tCrops as decimal(9,3)
>declare @.tTotalDMConsumed as decimal(9,3)
>declare a_Cursor cursor for
>select FarmId, Season, Period, tAvgCowsMilked, tDmc1avg, tDmc2avg, tDmc3avg
,
>tDmc4avg, tDmc5avg, tDmc6avg, tDmc7avg, tCrops, tTotalDMConsumed from #H2
>open a_Cursor
>fetch next from a_Cursor
>into @.F, @.S, @.P, @.tAvgCowsMilked, @.tDmc1, @.tDmc2, @.tDmc3, @.tDmc4, @.tDmc5,
>@.tDmc6, @.tDmc7, @.tCrops, @.tTotalDMConsumed
>while @.@.fetch_status = 0
>begin
> update nmlFMS set tAvgCowsMilked = @.tAvgCowsMilked, tDmc1 = @.tDmc1, tDmc2
=
>@.tDmc2, tDmc3 = @.tDmc3, tDmc4 = @.tDmc4, tDmc5 = @.tDmc5, tDmc6 = @.tDmc6,
>tDmc7 = @.tDmc7, tCrops = @.tCrops, tTotalDMConsumed = @.tTotalDMConsumed
> where (FarmId = @.F) and (Season = @.S) and (Period = @.P)
> fetch next from a_Cursor
> into @.F, @.S, @.P, @.tAvgCowsMilked, @.tDmc1, @.tDmc2, @.tDmc3, @.tDmc4, @.tDmc5,
>@.tDmc6, @.tDmc7, @.tCrops, @.tTotalDMConsumed
>end
>close a_Cursor
>deallocate a_Cursor
>----
-
>----
-
>---
>/* tSeasonToDateMS, tMonthTotalMS, [tMS/Cow/Day] */
>select drvFMS.FarmId, drvFMS.Season, drvFMS.Period,
>month(tblForecastProductionData.[Date]) as [Month],
>(tblForecastProductionData.Fat + tblForecastProductionData.Protein) as
>MilkSolids, tblForecastProductionData.Days
>into #I1
>from tblForecastProductionData
>inner join drvFMS on (tblForecastProductionData.FarmId = drvFMS.FarmId) and
>(tblForecastProductionData.Season = drvFMS.Season) and
>(tblForecastProductionData.Period = @.PrevPeriod)
>select #I1.FarmId, #I1.Season, #I1.Period, sum(#I1.MilkSolids) as
>tMonthTotalMS, sum(#I1.Days) as [Days]
>into #I2
>from #I1
>where (#I1.[Month] = @.ThisPeriod_Month)
>group by FarmId, Season, Period
>create index I2_ndx on #I2 (FarmId, Season, Period)
>create index H2_ndx on #H2 (FarmId, Season, Period)
>select #I1.*, dbo.fnMonthToPeriod([Month]) as MonthPeriod,
>#I2.tMonthTotalMS, cast((#I2.tMonthTotalMS / #H2.tAvgCowsMilked / #I2.Days)
>as decimal(6,4)) as [tMS/Cow/Day]
>into #I3
>from #I1
>inner join #I2 on #I2.FarmId = #I1.FarmId and #I2.Season = #I1.Season and
>#I2.Period = #I1.Period
>inner join #H2 on #H2.FarmId = #I1.FarmId and #H2.Season = #I1.Season and
>#H2.Period = #I1.Period
>select #I3.FarmId, #I3.Season, #I3.Period, #I3.tMonthTotalMS, [tMS/Cow/Day],
>sum(#I3.MilkSolids) as tSeasonToDateMS
>into #I4
>from #I3
>where #I3.MonthPeriod <= #I3.Period
>group by FarmId, Season, Period, tMonthTotalMS, [tMS/Cow/Day]
>declare @.tMonthTotalMS as decimal(9,3)
>declare @.tSeasonToDateMS as decimal(9,3)
>declare @.tMSCowDay as decimal(6,4)
>declare a_Cursor cursor for
>select * from #I4
>open a_Cursor
>fetch next from a_Cursor
>into @.F, @.S, @.P, @.tMonthTotalMS, @.tMSCowDay, @.tSeasonToDateMS
>while @.@.fetch_status = 0
>begin
> update nmlFMS set tMonthTotalMS = @.tMonthTotalMS, [tMS/Cow/Day] =
>@.tMSCowDay, tSeasonToDateMS = @.tSeasonToDateMS
> where (FarmId = @.F) and (Season = @.S) and (Period = @.P)
> fetch next from a_Cursor
> into @.F, @.S, @.P, @.tMonthTotalMS, @.tMSCowDay, @.tSeasonToDateMS
>end
>close a_Cursor
>deallocate a_Cursor
>----
-
>----
-
>---
>/* tFeedConvEff, [tFeedAlloc/LiveWeight], [tMthMS/Ha] */
>select #H2.FarmId, #H2.Season, #H2.Period, #H2.tTotalDMConsumed,
>sum(tblForecastProductionData.Days) as [Days]
>into #J1
>from #H2
>inner join tblForecastProductionData on (tblForecastProductionData.FarmId =
>#H2.FarmId) and (tblForecastProductionData.Season = #H2.Season) and
>(tblForecastProductionData.Period = @.PrevPeriod) and
>(month(tblForecastProductionData.[Date]) = @.ThisPeriod_Month)
>group by #H2.FarmId, #H2.Season, #H2.Period, #H2.tTotalDMConsumed
>select #J1.FarmId, #J1.Season, #J1.Period, #J1.tTotalDMConsumed, #J1.Days,
>avg(cast(tblForecastPastureData.FarmSize as decimal(9,3))) as tAverageArea
>into #J2
>from #J1
>inner join tblForecastPastureData on (tblForecastPastureData.FarmId =
>#J1.FarmId) and (tblForecastPastureData.Season = #J1.Season) and
>(tblForecastPastureData.Period = @.PrevPeriod) and
>(month(tblForecastPastureData.[Date]) = @.ThisPeriod_Month)
>group by #J1.FarmId, #J1.Season, #J1.Period, #J1.tTotalDMConsumed,
>#J1.[Days]
>select #J2.*, #H2.tAvgCowsMilked, #I4.tMonthTotalMS,
>tblForecastFarmData.FarmSize as 'tAvailableArea',
>tblFarmAdditionalInfo.Liveweight
>into #J3
>from #J2
>inner join #H2 on (#H2.FarmId = #J2.FarmId) and (#H2.Season = #J2.Season)
>and (#H2.Period = #J2.Period)
>inner join #I4 on (#I4.FarmId = #J2.FarmId) and (#I4.Season = #J2.Season)
>and (#I4.Period = #J2.Period)
>inner join tblForecastFarmData on (tblForecastFarmData.FarmId = #J2.FarmId)
>and (tblForecastFarmData.Season = #J2.Season) and
>(tblForecastFarmData.Period = #J2.Period)
>inner join tblFarmAdditionalInfo on (tblFarmAdditionalInfo.FarmId =
>#J2.FarmId)
>select #J3.FarmId, #J3.Season, #J3.Period, ((#J3.tTotalDMConsumed * #J3.Day
s
>* (#J3.tAvgCowsMilked / #J3.tAverageArea)) / (#J3.tMonthTotalMS /
>#J3.tAvailableArea)) as tFeedConvEff, (#J3.tTotalDMConsumed / #J3.Liveweigh
t
>* 100) as [tFeedAlloc/LiveWeight], (#J3.tMonthTotalMS / #J3.tAvailableArea)
>as [tMthMS/Ha]
>into #J4
>from #J3
>declare @.tFeedConvEff as decimal(9,3)
>declare @.tFeedAllocLiveWeight as decimal(9,3)
>declare @.tMthMSHa as decimal(9,3)
>declare a_Cursor cursor for
>select * from #J4
>open a_Cursor
>fetch next from a_Cursor
>into @.F, @.S, @.P, @.tFeedConvEff, @.tFeedAllocLiveWeight, @.tMthMSHa
>while @.@.fetch_status = 0
>begin
> update nmlFMS set tFeedConvEff = cast(@.tFeedConvEff as decimal(6,4)),
>[tFeedAlloc/LiveWeight] = cast(@.tFeedAllocLiveWeight as decimal(6,4)),
>[tMthMS/Ha] = @.tMthMSHa
> where (FarmId = @.F) and (Season = @.S) and (Period = @.P)
> fetch next from a_Cursor
> into @.F, @.S, @.P, @.tFeedConvEff, @.tFeedAllocLiveWeight, @.tMthMSHa
>end
>close a_Cursor
>deallocate a_Cursor
>----
-
>----
-
>---
>/* [tMthFeedCosts/KgMS] */
>select #J1.FarmId, #J1.Season, #J1.Period, #J1.Days,
>cast(avg(tblForecastMilkingCowData.NoOfCows) as smallint) as tAvgCowsMilked
,
>avg(tblForecastMilkingCowData.dmc2 * tblFarmAdditionalInfo.dmc2$) as tDmc2$
,
>avg(tblForecastMilkingCowData.dmc3 * tblFarmAdditionalInfo.dmc3$) as tDmc3$
,
>avg(tblForecastMilkingCowData.dmc4 * tblFarmAdditionalInfo.dmc4$) as tDmc4$
,
>avg(tblForecastMilkingCowData.dmc5 * tblFarmAdditionalInfo.dmc5$) as tDmc5$
,
>avg(tblForecastMilkingCowData.dmc6 * tblFarmAdditionalInfo.dmc6$) as tDmc6$
,
>avg(tblForecastMilkingCowData.dmc7 * tblFarmAdditionalInfo.dmc7$) as tDmc7$
,
>avg(tblForecastMilkingCowData.Crop1 * tblFarmAdditionalInfo.Crop1$) as
>tCrop1$, avg(tblForecastMilkingCowData.Crop2 * tblFarmAdditionalInfo.Crop2$
)
>as tCrop2$, avg(tblForecastMilkingCowData.Crop3 *
>tblFarmAdditionalInfo.Crop3$) as tCrop3$,
>avg(tblForecastMilkingCowData.Crop4 * tblFarmAdditionalInfo.Crop4$) as
>tCrop4$, avg(tblForecastMilkingCowData.Crop5 * tblFarmAdditionalInfo.Crop5$
)
>as tCrop5$
>into #K1
>from #J1
>inner join tblForecastMilkingCowData on (tblForecastMilkingCowData.FarmId =
>#J1.FarmId) and (tblForecastMilkingCowData.Season = #J1.Season) and
>(tblForecastMilkingCowData.Period = @.PrevPeriod) and
>(month(tblForecastMilkingCowData.[Date]) = @.ThisPeriod_Month)
>inner join tblFarmAdditionalInfo on (tblFarmAdditionalInfo.FarmId =
>#J1.FarmId)
>group by #J1.FarmId, #J1.Season, #J1.Period, #J1.Days
>select #K1.FarmId, #K1.Season, #K1.Period, #K1.Days, #K1.tAvgCowsMilked,
>#I2.tMonthTotalMS, (#K1.tDmc2$ + #K1.tDmc3$ + #K1.tDmc4$ + #K1.tDmc5$ +
>#K1.tDmc6$ + #K1.tDmc7$ + #K1.tCrop1$ + #K1.tCrop2$ + #K1.tCrop3$ +
>#K1.tCrop4$ + #K1.tCrop5$) as tMthFeedCosts, tblForecastFarmData.Adjistment
$
>as tGrazingCosts$, (tblForecastFarmData.Fat + tblForecastFarmData.Protein)
>as tSeasonMS, (tblForecastFarmData.IOFC$ - tblForecastFarmData.CowCosts$ -
>tblForecastFarmData.GrossMargin$) as tIrrigationCosts$,
>nmlFMS.rSeasonSuppUsed, nmlFMS.rSeasonFeedCosts
>into #K2
>from #K1
>inner join #I2 on (#I2.FarmId = #K1.FarmId) and (#I2.Season = #K1.Season)
>and (#I2.Period = #K1.Period)
>inner join tblForecastFarmData on (tblForecastFarmData.FarmId = #K1.FarmId)
>and (tblForecastFarmData.Season = #K1.Season) and
>(tblForecastFarmData.Period = @.PrevPeriod)
>inner join nmlFMS on (nmlFMS.FarmId = #K1.FarmId) and (nmlFMS.Season =
>#K1.Season) and (nmlFMS.Period = #K1.Period)
>select #K2.*, (((#K2.tMthFeedCosts * #K2.tAvgCowsMilked * #K2.Days) /
>#K2.tMonthTotalMS) + (#K2.tGrazingCosts$ / #K2.tSeasonMS) +
>(#K2.tIrrigationCosts$ / #K2.tSeasonMS)) as tMthFeedCostsKgMS
>into #K3
>from #K2
>declare @.tMthFeedCostsKgMS as decimal(9,4)
>declare a_Cursor cursor for
>select FarmId, Season, Period, tMthFeedCostsKgMS from #K3
>open a_Cursor
>fetch next from a_Cursor
>into @.F, @.S, @.P, @.tMthFeedCostsKgMS
>while @.@.fetch_status = 0
>begin
> update nmlFMS set [tMthFeedCosts/KgMS] = @.tMthFeedCostsKgMS
> where (FarmId = @.F) and (Season = @.S) and (Period = @.P)
> fetch next from a_Cursor
> into @.F, @.S, @.P, @.tMthFeedCostsKgMS
>end
>close a_Cursor
>deallocate a_Cursor
>----
-
>----
-
>---
>/* [tSeasonMS/Ha], [rSeasonMS/Ha], [tSeasonPastureHarvest/Ha],
>[rSeasonPastureHarvest/Ha], [tSeasonSuppUsed/Ha], [rSeasonSuppUsed/Ha],
>[tSeasonFeedCosts/Ha], [rSeasonFeedCosts/Ha], [tSeasonGrossMargin/Ha],
>[rSeasonGrossMargin/Ha] */
>select drvFMS.FarmId, drvFMS.Season, drvFMS.Period, (nmlFMS.tSeasonMS /
>tblOriginalFarmData.FarmSize) as [tSeasonMS/Ha], (nmlFMS.rSeasonMS /
>tblForecastFarmData.FarmSize) as [rSeasonMS/Ha],
>tblOriginalFarmData.Dmc1Used as [tSeasonPastureHarvest/Ha],
>tblForecastFarmData.Dmc1Used as [rSeasonPastureHarvest/Ha],
>(nmlFMS.tSeasonSuppUsed / tblOriginalFarmData.FarmSize) as
>[tSeasonSuppUsed/Ha], (nmlFMS.rSeasonSuppUsed /
>tblForecastFarmData.FarmSize) as [rSeasonSuppUsed/Ha],
>(nmlFMS.tSeasonFeedCosts / tblOriginalFarmData.FarmSize) as
>[tSeasonFeedCosts/Ha], (nmlFMS.rSeasonFeedCosts /
>tblForecastFarmData.FarmSize) as [rSeasonFeedCosts/Ha],
>(nmlFMS.tSeasonGrossMargin / tblOriginalFarmData.FarmSize) as
>[tSeasonGrossMargin/Ha], (nmlFMS.rSeasonGrossMargin /
>tblForecastFarmData.FarmSize) as [rSeasonGrossMargin/Ha]
>into #L1
>from drvFMS
>inner join nmlFMS on (nmlFMS.FarmId = drvFMS.FarmId) and (nmlFMS.Season =
>drvFMS.Season) and (nmlFMS.Period = drvFMS.Period)
>inner join tblOriginalFarmData on (tblOriginalFarmData.FarmId =
>drvFMS.FarmId) and (tblOriginalFarmData.Season = drvFMS.Season)
>inner join tblForecastFarmData on (tblForecastFarmData.FarmId =
>drvFMS.FarmId) and (tblForecastFarmData.Season = drvFMS.Season) and
>(tblForecastFarmData.Period = drvFMS.Period)
>declare @.tSeasonMSHa as integer
>declare @.rSeasonMSHa as integer
>declare @.tSeasonPastureHarvestHa as decimal(9,3)
>declare @.rSeasonPastureHarvestHa as decimal(9,3)
>declare @.tSeasonSuppUsedHa as decimal(9,3)
>declare @.rSeasonSuppUsedHa as decimal(9,3)
>declare @.tSeasonFeedCostsHa as decimal(9,3)
>declare @.rSeasonFeedCostsHa as decimal(9,3)
>declare @.tSeasonGrossMarginHa as decimal(9,3)
>declare @.rSeasonGrossMarginHa as decimal(9,3)
>declare a_Cursor cursor for select * from #L1
>open a_Cursor
>fetch next from a_Cursor
>into @.F, @.S, @.P, @.tSeasonMSHa, @.rSeasonMSHa, @.tSeasonPastureHarvestHa,
>@.rSeasonPastureHarvestHa, @.tSeasonSuppUsedHa, @.rSeasonSuppUsedHa,
>@.tSeasonFeedCostsHa, @.rSeasonFeedCostsHa, @.tSeasonGrossMarginHa,
>@.rSeasonGrossMarginHa
>while @.@.fetch_status = 0
>begin
> update nmlFMS set [tSeasonMS/Ha] = @.tSeasonMSHa, [rSeasonMS/Ha] =
>@.rSeasonMSHa, [tSeasonPastureHarvest/Ha] = @.tSeasonPastureHarvestHa,
>[rSeasonPastureHarvest/Ha] = @.rSeasonPastureHarvestHa, [tSeasonSuppUsed/Ha]
>= @.tSeasonSuppUsedHa, [rSeasonSuppUsed/Ha] = @.rSeasonSuppUsedHa,
>[tSeasonFeedCosts/Ha] = @.tSeasonFeedCostsHa, [rSeasonFeedCosts/Ha] =
>@.rSeasonFeedCostsHa, [tSeasonGrossMargin/Ha] = @.tSeasonGrossMarginHa,
>[rSeasonGrossMargin/Ha] = @.rSeasonGrossMarginHa
> where (FarmId = @.F) and (Season = @.S) and (Period = @.P)
> fetch next from a_Cursor
> into @.F, @.S, @.P, @.tSeasonMSHa, @.rSeasonMSHa, @.tSeasonPastureHarvestHa,
>@.rSeasonPastureHarvestHa, @.tSeasonSuppUsedHa, @.rSeasonSuppUsedHa,
>@.tSeasonFeedCostsHa, @.rSeasonFeedCostsHa, @.tSeasonGrossMarginHa,
>@.rSeasonGrossMarginHa
>end
>close a_Cursor
>deallocate a_Cursor
>----
-
>----
-
>---
>/* [tSeasonMS/Cow], [rSeasonMS/Cow], [tSeasonPastureHarvest/Cow],
>[rSeasonPastureHarvest/Cow], [tSeasonSuppUsed/Cow], [rSeasonSuppUsed/Cow],
>[tSeasonFeedCosts/Cow], [rSeasonFeedCosts/Cow], [tSeasonGrossMargin/Cow],
>[rSeasonGrossMargin/Cow] */
>select drvFMS.FarmId, drvFMS.Season, drvFMS.Period,
>max(tblForecastMilkingCowData.NoOfCows) as PeakCows
>into #M1
>from drvFMS
>inner join tblForecastMilkingCowData on (tblForecastMilkingCowData.FarmId =
>drvFMS.FarmId) and (tblForecastMilkingCowData.Season = drvFMS.Season) and
>(tblForecastMilkingCowData.Period = drvFMS.Period)
>group by drvFMS.FarmId, drvFMS.Season, drvFMS.Period
>order by drvFMS.FarmId, drvFMS.Season, drvFMS.Period
>select #M1.*, tblOriginalFarmData.FarmSize
>into #M2
>from #M1
>inner join tblOriginalFarmData on (tblOriginalFarmData.FarmId = #M1.FarmId)
>and (tblOriginalFarmData.Season = #M1.Season)
>select #M2.FarmId, #M2.Season, #M2.Period, cast(round((cast(nmlFMS.tSeasonM
S
>as decimal(11,3)) / cast(#M2.PeakCows as decimal(11,3))),0) as integer) as
>'tSeasonMS/Cow', cast(round((cast(nmlFMS.rSeasonMS as decimal(11,3)) /
>cast(#M2.PeakCows as decimal(11,3))),0) as integer) as 'rSeasonMS/Cow',
>(nmlFMS.[tSeasonPastureHarvest/Ha] / (#M2.PeakCows / #M2.FarmSize)) as
>'tSeasonPastureHarvest/Cow', (nmlFMS.[rSeasonPastureHarvest/Ha] /
>(#M2.PeakCows / #M2.FarmSize)) as 'rSeasonPastureHarvest/Cow',
>(nmlFMS.tSeasonSuppUsed / #M2.PeakCows) as 'tSeasonSuppUsed/Cow',
>(nmlFMS.rSeasonSuppUsed / #M2.PeakCows) as 'rSeasonSuppUsed/Cow',
>(nmlFMS.tSeasonFeedCosts / #M2.PeakCows) as 'tSeasonFeedCosts/Cow',
>(nmlFMS.rSeasonFeedCosts / #M2.PeakCows) as 'rSeasonFeedCosts/Cow',
>(nmlFMS.tSeasonGrossMargin / #M2.PeakCows) as 'tSeasonGrossMargin/Cow',
>(nmlFMS.rSeasonGrossMargin / #M2.PeakCows) as 'rSeasonGrossMargin/Cow'
>into #M3
>from #M2
>inner join nmlFMS on (nmlFMS.FarmId = #M2.FarmId) and (nmlFMS.Season =
>#M2.Season) and (nmlFMS.Period = #M2.Period)
>declare @.tSeasonMSCow as integer
>declare @.rSeasonMSCow as integer
>declare @.tSeasonPastureHarvestCow as decimal(9,3)
>declare @.rSeasonPastureHarvestCow as decimal(9,3)
>declare @.tSeasonSuppUsedCow as decimal(9,3)
>declare @.rSeasonSuppUsedCow as decimal(9,3)
>declare @.tSeasonFeedCostsCow as decimal(9,3)
>declare @.rSeasonFeedCostsCow as decimal(9,3)
>declare @.tSeasonGrossMarginCow as decimal(9,3)
>declare @.rSeasonGrossMarginCow as decimal(9,3)
>declare a_Cursor cursor for select * from #M3
>open a_Cursor
>fetch next from a_Cursor
>into @.F, @.S, @.P, @.tSeasonMSCow, @.rSeasonMSCow, @.tSeasonPastureHarvestCow,
>@.rSeasonPastureHarvestCow, @.tSeasonSuppUsedCow, @.rSeasonSuppUsedCow,
>@.tSeasonFeedCostsCow, @.rSeasonFeedCostsCow, @.tSeasonGrossMarginCow,
>@.rSeasonGrossMarginCow
>while @.@.fetch_status = 0
>begin
> update nmlFMS set [tSeasonMS/Cow] = @.tSeasonMSCow, [rSeasonMS/Cow] =
>@.rSeasonMSCow, [tSeasonPastureHarvest/Cow] = @.tSeasonPastureHarvestCow,
>[rSeasonPastureHarvest/Cow] = @.rSeasonPastureHarvestCow,
>[tSeasonSuppUsed/Cow] = @.tSeasonSuppUsedCow, [rSeasonSuppUsed/Cow] =
>@.rSeasonSuppUsedCow, [tSeasonFeedCosts/Cow] = @.tSeasonFeedCostsCow,
>[rSeasonFeedCosts/Cow] = @.rSeasonFeedCostsCow, [tSeasonGrossMargin/Cow] =
>@.tSeasonGrossMarginCow, [rSeasonGrossMargin/Cow] = @.rSeasonGrossMarginCow
> where (FarmId = @.F) and (Season = @.S) and (Period = @.P)
> fetch next from a_Cursor
> into @.F, @.S, @.P, @.tSeasonMSCow, @.rSeasonMSCow, @.tSeasonPastureHarvestCow,
>@.rSeasonPastureHarvestCow, @.tSeasonSuppUsedCow, @.rSeasonSuppUsedCow,
>@.tSeasonFeedCostsCow, @.rSeasonFeedCostsCow, @.tSeasonGrossMarginCow,
>@.rSeasonGrossMarginCow
>end
>close a_Cursor
>deallocate a_Cursor
>----
-
>----
-
>---
>/* [tSeasonPastureHarvest/KgMS], [rSeasonPastureHarvest/KgMS],
>[tSeasonSuppUsed/KgMS], [rSeasonSuppUsed/KgMS], [tSeasonFeedCosts/KgMS],
>[rSeasonFeedCosts/KgMS], [tSeasonGrossMargin/KgMS],
>[rSeasonGrossMargin/KgMS] */
>select drvFMS.FarmId, drvFMS.Season, drvFMS.Period,
>((nmlFMS.[tSeasonPastureHarvest/Ha] * 1000) / nmlFMS.[tSeasonMS/Ha]) as
>'tSeasonPastureHarvest/KgMS', ((nmlFMS.[rSeasonPastureHarvest/Ha] * 1000) /
>nmlFMS.[rSeasonMS/Ha]) as 'rSeasonPastureHarvest/KgMS',
>((nmlFMS.tSeasonSuppUsed * 1000 / tblOriginalFarmData.FarmSize) /
>nmlFMS.[tSeasonMS/Ha]) as 'tSeasonSuppUsed/KgMS', ((nmlFMS.rSeasonSuppUsed *
>1000 / tblOriginalFarmData.FarmSize) / nmlFMS.[rSeasonMS/Ha]) as
>'rSeasonSuppUsed/KgMS', (nmlFMS.tSeasonFeedCosts / nmlFMS.tSeasonMS) as
>'tSeasonFeedCosts/KgMS', (nmlFMS.rSeasonFeedCosts / nmlFMS.rSeasonMS) as
>'rSeasonFeedCosts/KgMS', (nmlFMS.tSeasonGrossMargin / nmlFMS.tSeasonMS) as
>'tSeasonGrossMargin/KgMS', (nmlFMS.rSeasonGrossMargin / nmlFMS.rSeasonMS) a
s
>'rSeasonGrossMargin/KgMS'
>into #N1
>from drvFMS
>inner join nmlFMS on (nmlFMS.FarmId = drvFMS.FarmId) and (nmlFMS.Season =
>drvFMS.Season) and (nmlFMS.Period = drvFMS.Period)
>inner join tblOriginalFarmData on (tblOriginalFarmData.FarmId =
>drvFMS.FarmId) and (tblOriginalFarmData.Season = drvFMS.Season)
>declare @.tSeasonPastureHarvestKgMS as decimal(9,3)
>declare @.rSeasonPastureHarvestKgMS as decimal(9,3)
>declare @.tSeasonSuppUsedKgMS as decimal(9,3)
>declare @.rSeasonSuppUsedKgMS as decimal(9,3)
>declare @.tSeasonFeedCostsKgMS as decimal(9,3)
>declare @.rSeasonFeedCostsKgMS as decimal(9,3)
>declare @.tSeasonGrossMarginKgMS as decimal(9,3)
>declare @.rSeasonGrossMarginKgMS as decimal(9,3)
>declare a_Cursor cursor for select * from #N1
>open a_Cursor
>fetch next from a_Cursor
>into @.F, @.S, @.P, @.tSeasonPastureHarvestKgMS, @.rSeasonPastureHarvestKgMS,
>@.tSeasonSuppUsedKgMS, @.rSeasonSuppUsedKgMS, @.tSeasonFeedCostsKgMS,
>@.rSeasonFeedCostsKgMS, @.tSeasonGrossMarginKgMS, @.rSeasonGrossMarginKgMS
>while @.@.fetch_status = 0
>begin
> update nmlFMS set [tSeasonPastureHarvest/KgMS] =
>@.tSeasonPastureHarvestKgMS, [rSeasonPastureHarvest/KgMS] =
>@.rSeasonPastureHarvestKgMS, [tSeasonSuppUsed/KgMS] = @.tSeasonSuppUsedKgMS,
>[rSeasonSuppUsed/KgMS] = @.rSeasonSuppUsedKgMS, [tSeasonFeedCosts/KgMS] =
>@.tSeasonFeedCostsKgMS, [rSeasonFeedCosts/KgMS] = @.rSeasonFeedCostsKgMS,
>[tSeasonGrossMargin/KgMS] = @.tSeasonGrossMarginKgMS,
>[rSeasonGrossMargin/KgMS] = @.rSeasonGrossMarginKgMS
> where (FarmId = @.F) and (Season = @.S) and (Period = @.P)
> fetch next from a_Cursor
> into @.F, @.S, @.P, @.tSeasonPastureHarvestKgMS, @.rSeasonPastureHarvestKgMS,
>@.tSeasonSuppUsedKgMS, @.rSeasonSuppUsedKgMS, @.tSeasonFeedCostsKgMS,
>@.rSeasonFeedCostsKgMS, @.tSeasonGrossMarginKgMS, @.rSeasonGrossMarginKgMS
>end
>close a_Cursor
>deallocate a_Cursor
>----
-
>----
-
>---
>/* nmMthEndAvgCover, nmPastureGrowth */
>select drvFMS.FarmId, drvFMS.Season, drvFMS.Period,
>tblForecastPastureData.Cover as nmMthEndAvgCover
>into #O1
>from drvFMS
>inner join tblForecastPastureData on (tblForecastPastureData.FarmId =
>drvFMS.FarmId) and (tblForecastPastureData.Season = drvFMS.Season) and
>(tblForecastPastureData.Period = drvFMS.Period) and
>(month(tblForecastPastureData.[Date]) = @.NextPeriod_Month) and
>(day(tblForecastPastureData.[Date]) = 21)
>select #O1.*, tblForecastPastureData.Growth
>into #O2
>from #O1
>inner join tblForecastPastureData on (tblForecastPastureData.FarmId =
>#O1.FarmId) and (tblForecastPastureData.Season = #O1.Season) and
>(tblForecastPastureData.Period = #O1.Period) and
>(month(tblForecastPastureData.[Date]) = @.NextPeriod_Month)
>select #O2.FarmId, #O2.Season, #O2.Period, #O2.nmMthEndAvgCover,
>avg(cast(#O2.Growth as decimal(9,3))) as nmPastureGrowth
>into #O3
>from #O2
>group by #O2.FarmId, #O2.Season, #O2.Period, #O2.nmMthEndAvgCover
>declare @.nmMthEndAvgCover as smallint
>declare @.nmPastureGrowth as decimal(9,3)
>declare a_Cursor cursor for select * from #O3
>open a_Cursor
>fetch next from a_Cursor
>into @.F, @.S, @.P, @.nmMthEndAvgCover, @.nmPastureGrowth
>while @.@.fetch_status = 0
>begin
> update nmlFMS set nmMthEndAvgCover = @.nmMthEndAvgCover, nmPastureGrowth =
>@.nmPastureGrowth
> where (FarmId = @.F) and (Season = @.S) and (Period = @.P)
> fetch next from a_Cursor
> into @.F, @.S, @.P, @.nmMthEndAvgCover, @.nmPastureGrowth
>end
>close a_Cursor
>deallocate a_Cursor
>----
-
>----
-
>---
>/* nmAvgCowsMilked, nmDmc1, nmDmc2, nmDmc3, nmDmc4, nmDmc5, nmDmc6, nmDmc7,
>nmCrops, nmTotalDMConsumed */
>select drvFMS.FarmId, drvFMS.Season, drvFMS.Period,
>avg(cast(tblForecastMilkingCowData.NoOfCows as decimal(9,3))) as
>nmAvgCowsMilked, avg(cast(tblForecastMilkingCowData.dmc1 as decimal(9,3)))
>as nmDmc1avg, avg(cast(tblForecastMilkingCowData.dmc2 as decimal(9,3))) as
>nmDmc2avg, avg(cast(tblForecastMilkingCowData.dmc3 as decimal(9,3))) as
>nmDmc3avg, avg(cast(tblForecastMilkingCowData.dmc4 as decimal(9,3))) as
>nmDmc4avg, avg(cast(tblForecastMilkingCowData.dmc5 as decimal(9,3))) as
>nmDmc5avg, avg(cast(tblForecastMilkingCowData.dmc6 as decimal(9,3))) as
>nmDmc6avg, avg(cast(tblForecastMilkingCowData.dmc7 as decimal(9,3))) as
>nmDmc7avg, avg(cast(tblForecastMilkingCowData.Crop1 as decimal(9,3))) as
>nmCrop1avg, avg(cast(tblForecastMilkingCowData.Crop2 as decimal(9,3))) as
>nmCrop2avg, avg(cast(tblForecastMilkingCowData.Crop3 as decimal(9,3))) as
>nmCrop3avg, avg(cast(tblForecastMilkingCowData.Crop4 as decimal(9,3))) as
>nmCrop4avg, avg(cast(tblForecastMilkingCowData.Crop5 as decimal(9,3))) as
>nmCrop5avg
>into #P1
>from tblForecastMilkingCowData
>inner join drvFMS on (tblForecastMilkingCowData.FarmId = drvFMS.FarmId) and
>(tblForecastMilkingCowData.Season = drvFMS.Season) and
>(tblForecastMilkingCowData.Period = @.ThisPeriod) and
>(month(tblForecastMilkingCowData.[Date]) = @.NextPeriod_Month)
>group by drvFMS.FarmId, drvFMS.Season, drvFMS.Period
>select #P1.*, (nmCrop1avg + nmCrop2avg + nmCrop3avg + nmCrop4avg +
>nmCrop5avg) as nmCrops, (round(nmDmc1avg,1) + round(nmDmc2avg,1) +
>round(nmDmc3avg,1) + round(nmDmc4avg,1) + round(nmDmc5avg,1) +
>round(nmDmc6avg,1) + round(nmDmc7avg,1) + round(nmCrop1avg,1) +
>round(nmCrop2avg,1) + round(nmCrop3avg,1) + round(nmCrop4avg,1) +
>round(nmCrop5avg,1)) as nmTotalDMConsumed
>into #P2
>from #P1
>declare @.nmAvgCowsMilked as smallint
>declare @.nmDmc1 as decimal(9,3)
>declare @.nmDmc2 as decimal(9,3)
>declare @.nmDmc3 as decimal(9,3)
>declare @.nmDmc4 as decimal(9,3)
>declare @.nmDmc5 as decimal(9,3)
>declare @.nmDmc6 as decimal(9,3)
>declare @.nmDmc7 as decimal(9,3)
>declare @.nmCrops as decimal(9,3)
>declare @.nmTotalDMConsumed as decimal(9,3)
>declare a_Cursor cursor for
>select FarmId, Season, Period, nmAvgCowsMilked, nmDmc1avg, nmDmc2avg,
>nmDmc3avg, nmDmc4avg, nmDmc5avg, nmDmc6avg, nmDmc7avg, nmCrops,
>nmTotalDMConsumed from #P2
>open a_Cursor
>fetch next from a_Cursor
>into @.F, @.S, @.P, @.nmAvgCowsMilked, @.nmDmc1, @.nmDmc2, @.nmDmc3, @.nmDmc4,
>@.nmDmc5, @.nmDmc6, @.nmDmc7, @.nmCrops, @.nmTotalDMConsumed
>while @.@.fetch_status = 0
>begin
> update nmlFMS set nmAvgCowsMilked = @.nmAvgCowsMilked, nmDmc1 = @.nmDmc1,
>nmDmc2 = @.nmDmc2, nmDmc3 = @.nmDmc3, nmDmc4 = @.nmDmc4, nmDmc5 = @.nmDmc5,
>nmDmc6 = @.nmDmc6, nmDmc7 = @.nmDmc7, nmCrops = @.nmCrops, nmTotalDMConsumed =
>@.nmTotalDMConsumed
> where (FarmId = @.F) and (Season = @.S) and (Period = @.P)
> fetch next from a_Cursor
> into @.F, @.S, @.P, @.nmAvgCowsMilked, @.nmDmc1, @.nmDmc2, @.nmDmc3, @.nmDmc4,
>@.nmDmc5, @.nmDmc6, @.nmDmc7, @.nmCrops, @.nmTotalDMConsumed
>end
>close a_Cursor
>deallocate a_Cursor
>----
-
>----
-
>---
>/* nmSeasonToDateMS, nmMonthTotalMS, [nmMS/Cow/Day] */
>select drvFMS.FarmId, drvFMS.Season, drvFMS.Period,
>month(tblForecastProductionData.[Date]) as [Month],
>(tblForecastProductionData.Fat + tblForecastProductionData.Protein) as
>MilkSolids, tblForecastProductionData.Days
>into #Q1
>from drvFMS
>inner join tblForecastProductionData on (tblForecastProductionData.FarmId =
>drvFMS.FarmId) and (tblForecastProductionData.Season = drvFMS.Season) and
>(tblForecastProductionData.Period = @.ThisPeriod)
>select #Q1.FarmId, #Q1.Season, #Q1.Period, sum(#Q1.MilkSolids) as
>nmMonthTotalMS, sum(#Q1.Days) as [Days]
>into #Q2
>from #Q1
>where (#Q1.[Month] = @.NextPeriod_Month)
>group by FarmId, Season, Period
>create index Q2_ndx on #Q2 (FarmId, Season, Period)
>create index P2_ndx on #P2 (FarmId, Season, Period)
>select #Q1.*, dbo.fnMonthToPeriod([Month]) as MonthPeriod,
>#Q2.nmMonthTotalMS, cast((#Q2.nmMonthTotalMS / #P2.nmAvgCowsMilked /
>#Q2.Days) as decimal(9,3)) as [nmMS/Cow/Day]
>into #Q3
>from #Q1
>inner join #Q2 on (#Q2.FarmId = #Q1.FarmId) and (#Q2.Season = #Q1.Season)
>and (#Q2.Period = #Q1.Period)
>inner join #P2 on (#P2.FarmId = #Q1.FarmId) and (#P2.Season = #Q1.Season)
>and (#P2.Period = #Q1.Period)
>select #Q3.FarmId, #Q3.Season, #Q3.Period, #Q3.nmMonthTotalMS,
>[nmMS/Cow/Day], sum(#Q3.MilkSolids) as nmSeasonToDateMS
>into #Q4
>from #Q3
>where #Q3.MonthPeriod <= @.NextPeriod
>group by FarmId, Season, Period, nmMonthTotalMS, [nmMS/Cow/Day]
>declare @.nmMonthTotalMS as decimal(9,3)
>declare @.nmMSCowDay as decimal(9,3)
>declare @.nmSeasonToDateMS as decimal(9,3)
>declare a_Cursor cursor for select * from #Q4
>open a_Cursor
>fetch next from a_Cursor
>into @.F, @.S, @.P, @.nmMonthTotalMS, @.nmMSCowDay, @.nmSeasonToDateMS
>while @.@.fetch_status = 0
>begin
> update nmlFMS set nmMonthTotalMS = @.nmMonthTotalMS, [nmMS/Cow/Day] =
>@.nmMSCowDay, nmSeasonToDateMS = @.nmSeasonToDateMS
> where (FarmId = @.F) and (Season = @.S) and (Period = @.P)
> fetch next from a_Cursor
> into @.F, @.S, @.P, @.nmMonthTotalMS, @.nmMSCowDay, @.nmSeasonToDateMS
>end
>close a_Cursor
>deallocate a_Cursor
>----
-
>----
-
>---
>/* nmFeedConvEff, [nmFeedAlloc/LiveWeight], [nmMthMS/Ha] */
>select #P2.FarmId, #P2.Season, #P2.Period, #P2.nmTotalDMConsumed,
>sum(tblForecastProductionData.Days) as [Days]
>into #R1
>from #P2
>inner join tblForecastProductionData on (tblForecastProductionData.FarmId =
>#P2.FarmId) and (tblForecastProductionData.Season = #P2.Season) and
>(tblForecastProductionData.Period = @.ThisPeriod) and
>(month(tblForecastProductionData.[Date]) = @.NextPeriod_Month)
>group by #P2.FarmId, #P2.Season, #P2.Period, #P2.nmTotalDMConsumed
>select #R1.FarmId, #R1.Season, #R1.Period, #R1.nmTotalDMConsumed, #R1.Days,
>avg(cast(tblForecastPastureData.FarmSize as decimal(9,3))) as nmAverageArea
>into #R2
>from #R1
>inner join tblForecastPastureData on (tblForecastPastureData.FarmId =
>#R1.FarmId) and (tblForecastPastureData.Season = #R1.Season) and
>(tblForecastPastureData.Period = @.ThisPeriod) and
>(month(tblForecastPastureData.[Date]) = @.NextPeriod_Month)
>group by #R1.FarmId, #R1.Season, #R1.Period, #R1.nmTotalDMConsumed,
>#R1.[Days]
>select #R2.*, #P2.nmAvgCowsMilked, #Q4.nmMonthTotalMS,
>tblForecastFarmData.FarmSize as 'nmAvailableArea',
>tblFarmAdditionalInfo.Liveweight
>into #R3
>from #R2
>inner join #P2 on (#P2.FarmId = #R2.FarmId) and (#P2.Season = #R2.Season)
>and (#P2.Period = #R2.Period)
>inner join #Q4 on (#Q4.FarmId = #R2.FarmId) and (#Q4.Season = #R2.Season)
>and (#Q4.Period = #R2.Period)
>inner join tblForecastFarmData on (tblForecastFarmData.FarmId = #R2.FarmId)
>and (tblForecastFarmData.Season = #R2.Season) and
>(tblForecastFarmData.Period = #R2.Period)
>inner join tblFarmAdditionalInfo on (tblFarmAdditionalInfo.FarmId =
>#R2.FarmId)
>select #R3.FarmId, #R3.Season, #R3.Period, ((#R3.nmTotalDMConsumed *
>#R3.Days * (#R3.nmAvgCowsMilked / #R3.nmAverageArea)) / (#R3.nmMonthTotalMS
>/ #R3.nmAvailableArea)) as nmFeedConvEff, (#R3.nmTotalDMConsumed /
>#R3.Liveweight * 100) as [nmFeedAlloc/LiveWeight], (#R3.nmMonthTotalMS /
>#R3.nmAvailableArea) as [nmMthMS/Ha]
>into #R4
>from #R3
>declare @.nmFeedConvEff as decimal(9,3)
>declare @.nmFeedAllocLiveWeight as decimal(9,3)
>declare @.nmMthMSHa as decimal(9,3)
>declare a_Cursor cursor for select * from #R4
>open a_Cursor
>fetch next from a_Cursor
>into @.F, @.S, @.P, @.nmFeedConvEff, @.nmFeedAllocLiveWeight, @.nmMthMSHa
>while @.@.fetch_status = 0
>begin
> update nmlFMS set nmFeedConvEff = cast(@.nmFeedConvEff as decimal(6,4)),
>[nmFeedAlloc/LiveWeight] = cast(@.nmFeedAllocLiveWeight as decimal(6,4)),
>[nmMthMS/Ha] = @.nmMthMSHa
> where (FarmId = @.F) and (Season = @.S) and (Period = @.P)
> fetch next from a_Cursor
> into @.F, @.S, @.P, @.nmFeedConvEff, @.nmFeedAllocLiveWeight, @.nmMthMSHa
>end
>close a_Cursor
>deallocate a_Cursor
>----
-
>----
-
>---
>/* [nmMthFeedCosts/KgMS] */
>select #R1.FarmId, #R1.Season, #R1.Period, #R1.Days,
>cast(avg(tblForecastMilkingCowData.NoOfCows) as smallint) as
>nmAvgCowsMilked, avg(tblForecastMilkingCowData.dmc2 *
>tblFarmAdditionalInfo.dmc2$) as nmDmc2$, avg(tblForecastMilkingCowData.dmc3
>* tblFarmAdditionalInfo.dmc3$) as nmDmc3$,
>avg(tblForecastMilkingCowData.dmc4 * tblFarmAdditionalInfo.dmc4$) as
>nmDmc4$, avg(tblForecastMilkingCowData.dmc5 * tblFarmAdditionalInfo.dmc5$)
>as nmDmc5$, avg(tblForecastMilkingCowData.dmc6 *
>tblFarmAdditionalInfo.dmc6$) as nmDmc6$, avg(tblForecastMilkingCowData.dmc7
>* tblFarmAdditionalInfo.dmc7$) as nmDmc7$,
>avg(tblForecastMilkingCowData.Crop1 * tblFarmAdditionalInfo.Crop1$) as
>nmCrop1$, avg(tblForecastMilkingCowData.Crop2 *
>tblFarmAdditionalInfo.Crop2$) as nmCrop2$,
>avg(tblForecastMilkingCowData.Crop3 * tblFarmAdditionalInfo.Crop3$) as
>nmCrop3$, avg(tblForecastMilkingCowData.Crop4 *
>tblFarmAdditionalInfo.Crop4$) as nmCrop4$,
>avg(tblForecastMilkingCowData.Crop5 * tblFarmAdditionalInfo.Crop5$) as
>nmCrop5$
>into #S1
>from #R1
>inner join tblForecastMilkingCowData on (tblForecastMilkingCowData.FarmId =
>#R1.FarmId) and (tblForecastMilkingCowData.Season = #R1.Season) and
>(tblForecastMilkingCowData.Period = @.ThisPeriod) and
>(month(tblForecastMilkingCowData.[Date]) = @.NextPeriod_Month)
>inner join tblFarmAdditionalInfo on (tblFarmAdditionalInfo.FarmId =
>#R1.FarmId)
>group by #R1.FarmId, #R1.Season, #R1.Period, #R1.Days
>select #S1.FarmId, #S1.Season, #S1.Period, #S1.Days, #S1.nmAvgCowsMilked,
>#Q2.nmMonthTotalMS, (#S1.nmDmc2$ + #S1.nmDmc3$ + #S1.nmDmc4$ + #S1.nmDmc5$
+
>#S1.nmDmc6$ + #S1.nmDmc7$ + #S1.nmCrop1$ + #S1.nmCrop2$ + #S1.nmCrop3$ +
>#S1.nmCrop4$ + #S1.nmCrop5$) as nmMthFeedCosts,
>tblForecastFarmData.Adjistment$ as nmGrazingCosts$, (tblForecastFarmData.Fa
t
>+ tblForecastFarmData.Protein) as nmSeasonMS, (tblForecastFarmData.IOFC$ -
>tblForecastFarmData.CowCosts$ - tblForecastFarmData.GrossMargin$) as
>nmIrrigationCosts$, nmlFMS.rSeasonSuppUsed, nmlFMS.rSeasonFeedCosts
>into #S2
>from #S1
>inner join #Q2 on (#Q2.FarmId = #S1.FarmId) and (#Q2.Season = #S1.Season)
>and (#Q2.Period = #S1.Period)
>inner join tblForecastFarmData on (tblForecastFarmData.FarmId = #S1.FarmId)
>and (tblForecastFarmData.Season = #S1.Season) and
>(tblForecastFarmData.Period = @.ThisPeriod)
>inner join nmlFMS on (nmlFMS.FarmId = #S1.FarmId) and (nmlFMS.Season =
>#S1.Season) and (nmlFMS.Period = #S1.Period)
>select #S2.*, (((#S2.nmMthFeedCosts * #S2.nmAvgCowsMilked * #S2.Days) /
>#S2.nmMonthTotalMS) + (#S2.nmGrazingCosts$ / #S2.nmSeasonMS) +
>(#S2.nmIrrigationCosts$ / #S2.nmSeasonMS)) as nmMthFeedCostsKgMS
>into #S3
>from #S2
>declare @.nmMthFeedCostsKgMS as decimal(9,4)
>declare a_Cursor cursor for select FarmId, Season, Period,
>nmMthFeedCostsKgMS from #S3
>open a_Cursor
>fetch next from a_Cursor
>into @.F, @.S, @.P, @.nmMthFeedCostsKgMS
>while @.@.fetch_status = 0
>begin
> update nmlFMS set [nmMthFeedCosts/KgMS] = @.nmMthFeedCostsKgMS
> where (FarmId = @.F) and (Season = @.S) and (Period = @.P)
> fetch next from a_Cursor
> into @.F, @.S, @.P, @.nmMthFeedCostsKgMS
>end
>close a_Cursor
>deallocate a_Cursor
>----
-
>----
-
>---
>EndOf:
>Return
>GO
>
>
>[/color]|||"SBeetham" <sbeetham@.xtra.co.nz> wrote a message
news:O9XQLNyHFHA.3760@.TK2MSFTNGP12.phx.gbl...
Start by writing some tests that 1) show you understand the requirements of
the procedure, and 2) make sure those requirements are still met after you
rewrite it.|||Thanks for your input Steve.
I've taken your comments on board... I'm just going to break out portions,
remove the cursors and most of the temp tables and see how I go from there..
.
appreciate all of your inputs...
Cheers, Simon
No comments:
Post a Comment