Recipient name becomes <unknown sender>

Our SQL Server was moved to a new domain. A stored procedure that emails ou
t
with sender's address as [@.v_from = 'abc@.abc.com'] suddenly appears in
Outlook w/o the sender name in "From" column like it used to. In its place
now is <unknown sender>.
Please help.
Gracias
LaEsmeraldaLaEsmeralda wrote:
> Our SQL Server was moved to a new domain. A stored procedure that emails
out
> with sender's address as [@.v_from = 'abc@.abc.com'] suddenly appears in
> Outlook w/o the sender name in "From" column like it used to. In its pla
ce
> now is <unknown sender>.
> Please help.
> Gracias
> LaEsmeralda
What name would you usually see in the from field? It sounds like it's
more of an Outlook problem than it's a SQL server problem. When you send
out emails from Outlook, it will use your Exchange server to resolve the
name into what ever name is in the Global Addresse Book. SInce you've
moved your SQL server to a new domain, it could be that Outlook isn't
able to resolve the name. What happens if you open Outlook on the SQL
server (with the profile that are used for sending out emails) and then
send an email from there. Will you then get the correct name in the
"from" field?
Regards
Steen|||Outlook works normally.
"Steen Persson (DK)" wrote:

> LaEsmeralda wrote:
> What name would you usually see in the from field? It sounds like it's
> more of an Outlook problem than it's a SQL server problem. When you send
> out emails from Outlook, it will use your Exchange server to resolve the
> name into what ever name is in the Global Addresse Book. SInce you've
> moved your SQL server to a new domain, it could be that Outlook isn't
> able to resolve the name. What happens if you open Outlook on the SQL
> server (with the profile that are used for sending out emails) and then
> send an email from there. Will you then get the correct name in the
> "from" field?
> Regards
> Steen
>

Recipient name becomes <unknown sender>

Our SQL Server was moved to a new domain. A stored procedure that emails out
with sender's address as [@.v_from = 'abc@.abc.com'] suddenly appears in
Outlook w/o the sender name in "From" column like it used to. In its place
now is <unknown sender>.
Please help.
Gracias
LaEsmeraldaLaEsmeralda wrote:
> Our SQL Server was moved to a new domain. A stored procedure that emails out
> with sender's address as [@.v_from = 'abc@.abc.com'] suddenly appears in
> Outlook w/o the sender name in "From" column like it used to. In its place
> now is <unknown sender>.
> Please help.
> Gracias
> LaEsmeralda
What name would you usually see in the from field? It sounds like it's
more of an Outlook problem than it's a SQL server problem. When you send
out emails from Outlook, it will use your Exchange server to resolve the
name into what ever name is in the Global Addresse Book. SInce you've
moved your SQL server to a new domain, it could be that Outlook isn't
able to resolve the name. What happens if you open Outlook on the SQL
server (with the profile that are used for sending out emails) and then
send an email from there. Will you then get the correct name in the
"from" field?
Regards
Steen|||Outlook works normally.
"Steen Persson (DK)" wrote:
> LaEsmeralda wrote:
> > Our SQL Server was moved to a new domain. A stored procedure that emails out
> > with sender's address as [@.v_from = 'abc@.abc.com'] suddenly appears in
> > Outlook w/o the sender name in "From" column like it used to. In its place
> > now is <unknown sender>.
> > Please help.
> > Gracias
> > LaEsmeralda
> What name would you usually see in the from field? It sounds like it's
> more of an Outlook problem than it's a SQL server problem. When you send
> out emails from Outlook, it will use your Exchange server to resolve the
> name into what ever name is in the Global Addresse Book. SInce you've
> moved your SQL server to a new domain, it could be that Outlook isn't
> able to resolve the name. What happens if you open Outlook on the SQL
> server (with the profile that are used for sending out emails) and then
> send an email from there. Will you then get the correct name in the
> "from" field?
> Regards
> Steen
>

Receiving queue does not fire stored procedure

I am doing my first SSB application where two different servers send messages to each other. The logic was all previously tested on a single server between two databases and it worked OK.

The problem I am having is that when the message is received at the target server (I see this in profiler), the stored procedure associated with the queue does not fire.

I see an acknowledgment fire back to the initiator, but it is like the target server does nothing with the initial message.

Any ideas on how I can further troubleshoot this? FWIW, I used the setup tool provided by RemusResanu to set up the routes and service bindings.

Thanks for any help!
John
Here is what my target server shows in profiler

Event Class/Sub Class

Broker:Conversation Group 1 - Create
Broker:Conversation 12 - Dialog Created
Broker:Conversation 6 - Received Sequenced Message
Broker:Remote Message Acknowledgement 3 - Message with Acknowledgement Received
Broker:Message Classify 2 - Remote
|||Also, when I query the queue, I can see all of the messages just sitting there. They are reaching the right place, just nothing happens from there.

Please help!
John

Code Snippet

CREATE QUEUE [dbo].[SiteChangeNotifyReceiveQueue] WITH STATUS = ON , RETENTION = ON , ACTIVATION ( STATUS = ON , PROCEDURE_NAME = [dbo].[SiteChangeQueueReader] , MAX_QUEUE_READERS = 10 , EXECUTE AS N'sqlservices' ) ON [PRIMARY]

|||Have you tried to execute the procedure manually?
Can the user 'sqlservices' be impersonated? I.e. executing EXECUTE AS USER='sqlservices' works fine w/o errors?
Do you see any errors in the ERRORLOG file or in the NT application event log (eventvwr.exe)?|||Yes, I just tried that and it worked fine. In fact, it seemed to go in to the queue and process all of the pending messages.

So, here is the really strange part:

Now, everything works fine.

After I ran the SP manually, my messages come through and the SP fires by itself again.

I would really like it if someone could give me an explanation of this. Is this part of Service Broker enforcing the order of transactions- there were errors in my SP at one time. Perhaps the SSB was not processing the later messages until the old ones went through?

I don't t like it when I can't explain how things went from broken to working!
|||If the situation repeats please look at sys.dm_broker_queue_monitors and tell us what is the state of the queue with the problem.

Receiving Error: 'QUOTED_IDENTIFIER' when updating

Hello, I am getting the following message when running a stored proc:
Server: Msg 1934, Level 16, State 1, Procedure SZ_ProcessToCPInvoices, Line
261
UPDATE failed because the following SET options have incorrect settings:
'QUOTED_IDENTIFIER'.
I cannot find any information about this error. This happens with
QUOTED_IDENTIFIER on or off.Ric,
Try going to Query Analyzer and editing the sp. You should be able to see
the settings of "QUOTED_IDENTIFIER" when the sp was created. chnage it to
accomodate your needs.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER procedure dbo.SZ_ProcessToCPInvoices
...
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
AMB
"Ric" wrote:

> Hello, I am getting the following message when running a stored proc:
> Server: Msg 1934, Level 16, State 1, Procedure SZ_ProcessToCPInvoices, Lin
e
> 261
> UPDATE failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> I cannot find any information about this error. This happens with
> QUOTED_IDENTIFIER on or off.
>

Receiving Error: 'QUOTED_IDENTIFIER' when updating

Hello, I am getting the following message when running a stored proc:
Server: Msg 1934, Level 16, State 1, Procedure SZ_ProcessToCPInvoices, Line
261
UPDATE failed because the following SET options have incorrect settings:
'QUOTED_IDENTIFIER'.
I cannot find any information about this error. This happens with
QUOTED_IDENTIFIER on or off.
Ric,
Try going to Query Analyzer and editing the sp. You should be able to see
the settings of "QUOTED_IDENTIFIER" when the sp was created. chnage it to
accomodate your needs.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER procedure dbo.SZ_ProcessToCPInvoices
...
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
AMB
"Ric" wrote:

> Hello, I am getting the following message when running a stored proc:
> Server: Msg 1934, Level 16, State 1, Procedure SZ_ProcessToCPInvoices, Line
> 261
> UPDATE failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> I cannot find any information about this error. This happens with
> QUOTED_IDENTIFIER on or off.
>

Receiving and sending a cursor with(in) a Stored Procedure

Can someone post some code that shows a Stored Procedure receiving a cursor that it can process - lets say a group of order detail records are received that must be saved along with the single Order header record.

And, in another example, a SP returns a result set to the calling program. - For example, a particular sale receipt is pulled up on the screen and the order detail is needed.

Thanks for help on this,

PeterCursors are a really poor choice in MS-SQL. It would appear that you are trying to use Oracle-like logic in SQL Server, which is a receipe for disaster.

Can you explain in a bit more detail what you are planning to do? I suspect that there is a much better way to do the job once we understand what you are trying to accomplish.

-PatP|||Hi Pat:

Maybe I should have said "result set" or "table" - I'm fairly new with SS.

Here's more detail:

A web app has a screen where the user enters order header information - name, address, etc. - this would be the tblOrder - 1 record. Then they enter the order detail - say 6 individual sales items - tblDetail - 6 records. All these entries are done on 1 screen in the web app.

Upon save, a stored procedure is called that will do 2 things.

1. It will save the tblOrder record and generate the PK for this record
2. It will save the 6 records into tblDetail including stuffing the PK from the tblOrder into a FK field within the tblDetail records.

1 and 2 would probably be wrapped in a transaction in case either fails it could Rollback. Otherwise, Commit.

The approach within the SP is basically what I'm after. Does that make sense?

Thanks,

Peter|||Write one stored procedure that saves your order record and returns the generated key value to your interface.

Write a second stored procedure that that save the detail records, including the order key returned by the first procedure.

Your interface should save the order, and then loop through the detail records calling an insert for each one.|||Blindman:

I was hoping for some code to show the passing of the detail record set from the view tier to the SP.

Secondly - I'd rather fail both the Order record creation and Part records creation if either fails.

Wouldn't it be better to have both processes in the same stored procedure? Otherwise, end up with an order and no detail?

Is there anything preventing me from doing both in 1 stored procedure? If not, how do I pass the needed data - that's the key to my question.

Thanks for helping,

Peter|||The detail recored set is just passed to the stored procedure as a set of parameters defined in the procedure's heading.|||Hi - a coded example of the stored procedure would be most helpful to this newbie. I understand that detailed record set is passed as a parameter. My original question would be to see the code of the Stored Procedure handling the receipt and processing of the detailed record set.

Thanks,

Peter|||Create Procedure DetailInsert(@.OrderID as int, @.DetailInfo as varchar(50))
as
begin
insert into DetailTable (OrderID, DetailInfo) values (@.OrderID, @.DetailInfo)
end|||So detailInfo is a record set? It looks like a single field. If it is a record set, are the values comma delimited or is it simply a reference to a record set that was established in the calling program?

This is where I'm confused. I can handle a SP that inserts a single record into a DB. I'm trying to understand a situation where multiple records are submitted at once.

Thanks,

peter|||I have no idea what @.DetailInfo is. That is up to you. It is a dummy parameter that represents all the values you need to submit to the procedure. Submit each value as a separate parameter.

Peter, have you even TRIED to look up how to write Stored Procedure in Books Online?|||Blindman:

I have looked up many things with books online. I thought this might be a place to zero in efficiently for a solution. As someone new, I'm struggling. I apologize that I didn't understand the purpose of this board and to have taken valuable time away from you.

I have numerous stored procedures that work to insert and update single records. I have some stored procedures to query and return a record set. What I haven't had success in is having a stored procedure receive a group of child records along with parent and save both. If either fails, I want to rollback - that's why I thought there would be a benefit to combining the operations into a single SP transaction.|||The answer here is easy.

This is not Oracle

You can not do what you are thinking unless you use bcp or bulk insert.

In either case that means you'd have to create a file, which I don't think is a good idea.

You need to iterate through your rs and make a stored procedure call for each record set.

Now you could put all of the data in to 1 string, pass it to a sproc, the "unstring" it in the sproc...but that would be overkill.

Use 2 sprocs like the blind dude said.

Pass the id as an output variable from the first sproc.

In the second sproc use error checking. If anything fails, perform a delete of the initial record.

Sorry|||You can't set up a stored procedure that will recieve a group of records. They can only accept input parameters.

I recommend that you either change your application design to one that submits new data one record at a time, or look into other options available through your development interface for handling recordset. I'm not much of an interface programmer, so I can't help you with that, but you might check one of the other sections of DB Forums.

Sorry if I was short with you. Been sick today. I readily answer question such as "How do I write a stored procedure to do X", but I don't answer questions like "How do I write a stored procedure."

If you have specific questions, please do post them on this forum and I or somebody else will assist you.|||Thank you Brett and Blindman. I now understand that the best way to handle is from the interface, not at SQL server.

I hope you are feeling better Blindman.

thanks,

Peter|||Sick?

Don't you listen to anything I say?

A bottle of tequila will take care of all those germs|||Inadequate solution.

A bottle of tequila is too large to physically smash the germs, and too small to submerge myself in.

Can you suggest something more scalable?|||Inadequate solution.

A bottle of tequila is too large to physically smash the germs, and too small to submerge myself in.

Can you suggest something more scalable?Have you ever tried using a bottle of Tequila to smash the germs? I'd like the video rights, and would make you a very handsome deal.

Scalable? How about many cases of Tequila?

-PatP

receive top 20

HI

I am trying to set up a stored procedure to retrieve to 20 messages from a queue into a table to implement a batched process. I have the following code in a stored procedure.

WAITFOR (
RECEIVE top (20) -- get batched so that we can process same listid once
message_type_name,
message_body, -- the message contents
conversation_handle -- the identifier of the dialog this message was received on
FROM dbo.target
into @.PayloadData
), TIMEOUT 3000 -- if the queue is empty for three second, give UPDATE and go away

However, the stored procedure is only retrieving 1 message at a time from the queue. Did I miss some other setting

thanks

P

RECEIVE can only return messages on one conversation group. Normally each conversation is its own conversation group. If you sent only one message on each conversation, RECEIVE cannot get more that one message at a time, even if there are more messages in the queue.|||

Hi

so, in your blog on T-SQL RECEIVE. Fast. : Set based Processing.

How are you able to receive the message in bulk? Is it because of the way you send the message in LoadQueueReceivePerfBlog?

P|||Yes. This is also the reason why I recommend reusing dialogs in my other entry at http://blogs.msdn.com/remusrusanu/archive/2007/04/24/reusing-conversations.aspx|||

one follow up,

when I send the message using the same conversation handle, the receive top (20) statement waits until the previous batch is committed before it will start retrieving the next 20. I guess this is because of the its now part of the same conversation group and service broker need to guarantee process order?

This is what my proc looks like

BEGIN TRANSACTION

WAITFOR (

RECEIVE top (20) -- get batched so that we can process same listid once

message_type_name,

message_body, -- the message contents

conversation_handle -- the identifier of the dialog this message was received on

FROM dbo.target

into @.PayloadData

), TIMEOUT 3000 -- if the queue is empty for three second, give UPDATE and go away

-- do some processing of the records in @.PayloadData

COMMIT TRANSACTION

if "-- do some processing of the records in @.PayloadData" is taking a long time, its going to block the messages in the queue.

If I remove the begin and commit transaction block, it only wait for the 3 seconds I specified.

Question: is the transaction block necessary in the activated procedure.

thanks

Paul

RECEIVE in transactions

Hello!
I have an an event notifications audit solution that I'm working on. I am
using this activation procedure.
CREATE PROCEDURE dbo.AuditReceive
AS
DECLARE @.AuditMessage TABLE (EventData xml);
RECEIVECAST(message_body AS XML)
FROM AuditQueue
INTO @.AuditMessage
INSERT INTO dbo.Audit (PostTime, EventType, LoginName, UserName, ServerName,
DatabaseName, ObjectType, SchemaName, ObjectName, EventData)
SELECTCAST(EventData.query('data(//PostTime)') AS varchar(23)),
CAST(EventData.query('data(//EventType)') AS sysname),
CAST(EventData.query('data(//LoginName)') AS sysname),
CAST(EventData.query('data(//UserName)') AS sysname),
CAST(EventData.query('data(//ServerName)') AS sysname),
CAST(EventData.query('data(//DatabaseName)') AS sysname),
CAST(EventData.query('data(//ObjectType)') AS sysname),
CAST(EventData.query('data(//SchemaName)') AS sysname),
CAST(EventData.query('data(//ObjectName)') AS sysname),
EventData
FROM @.AuditMessage
It's working nicely, but there is one weakness. I would like to have the
RECEIVE and the INSERT as a transaction, but I can't get it working. Is it
not possible to use RECEIVE in transactions?
Best regards
Ola Hallengren
the books on line tell:
"The RECEIVE statement removes received messages from the queue unless the
queue specifies message retention. When the RETENTION setting for the queue
is ON, the RECEIVE statement updates the status column to 1 and leaves the
messages in the queue. When a transaction that contains a RECEIVE statement
rolls back, all changes to the queue within the transaction are also rolled
back, returning messages to the queue."
Could you expose the source code of the transaction you tried? there's
something wrong but i cannot imagine what without seeing an example.
Gilberto Zampatti
"Ola Hallengren" wrote:

> Hello!
> I have an an event notifications audit solution that I'm working on. I am
> using this activation procedure.
> CREATE PROCEDURE dbo.AuditReceive
> AS
> DECLARE @.AuditMessage TABLE (EventData xml);
> RECEIVECAST(message_body AS XML)
> FROM AuditQueue
> INTO @.AuditMessage
> INSERT INTO dbo.Audit (PostTime, EventType, LoginName, UserName, ServerName,
> DatabaseName, ObjectType, SchemaName, ObjectName, EventData)
> SELECTCAST(EventData.query('data(//PostTime)') AS varchar(23)),
> CAST(EventData.query('data(//EventType)') AS sysname),
> CAST(EventData.query('data(//LoginName)') AS sysname),
> CAST(EventData.query('data(//UserName)') AS sysname),
> CAST(EventData.query('data(//ServerName)') AS sysname),
> CAST(EventData.query('data(//DatabaseName)') AS sysname),
> CAST(EventData.query('data(//ObjectType)') AS sysname),
> CAST(EventData.query('data(//SchemaName)') AS sysname),
> CAST(EventData.query('data(//ObjectName)') AS sysname),
> EventData
> FROM @.AuditMessage
> It's working nicely, but there is one weakness. I would like to have the
> RECEIVE and the INSERT as a transaction, but I can't get it working. Is it
> not possible to use RECEIVE in transactions?
> Best regards
> Ola Hallengren
|||> I would like to have the
> RECEIVE and the INSERT as a transaction, but I can't get it working.
In what way doesn't it work? Error messages? Something else?

> Is it
> not possible to use RECEIVE in transactions?
Yes, but you want to be careful. Say you determine that you can't process this message so you
rollback. Since messages are received in order, you will just read the same message next time.
Essentially, you end up in an endless loop. This is called a "poison message" and after 5 rollbacks
in a row, SB will disable the queue. I strongly suggest you get Roger Wolter's book on SB, this
topic is handled in chapter 8.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
news:38D8CD3E-9452-4EAE-A300-DB0F9B3A6F53@.microsoft.com...
> Hello!
> I have an an event notifications audit solution that I'm working on. I am
> using this activation procedure.
> CREATE PROCEDURE dbo.AuditReceive
> AS
> DECLARE @.AuditMessage TABLE (EventData xml);
> RECEIVE CAST(message_body AS XML)
> FROM AuditQueue
> INTO @.AuditMessage
> INSERT INTO dbo.Audit (PostTime, EventType, LoginName, UserName, ServerName,
> DatabaseName, ObjectType, SchemaName, ObjectName, EventData)
> SELECT CAST(EventData.query('data(//PostTime)') AS varchar(23)),
> CAST(EventData.query('data(//EventType)') AS sysname),
> CAST(EventData.query('data(//LoginName)') AS sysname),
> CAST(EventData.query('data(//UserName)') AS sysname),
> CAST(EventData.query('data(//ServerName)') AS sysname),
> CAST(EventData.query('data(//DatabaseName)') AS sysname),
> CAST(EventData.query('data(//ObjectType)') AS sysname),
> CAST(EventData.query('data(//SchemaName)') AS sysname),
> CAST(EventData.query('data(//ObjectName)') AS sysname),
> EventData
> FROM @.AuditMessage
> It's working nicely, but there is one weakness. I would like to have the
> RECEIVE and the INSERT as a transaction, but I can't get it working. Is it
> not possible to use RECEIVE in transactions?
> Best regards
> Ola Hallengren
|||I overlooked one comment in Books Online"
"If the RECEIVE statement is not the first statement in a batch or stored
procedure, the preceding statement must be terminated with a semicolon (;),
the Transact-SQL statement terminator."
Now it's working.
About poisonous messages I have been doing some testing on that by changing
the datatypes in my auditing table, so that the insert fails. The queue then
gets deactivated as you write.
Now it's not so likely that the insert fails, since I have matching
datatypes. I mostly would like to have a transaction in case of a server
crash.
I have ordered the Roger Walters book today.
Thanks for you help.
/Ola
"Tibor Karaszi" wrote:

> In what way doesn't it work? Error messages? Something else?
>
> Yes, but you want to be careful. Say you determine that you can't process this message so you
> rollback. Since messages are received in order, you will just read the same message next time.
> Essentially, you end up in an endless loop. This is called a "poison message" and after 5 rollbacks
> in a row, SB will disable the queue. I strongly suggest you get Roger Wolter's book on SB, this
> topic is handled in chapter 8.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
> news:38D8CD3E-9452-4EAE-A300-DB0F9B3A6F53@.microsoft.com...
>

RECEIVE in transactions

Hello!
I have an an event notifications audit solution that I'm working on. I am
using this activation procedure.
CREATE PROCEDURE dbo.AuditReceive
AS
DECLARE @.AuditMessage TABLE (EventData xml);
RECEIVE CAST(message_body AS XML)
FROM AuditQueue
INTO @.AuditMessage
INSERT INTO dbo.Audit (PostTime, EventType, LoginName, UserName, ServerName,
DatabaseName, ObjectType, SchemaName, ObjectName, EventData)
SELECT CAST(EventData.query('data(//PostTime)') AS varchar(23)),
CAST(EventData.query('data(//EventType)') AS sysname),
CAST(EventData.query('data(//LoginName)') AS sysname),
CAST(EventData.query('data(//UserName)') AS sysname),
CAST(EventData.query('data(//ServerName)') AS sysname),
CAST(EventData.query('data(//DatabaseName)') AS sysname),
CAST(EventData.query('data(//ObjectType)') AS sysname),
CAST(EventData.query('data(//SchemaName)') AS sysname),
CAST(EventData.query('data(//ObjectName)') AS sysname),
EventData
FROM @.AuditMessage
It's working nicely, but there is one weakness. I would like to have the
RECEIVE and the INSERT as a transaction, but I can't get it working. Is it
not possible to use RECEIVE in transactions?
Best regards
Ola Hallengrenthe books on line tell:
"The RECEIVE statement removes received messages from the queue unless the
queue specifies message retention. When the RETENTION setting for the queue
is ON, the RECEIVE statement updates the status column to 1 and leaves the
messages in the queue. When a transaction that contains a RECEIVE statement
rolls back, all changes to the queue within the transaction are also rolled
back, returning messages to the queue."
Could you expose the source code of the transaction you tried? there's
something wrong but i cannot imagine what without seeing an example.
Gilberto Zampatti
"Ola Hallengren" wrote:
> Hello!
> I have an an event notifications audit solution that I'm working on. I am
> using this activation procedure.
> CREATE PROCEDURE dbo.AuditReceive
> AS
> DECLARE @.AuditMessage TABLE (EventData xml);
> RECEIVE CAST(message_body AS XML)
> FROM AuditQueue
> INTO @.AuditMessage
> INSERT INTO dbo.Audit (PostTime, EventType, LoginName, UserName, ServerName,
> DatabaseName, ObjectType, SchemaName, ObjectName, EventData)
> SELECT CAST(EventData.query('data(//PostTime)') AS varchar(23)),
> CAST(EventData.query('data(//EventType)') AS sysname),
> CAST(EventData.query('data(//LoginName)') AS sysname),
> CAST(EventData.query('data(//UserName)') AS sysname),
> CAST(EventData.query('data(//ServerName)') AS sysname),
> CAST(EventData.query('data(//DatabaseName)') AS sysname),
> CAST(EventData.query('data(//ObjectType)') AS sysname),
> CAST(EventData.query('data(//SchemaName)') AS sysname),
> CAST(EventData.query('data(//ObjectName)') AS sysname),
> EventData
> FROM @.AuditMessage
> It's working nicely, but there is one weakness. I would like to have the
> RECEIVE and the INSERT as a transaction, but I can't get it working. Is it
> not possible to use RECEIVE in transactions?
> Best regards
> Ola Hallengren|||> I would like to have the
> RECEIVE and the INSERT as a transaction, but I can't get it working.
In what way doesn't it work? Error messages? Something else?
> Is it
> not possible to use RECEIVE in transactions?
Yes, but you want to be careful. Say you determine that you can't process this message so you
rollback. Since messages are received in order, you will just read the same message next time.
Essentially, you end up in an endless loop. This is called a "poison message" and after 5 rollbacks
in a row, SB will disable the queue. I strongly suggest you get Roger Wolter's book on SB, this
topic is handled in chapter 8.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
news:38D8CD3E-9452-4EAE-A300-DB0F9B3A6F53@.microsoft.com...
> Hello!
> I have an an event notifications audit solution that I'm working on. I am
> using this activation procedure.
> CREATE PROCEDURE dbo.AuditReceive
> AS
> DECLARE @.AuditMessage TABLE (EventData xml);
> RECEIVE CAST(message_body AS XML)
> FROM AuditQueue
> INTO @.AuditMessage
> INSERT INTO dbo.Audit (PostTime, EventType, LoginName, UserName, ServerName,
> DatabaseName, ObjectType, SchemaName, ObjectName, EventData)
> SELECT CAST(EventData.query('data(//PostTime)') AS varchar(23)),
> CAST(EventData.query('data(//EventType)') AS sysname),
> CAST(EventData.query('data(//LoginName)') AS sysname),
> CAST(EventData.query('data(//UserName)') AS sysname),
> CAST(EventData.query('data(//ServerName)') AS sysname),
> CAST(EventData.query('data(//DatabaseName)') AS sysname),
> CAST(EventData.query('data(//ObjectType)') AS sysname),
> CAST(EventData.query('data(//SchemaName)') AS sysname),
> CAST(EventData.query('data(//ObjectName)') AS sysname),
> EventData
> FROM @.AuditMessage
> It's working nicely, but there is one weakness. I would like to have the
> RECEIVE and the INSERT as a transaction, but I can't get it working. Is it
> not possible to use RECEIVE in transactions?
> Best regards
> Ola Hallengren|||I overlooked one comment in Books Online"
"If the RECEIVE statement is not the first statement in a batch or stored
procedure, the preceding statement must be terminated with a semicolon (;),
the Transact-SQL statement terminator."
Now it's working.
About poisonous messages I have been doing some testing on that by changing
the datatypes in my auditing table, so that the insert fails. The queue then
gets deactivated as you write.
Now it's not so likely that the insert fails, since I have matching
datatypes. I mostly would like to have a transaction in case of a server
crash.
I have ordered the Roger Walters book today.
Thanks for you help.
/Ola
"Tibor Karaszi" wrote:
> > I would like to have the
> > RECEIVE and the INSERT as a transaction, but I can't get it working.
> In what way doesn't it work? Error messages? Something else?
>
> > Is it
> > not possible to use RECEIVE in transactions?
> Yes, but you want to be careful. Say you determine that you can't process this message so you
> rollback. Since messages are received in order, you will just read the same message next time.
> Essentially, you end up in an endless loop. This is called a "poison message" and after 5 rollbacks
> in a row, SB will disable the queue. I strongly suggest you get Roger Wolter's book on SB, this
> topic is handled in chapter 8.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
> news:38D8CD3E-9452-4EAE-A300-DB0F9B3A6F53@.microsoft.com...
> > Hello!
> >
> > I have an an event notifications audit solution that I'm working on. I am
> > using this activation procedure.
> >
> > CREATE PROCEDURE dbo.AuditReceive
> > AS
> > DECLARE @.AuditMessage TABLE (EventData xml);
> >
> > RECEIVE CAST(message_body AS XML)
> > FROM AuditQueue
> > INTO @.AuditMessage
> >
> > INSERT INTO dbo.Audit (PostTime, EventType, LoginName, UserName, ServerName,
> > DatabaseName, ObjectType, SchemaName, ObjectName, EventData)
> > SELECT CAST(EventData.query('data(//PostTime)') AS varchar(23)),
> > CAST(EventData.query('data(//EventType)') AS sysname),
> > CAST(EventData.query('data(//LoginName)') AS sysname),
> > CAST(EventData.query('data(//UserName)') AS sysname),
> > CAST(EventData.query('data(//ServerName)') AS sysname),
> > CAST(EventData.query('data(//DatabaseName)') AS sysname),
> > CAST(EventData.query('data(//ObjectType)') AS sysname),
> > CAST(EventData.query('data(//SchemaName)') AS sysname),
> > CAST(EventData.query('data(//ObjectName)') AS sysname),
> > EventData
> > FROM @.AuditMessage
> >
> > It's working nicely, but there is one weakness. I would like to have the
> > RECEIVE and the INSERT as a transaction, but I can't get it working. Is it
> > not possible to use RECEIVE in transactions?
> >
> > Best regards
> >
> > Ola Hallengren
>sql

RECEIVE in transactions

Hello!
I have an an event notifications audit solution that I'm working on. I am
using this activation procedure.
CREATE PROCEDURE dbo.AuditReceive
AS
DECLARE @.AuditMessage TABLE (EventData xml);
RECEIVE CAST(message_body AS XML)
FROM AuditQueue
INTO @.AuditMessage
INSERT INTO dbo.Audit (PostTime, EventType, LoginName, UserName, ServerName,
DatabaseName, ObjectType, SchemaName, ObjectName, EventData)
SELECT CAST(EventData.query('data(//PostTime)') AS varchar(23)),
CAST(EventData.query('data(//EventType)') AS sysname),
CAST(EventData.query('data(//LoginName)') AS sysname),
CAST(EventData.query('data(//UserName)') AS sysname),
CAST(EventData.query('data(//ServerName)') AS sysname),
CAST(EventData.query('data(//DatabaseName)') AS sysname),
CAST(EventData.query('data(//ObjectType)') AS sysname),
CAST(EventData.query('data(//SchemaName)') AS sysname),
CAST(EventData.query('data(//ObjectName)') AS sysname),
EventData
FROM @.AuditMessage
It's working nicely, but there is one weakness. I would like to have the
RECEIVE and the INSERT as a transaction, but I can't get it working. Is it
not possible to use RECEIVE in transactions?
Best regards
Ola Hallengrenthe books on line tell:
"The RECEIVE statement removes received messages from the queue unless the
queue specifies message retention. When the RETENTION setting for the queue
is ON, the RECEIVE statement updates the status column to 1 and leaves the
messages in the queue. When a transaction that contains a RECEIVE statement
rolls back, all changes to the queue within the transaction are also rolled
back, returning messages to the queue."
Could you expose the source code of the transaction you tried? there's
something wrong but i cannot imagine what without seeing an example.
Gilberto Zampatti
"Ola Hallengren" wrote:

> Hello!
> I have an an event notifications audit solution that I'm working on. I am
> using this activation procedure.
> CREATE PROCEDURE dbo.AuditReceive
> AS
> DECLARE @.AuditMessage TABLE (EventData xml);
> RECEIVE CAST(message_body AS XML)
> FROM AuditQueue
> INTO @.AuditMessage
> INSERT INTO dbo.Audit (PostTime, EventType, LoginName, UserName, ServerNam
e,
> DatabaseName, ObjectType, SchemaName, ObjectName, EventData)
> SELECT CAST(EventData.query('data(//PostTime)') AS varchar(23)),
> CAST(EventData.query('data(//EventType)') AS sysname),
> CAST(EventData.query('data(//LoginName)') AS sysname),
> CAST(EventData.query('data(//UserName)') AS sysname),
> CAST(EventData.query('data(//ServerName)') AS sysname),
> CAST(EventData.query('data(//DatabaseName)') AS sysname),
> CAST(EventData.query('data(//ObjectType)') AS sysname),
> CAST(EventData.query('data(//SchemaName)') AS sysname),
> CAST(EventData.query('data(//ObjectName)') AS sysname),
> EventData
> FROM @.AuditMessage
> It's working nicely, but there is one weakness. I would like to have the
> RECEIVE and the INSERT as a transaction, but I can't get it working. Is it
> not possible to use RECEIVE in transactions?
> Best regards
> Ola Hallengren|||> I would like to have the
> RECEIVE and the INSERT as a transaction, but I can't get it working.
In what way doesn't it work? Error messages? Something else?

> Is it
> not possible to use RECEIVE in transactions?
Yes, but you want to be careful. Say you determine that you can't process th
is message so you
rollback. Since messages are received in order, you will just read the same
message next time.
Essentially, you end up in an endless loop. This is called a "poison message
" and after 5 rollbacks
in a row, SB will disable the queue. I strongly suggest you get Roger Wolter
's book on SB, this
topic is handled in chapter 8.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
news:38D8CD3E-9452-4EAE-A300-DB0F9B3A6F53@.microsoft.com...
> Hello!
> I have an an event notifications audit solution that I'm working on. I am
> using this activation procedure.
> CREATE PROCEDURE dbo.AuditReceive
> AS
> DECLARE @.AuditMessage TABLE (EventData xml);
> RECEIVE CAST(message_body AS XML)
> FROM AuditQueue
> INTO @.AuditMessage
> INSERT INTO dbo.Audit (PostTime, EventType, LoginName, UserName, ServerNam
e,
> DatabaseName, ObjectType, SchemaName, ObjectName, EventData)
> SELECT CAST(EventData.query('data(//PostTime)') AS varchar(23)),
> CAST(EventData.query('data(//EventType)') AS sysname),
> CAST(EventData.query('data(//LoginName)') AS sysname),
> CAST(EventData.query('data(//UserName)') AS sysname),
> CAST(EventData.query('data(//ServerName)') AS sysname),
> CAST(EventData.query('data(//DatabaseName)') AS sysname),
> CAST(EventData.query('data(//ObjectType)') AS sysname),
> CAST(EventData.query('data(//SchemaName)') AS sysname),
> CAST(EventData.query('data(//ObjectName)') AS sysname),
> EventData
> FROM @.AuditMessage
> It's working nicely, but there is one weakness. I would like to have the
> RECEIVE and the INSERT as a transaction, but I can't get it working. Is it
> not possible to use RECEIVE in transactions?
> Best regards
> Ola Hallengren|||I overlooked one comment in Books Online"
"If the RECEIVE statement is not the first statement in a batch or stored
procedure, the preceding statement must be terminated with a semicolon (;),
the Transact-SQL statement terminator."
Now it's working.
About poisonous messages I have been doing some testing on that by changing
the datatypes in my auditing table, so that the insert fails. The queue then
gets deactivated as you write.
Now it's not so likely that the insert fails, since I have matching
datatypes. I mostly would like to have a transaction in case of a server
crash.
I have ordered the Roger Walters book today.
Thanks for you help.
/Ola
"Tibor Karaszi" wrote:

> In what way doesn't it work? Error messages? Something else?
>
> Yes, but you want to be careful. Say you determine that you can't process
this message so you
> rollback. Since messages are received in order, you will just read the sam
e message next time.
> Essentially, you end up in an endless loop. This is called a "poison messa
ge" and after 5 rollbacks
> in a row, SB will disable the queue. I strongly suggest you get Roger Wolt
er's book on SB, this
> topic is handled in chapter 8.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in messag
e
> news:38D8CD3E-9452-4EAE-A300-DB0F9B3A6F53@.microsoft.com...
>

Rebuilding the Master Database

Hi
I thought that I had this procedure down pat, but just got
hit. What I am trying to do is change the collation
setting for the entire database. These settings only have
to apply to NEW databases, so the old ones I am dettaching
and then reattaching them after the rebuild.
The problem lies in that there are users and DTS packages
that are saved in the old MASTER and MSDB databases, and
when I restore them from old backups, the following
happens.
1) I cannot seem to recover the DTS packages.
2) The collation settings for these two databases revert
to the previous setting.
Any help please or is there a fool proof method of doing
this.
Cheers
PaulIt is not supported to have different collations between the system databases, so rebuild and then
restore a system database is not the way to go (because as you have noticed, you get the old
collation when you restore). So, do a rebuild and then re-create the stuff in master and msdb. You
can script jobs etc for msdb in EM. As for master, most important is probably logins, see KB's
listed below. But don't forget that there are other things to consider as well (sysconfigures,
sysservers etc).
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Paul Blackler" <pblackler@.themis.co.uk> wrote in message
news:084001c3adcd$e5b65aa0$a501280a@.phx.gbl...
> Hi
> I thought that I had this procedure down pat, but just got
> hit. What I am trying to do is change the collation
> setting for the entire database. These settings only have
> to apply to NEW databases, so the old ones I am dettaching
> and then reattaching them after the rebuild.
> The problem lies in that there are users and DTS packages
> that are saved in the old MASTER and MSDB databases, and
> when I restore them from old backups, the following
> happens.
> 1) I cannot seem to recover the DTS packages.
> 2) The collation settings for these two databases revert
> to the previous setting.
> Any help please or is there a fool proof method of doing
> this.
> Cheers
> Paulsql

Rebooting Mirrored Servers

New to SQL. Can you reboot the principal server after running updates without causing a failover?

If not what would the correct procedure be.

Do you have a witness?

If so, you could stop SQL Server on the mirror while you reboot the principal, that will prevent failover.

When I do this I let failover happen - it's a good test of it and it's pretty quick, once the principal is back up you can fail back over to it.

|||If there is no witness , Database Mirroring will get into disconnected state and resumes when able to communicate with Mirror.

Rearrange column

Hi!
I want to rearrange a column in a table within a stored procedure.
I got this table with this sample data:
ID Text Number
1 Testing 1
2 Testing2 2
3 Testing3 3
4 Testing4 4
5 Testing5 5
I want this:
ID Text Number
1 Testing 5
2 Testing2 4
3 Testing3 3
4 Testing4 2
5 Testing5 1
The numbers of rows varies so it must be dynamical and i want 2 inparameters
(startID and endID).
I hope someone can help me.
//MagnusNumber = endID - Number +1?
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"Mange" <Mange@.discussions.microsoft.com> wrote in message
news:777484C3-3018-48ED-81D8-5E9BF071DD1C@.microsoft.com...
> Hi!
> I want to rearrange a column in a table within a stored procedure.
> I got this table with this sample data:
> ID Text Number
> 1 Testing 1
> 2 Testing2 2
> 3 Testing3 3
> 4 Testing4 4
> 5 Testing5 5
> I want this:
> ID Text Number
> 1 Testing 5
> 2 Testing2 4
> 3 Testing3 3
> 4 Testing4 2
> 5 Testing5 1
> The numbers of rows varies so it must be dynamical and i want 2
> inparameters
> (startID and endID).
> I hope someone can help me.
> //Magnus
>|||When you say "rearrange a column", exactly what transformation are you
looking for? If you only specify start and end rows then what
determines the order of the other values you don't specify?
If this column is always to be dynamic then it doesn't really make
sense to have it in the table at all. Derive it in a query like this:
SELECT A.au_id, A.au_lname,
(SELECT COUNT(*)
FROM pubs.dbo.authors
WHERE au_id<=A.au_id) AS number
FROM pubs.dbo.authors AS A
ORDER BY A.au_id ;
David Portas
SQL Server MVP
--|||The meaning with the column "Number" is to display data sorted in webform.
But sometime this displayorder must be changed.
The rest of the columns must stay the same.
//Magnus
"David Portas" wrote:

> When you say "rearrange a column", exactly what transformation are you
> looking for? If you only specify start and end rows then what
> determines the order of the other values you don't specify?
> If this column is always to be dynamic then it doesn't really make
> sense to have it in the table at all. Derive it in a query like this:
> SELECT A.au_id, A.au_lname,
> (SELECT COUNT(*)
> FROM pubs.dbo.authors
> WHERE au_id<=A.au_id) AS number
> FROM pubs.dbo.authors AS A
> ORDER BY A.au_id ;
> --
> David Portas
> SQL Server MVP
> --
>|||Mange
create table #test
(
col int not null primary key,
col1 char(1)
)
insert into #test values (1,'a')
insert into #test values (2,'b')
insert into #test values (3,'c')
insert into #test values (4,'d')
select col,col1,
(select count(*) from #test t where t.col>=#test.col) from #test
"Mange" <Mange@.discussions.microsoft.com> wrote in message
news:5ABD8DA6-F591-4EFD-8C19-716042AB7010@.microsoft.com...
> The meaning with the column "Number" is to display data sorted in webform.
> But sometime this displayorder must be changed.
> The rest of the columns must stay the same.
> //Magnus
>
>
> "David Portas" wrote:
>|||>> The meaning with the column "Number" is to display data sorted in webform
. But sometime this displayorder must be changed. The rest of the columns mu
st stay the same. <<
Instead of the vague name "number", you should have used sometrhng like
"physical_display_position" so people maintaining the code would know
it is not a logical data element.
The basic principle of a tiered architecture is that display is done in
the front end and never in the back end. This a more basic programming
principle than just SQL and RDBMS.|||It doesnt matter what the intention with the column is.
Do you mean that all sorting is done in the webform ?
"--CELKO--" wrote:

> Instead of the vague name "number", you should have used sometrhng like
> "physical_display_position" so people maintaining the code would know
> it is not a logical data element.
> The basic principle of a tiered architecture is that display is done in
> the front end and never in the back end. This a more basic programming
> principle than just SQL and RDBMS.
>

Rearrange column

Hi!
I'll try again
I want to rearrange a column in a table within a stored procedure.
I got this table with this sample data:
ID Text Displayorder
1 Testing 100
2 Testing2 200
3 Testing3 300
4 Testing4 400
5 Testing5 500
The numbers of rows varies so i want 2 inparameters
(start_ID and End_ID).
Example:
start_ID, End_ID (2,4)
Will return.
ID Text Number
1 Testing 100
2 Testing2 400
3 Testing3 300
4 Testing4 200
5 Testing5 500
Can this be done?
I hope someone can help me.
//MagnusHi Magnus
Assuming that your Id columns are not contiguous then you will need to rank
them and do something like:
DECLARE @.lower int, @.upper int
SELECT @.lower = 2, @.upper = 4
SELECT [ASC].ID, [ASC].[Text],
CASE WHEN [ASC].[ORDER] >= @.lower AND [ASC].[ORDER] <= @.upper THEN
[DESC].DisplayOrder ELSE [ASC].DisplayOrder END AS Number
FROM ( SELECT (Select count(*) FROM MyDisplays M where m.id <= D.id) AS
[Order],
D.id, D.[Text], D.DisplayOrder
FROM MyDisplays D ) [ASC]
JOIN
( SELECT (Select count(*) FROM MyDisplays M where m.id >= D.id) AS [Order],
D.id, D.[Text], D.DisplayOrder
FROM MyDisplays D ) [DESC] ON [ASC].[Order] = [DESC].[ORDER]
You can still use [id] in the case statement if necessary.
John
"Mange" wrote:

> Hi!
> I'll try again
> I want to rearrange a column in a table within a stored procedure.
> I got this table with this sample data:
> ID Text Displayorder
> 1 Testing 100
> 2 Testing2 200
> 3 Testing3 300
> 4 Testing4 400
> 5 Testing5 500
> The numbers of rows varies so i want 2 inparameters
> (start_ID and End_ID).
> Example:
> start_ID, End_ID (2,4)
> Will return.
> ID Text Number
> 1 Testing 100
> 2 Testing2 400
> 3 Testing3 300
> 4 Testing4 200
> 5 Testing5 500
> Can this be done?
> I hope someone can help me.
> //Magnus
>|||Thanks but..
That doesnt save the result into that table.
"John Bell" wrote:
> Hi Magnus
> Assuming that your Id columns are not contiguous then you will need to ran
k
> them and do something like:
> DECLARE @.lower int, @.upper int
> SELECT @.lower = 2, @.upper = 4
> SELECT [ASC].ID, [ASC].[Text],
> CASE WHEN [ASC].[ORDER] >= @.lower AND [ASC].[ORDER] <= @.upper THEN
> [DESC].DisplayOrder ELSE [ASC].DisplayOrder END AS Number
> FROM ( SELECT (Select count(*) FROM MyDisplays M where m.id <= D.id) AS
> [Order],
> D.id, D.[Text], D.DisplayOrder
> FROM MyDisplays D ) [ASC]
> JOIN
> ( SELECT (Select count(*) FROM MyDisplays M where m.id >= D.id) AS [Order],
> D.id, D.[Text], D.DisplayOrder
> FROM MyDisplays D ) [DESC] ON [ASC].[Order] = [DESC].[ORDER]
> You can still use [id] in the case statement if necessary.
> John
>
> "Mange" wrote:
>|||> That doesnt save the result into that table.
No, it does not, but you can *use* it in your procedure to make it do whan
you need. :)
ML|||It doesnt work.
The result is the whole table.
"John Bell" wrote:
> Hi Magnus
> Assuming that your Id columns are not contiguous then you will need to ran
k
> them and do something like:
> DECLARE @.lower int, @.upper int
> SELECT @.lower = 2, @.upper = 4
> SELECT [ASC].ID, [ASC].[Text],
> CASE WHEN [ASC].[ORDER] >= @.lower AND [ASC].[ORDER] <= @.upper THEN
> [DESC].DisplayOrder ELSE [ASC].DisplayOrder END AS Number
> FROM ( SELECT (Select count(*) FROM MyDisplays M where m.id <= D.id) AS
> [Order],
> D.id, D.[Text], D.DisplayOrder
> FROM MyDisplays D ) [ASC]
> JOIN
> ( SELECT (Select count(*) FROM MyDisplays M where m.id >= D.id) AS [Order],
> D.id, D.[Text], D.DisplayOrder
> FROM MyDisplays D ) [DESC] ON [ASC].[Order] = [DESC].[ORDER]
> You can still use [id] in the case statement if necessary.
> John
>
> "Mange" wrote:
>|||Hi
It does what you specified with the data that you gave. You have not
specified what your restriction should be but you should be able to use a
WHERE clause in each of the derived tables to do what you require.
John
"Mange" wrote:
> Thanks but..
> That doesnt save the result into that table.
>
> "John Bell" wrote:
>|||If you actually want to change the data try:
DECLARE @.lower int, @.upper int
SELECT @.lower = 2, @.upper = 4
UPDATE O
SET DisplayOrder = N.DisplayOrder
FROM MyDisplays O
JOIN MyDisplays N ON ( O.id = @.lower AND N.id = @.upper ) OR ( O.id = @.upper
AND N.id = @.lower )
John
"Mange" wrote:
> Thanks but..
> That doesnt save the result into that table.
>
> "John Bell" wrote:
>|||Hi
To be more precise.
The result is exactly like if i would have used Select * from MyDisplays
"John Bell" wrote:
> Hi
> It does what you specified with the data that you gave. You have not
> specified what your restriction should be but you should be able to use a
> WHERE clause in each of the derived tables to do what you require.
> John
> "Mange" wrote:
>|||Many Thanks John you are a star.
I'm sorry about my bad english and my poor knowledge about SQL.
Thanks again it works know.
//Magnus
"John Bell" wrote:
> If you actually want to change the data try:
> DECLARE @.lower int, @.upper int
> SELECT @.lower = 2, @.upper = 4
> UPDATE O
> SET DisplayOrder = N.DisplayOrder
> FROM MyDisplays O
> JOIN MyDisplays N ON ( O.id = @.lower AND N.id = @.upper ) OR ( O.id = @.uppe
r
> AND N.id = @.lower )
> John
> "Mange" wrote:
>

Re-architect a Stored Procedure...

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) 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 having
witnessed 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