Running total using day before record.

I have 1 record per day with a Daily Difference. I would like to also keep
a
running difference. So I am looking for a way to insert the value for
RunningDiff
RunningDiff = PreviousDay's RunningDiff - current day's DailyDiff
date, $ShouldHave, $Have, DailyDiff, RunningDiff
1/23/2006, 12.50, 10.00, 2.50, 4.00
1/24/2006, 10.00, 7.00, 3.00, 1.00
1/25/2006, 10.00, 15.00, - 5.00, 6.00
1/26/2006, 17.00, 13.00, 4.00, 2.00
The RunnningDiff I can have update at the time a record is added, or can
have a stored procedure run multiple times throughout the day to update the
value.
thank you for any ideas,Ashley,
This could be achieved using cursors although I wouldn't recomend it if
the Table is going to be huge and processing time is going to be an
issue.
Barry|||Using a cursor would be find in this problem. Any suggestions of an example
to look at?
thanks!
"Barry" wrote:

> Ashley,
> This could be achieved using cursors although I wouldn't recomend it if
> the Table is going to be huge and processing time is going to be an
> issue.
> Barry
>|||Ahsley,
Have a look at this - I would test it in some different scenarios...I
haven't got time to fully test it I'm afraid.
HTH
Barry
Create Table Test
(
ProcessingDate Datetime Not Null,
TargetTotal Money Not Null,
CurrentTotal Money Not Null,
DailyDiff Money Null,
RunningDiff Money Null
)
Insert Into Test (ProcessingDate, TargetTotal, CurrentTotal)
Values ('2006-01-23', 12.50, 10.00)
Insert Into Test (ProcessingDate, TargetTotal, CurrentTotal)
Values ('2006-01-24', 10.00, 7.00)
Insert Into Test (ProcessingDate, TargetTotal, CurrentTotal)
Values ('2006-01-25', 10.00, 15.00)
Insert Into Test (ProcessingDate, TargetTotal, CurrentTotal)
Values ('2006-01-26', 17.00, 13.00)
Insert Into Test (ProcessingDate, TargetTotal, CurrentTotal)
Values ('2006-01-27', 16.50, 14.00)
Declare @.ProcessingDateFirst Datetime,
@.TargetTotalFirst Money,
@.CurrentTotalFirst Money,
@.DailyDiffFirst Money,
@.RunningDiffFirst Money,
@.ProcessingDateSecond Datetime,
@.TargetTotalSecond Money,
@.CurrentTotalSecond Money,
@.DailyDiffSecond Money,
@.RunningDiffSecond Money
Declare FirstRowCursor Cursor For
Select ProcessingDate, TargetTotal, CurrentTotal, DailyDiff,
RunningDiff
From Test
Open FirstRowCursor
Fetch Next From FirstRowCursor Into @.ProcessingDateFirst,
@.TargetTotalFirst, @.CurrentTotalFirst,
@.DailyDiffFirst, @.RunningDiffFirst
Declare SecondRowCursor Cursor For
Select ProcessingDate, TargetTotal, CurrentTotal, DailyDiff,
RunningDiff
From Test
Open SecondRowCursor
Fetch Next From SecondRowCursor Into @.ProcessingDateSecond,
@.TargetTotalSecond, @.CurrentTotalSecond,
@.DailyDiffSecond, @.RunningDiffSecond
Fetch Next From SecondRowCursor Into @.ProcessingDateSecond,
@.TargetTotalSecond, @.CurrentTotalSecond,
@.DailyDiffSecond, @.RunningDiffSecond
While @.@.Fetch_Status = 0
Begin
If @.DailyDiffFirst Is Null And @.RunningDiffFirst Is Null
Begin
Update Test
Set DailyDiff = TargetTotal - CurrentTotal, @.DailyDiffFirst =
TargetTotal - CurrentTotal,
RunningDiff = TargetTotal - CurrentTotal, @.RunningDiffFirst =
TargetTotal - CurrentTotal
Where ProcessingDate = @.ProcessingDateFirst
End
If @.DailyDiffFirst Is Not Null And @.RunningDiffFirst Is Not Null
Begin
Update Test
Set DailyDiff = TargetTotal - CurrentTotal
Where ProcessingDate = @.ProcessingDateSecond
Update Test
Set RunningDiff = @.RunningDiffFirst - DailyDiff
Where ProcessingDate = @.ProcessingDateSecond
End
Fetch Next From FirstRowCursor Into @.ProcessingDateFirst,
@.TargetTotalFirst, @.CurrentTotalFirst,
@.DailyDiffFirst, @.RunningDiffFirst
Fetch Next From SecondRowCursor Into @.ProcessingDateSecond,
@.TargetTotalSecond, @.CurrentTotalSecond,
@.DailyDiffSecond, @.RunningDiffSecond
End
Close FirstRowCursor
Close SecondRowCursor
Deallocate FirstRowCursor
Deallocate SecondRowCursor
Select * From Test|||Perfect.. Thank you!
"Barry" wrote:

> Ahsley,
> Have a look at this - I would test it in some different scenarios...I
> haven't got time to fully test it I'm afraid.
> HTH
> Barry
>
> Create Table Test
> (
> ProcessingDate Datetime Not Null,
> TargetTotal Money Not Null,
> CurrentTotal Money Not Null,
> DailyDiff Money Null,
> RunningDiff Money Null
> )
>
> Insert Into Test (ProcessingDate, TargetTotal, CurrentTotal)
> Values ('2006-01-23', 12.50, 10.00)
> Insert Into Test (ProcessingDate, TargetTotal, CurrentTotal)
> Values ('2006-01-24', 10.00, 7.00)
> Insert Into Test (ProcessingDate, TargetTotal, CurrentTotal)
> Values ('2006-01-25', 10.00, 15.00)
> Insert Into Test (ProcessingDate, TargetTotal, CurrentTotal)
> Values ('2006-01-26', 17.00, 13.00)
> Insert Into Test (ProcessingDate, TargetTotal, CurrentTotal)
> Values ('2006-01-27', 16.50, 14.00)
>
> Declare @.ProcessingDateFirst Datetime,
> @.TargetTotalFirst Money,
> @.CurrentTotalFirst Money,
> @.DailyDiffFirst Money,
> @.RunningDiffFirst Money,
> @.ProcessingDateSecond Datetime,
> @.TargetTotalSecond Money,
> @.CurrentTotalSecond Money,
> @.DailyDiffSecond Money,
> @.RunningDiffSecond Money
>
> Declare FirstRowCursor Cursor For
> Select ProcessingDate, TargetTotal, CurrentTotal, DailyDiff,
> RunningDiff
> From Test
>
> Open FirstRowCursor
> Fetch Next From FirstRowCursor Into @.ProcessingDateFirst,
> @.TargetTotalFirst, @.CurrentTotalFirst,
> @.DailyDiffFirst, @.RunningDiffFirst
>
>
> Declare SecondRowCursor Cursor For
> Select ProcessingDate, TargetTotal, CurrentTotal, DailyDiff,
> RunningDiff
> From Test
>
> Open SecondRowCursor
> Fetch Next From SecondRowCursor Into @.ProcessingDateSecond,
> @.TargetTotalSecond, @.CurrentTotalSecond,
> @.DailyDiffSecond, @.RunningDiffSecond
> Fetch Next From SecondRowCursor Into @.ProcessingDateSecond,
> @.TargetTotalSecond, @.CurrentTotalSecond,
> @.DailyDiffSecond, @.RunningDiffSecond
>
>
> While @.@.Fetch_Status = 0
> Begin
> If @.DailyDiffFirst Is Null And @.RunningDiffFirst Is Null
> Begin
> Update Test
> Set DailyDiff = TargetTotal - CurrentTotal, @.DailyDiffFirst =
> TargetTotal - CurrentTotal,
> RunningDiff = TargetTotal - CurrentTotal, @.RunningDiffFirst =
> TargetTotal - CurrentTotal
> Where ProcessingDate = @.ProcessingDateFirst
>
> End
>
> If @.DailyDiffFirst Is Not Null And @.RunningDiffFirst Is Not Null
> Begin
>
> Update Test
> Set DailyDiff = TargetTotal - CurrentTotal
> Where ProcessingDate = @.ProcessingDateSecond
> Update Test
> Set RunningDiff = @.RunningDiffFirst - DailyDiff
> Where ProcessingDate = @.ProcessingDateSecond
> End
>
> Fetch Next From FirstRowCursor Into @.ProcessingDateFirst,
> @.TargetTotalFirst, @.CurrentTotalFirst,
> @.DailyDiffFirst, @.RunningDiffFirst
> Fetch Next From SecondRowCursor Into @.ProcessingDateSecond,
> @.TargetTotalSecond, @.CurrentTotalSecond,
> @.DailyDiffSecond, @.RunningDiffSecond
>
> End
> Close FirstRowCursor
> Close SecondRowCursor
> Deallocate FirstRowCursor
> Deallocate SecondRowCursor
>
> Select * From Test
>

Running total count in stored procedure

in my procedure, I want to count the number of rows that have errored
during an insert statement - each row is evaluated using a cursor, so
I am processing one row at a time for the insert. My total count to
be displayed is inside the cursor, but after the last fetch is called.
Wouldn't this display the last count? The problem is that the count is
always 1. Can anyone help?

here is my code,

... cursor fetch
begin ... cursor
if error then:
begin

INSERT INTO US_ACCT_ERRORS(ERROR_NUMBER, ERROR_DESC, cUSTOMERNUMBER,
CUSTOMERNAME, ADDRESS1, ADDRESS2, CITY,
STATE, POSTALCODE, CONTACT, PHONE, SALESREPCODE,
PRICELEVEL, TERMSCODE, DISCPERCENT, TAXCODE,
USERCOMMENT, CURRENCY, EMAILADDRESS, CUSTOMERGROUP,
CUSTINDICATOR, DT_LOADED)
VALUES(@.ERRORNUM, @.ERRORDESC,
@.CUSTOMERNUMBER, @.CUSTOMERNAME, @.ADDRESS1, @.ADDRESS2, @.CITY,
@.STATE, @.POSTALCODE, @.CONTACT, @.PHONE, @.SALESREPCODE,
@.PRICELEVEL, @.TERMSCODE, @.DISCPERCENT, @.TAXCODE,
@.USERCOMMENT, @.CURRENCY, @.EMAILADDRESS, @.CUSTOMERGROUP,
@.CUSTINDICATOR, @.DTLOADED)

SET @.ERRORCNT = @.ERRORCNT + 1

END --error

--
FETCH NEXT FROM CERNO_US INTO
@.CUSTOMERNUMBER, @.CUSTOMERNAME, @.ADDRESS1, @.ADDRESS2, @.CITY, @.STATE,
@.POSTALCODE, @.CONTACT,@.PHONE,@.SALESREPCODE, @.PRICELEVEL,@.TERMSCODE,
@.DISCPERCENT, @.TAXCODE, @.USERCOMMENT, @.CURRENCY,@.EMAILADDRESS,
@.CUSTOMERGROUP, @.CUSTINDICATOR, @.DTLOADED
--
IF @.ERRORCNT > 0
INSERT INTO PROCEDURE_RESULTS(PROCEDURE_NAME, TABLE_NAME, ROW_COUNT,
STATUS)
VALUES('LOAD_ACCOUNTS', 'LOAD_ERNO_US_ACCT', @.ERRORCNT, 'FAILED
INSERT/UPDATE')

END -- cursor
CLOSE CERNO_US
DEALLOCATE CERNO_USTracey (tracey.lemer@.itsservices.com) writes:
> in my procedure, I want to count the number of rows that have errored
> during an insert statement - each row is evaluated using a cursor, so
> I am processing one row at a time for the insert. My total count to
> be displayed is inside the cursor, but after the last fetch is called.
> Wouldn't this display the last count? The problem is that the count is
> always 1. Can anyone help?

As I read your code, you insert a row into PROCEDURE_RESULTS as soon as
@.ERRORCNT is > 0, that is 1.

I don't know what you mean with "be displayed is inside the cursor, but
after the last fetch is called" - that makes no sense to me. You don't
really display the data what I can see, and you should just as well
insert into PROCEDURE_RESULTS after you have dealloacated the cursor.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

running stored procedure every 30 minutes

is there a way to run a stored procedure every 30 minutes or so? its a
simple insert statement that i want to run.
thanks,
kam
Set up a SQL Server Agent Job
Via the Enterprise Manager for your database choose
Management -> Jobs
"kameron" <kameron@.discussions.microsoft.com> wrote in message
news:58B18A59-7566-4F56-82FF-18CDEE3BF0DF@.microsoft.com...
> is there a way to run a stored procedure every 30 minutes or so? its a
> simple insert statement that i want to run.
>
> thanks,
> kam
|||Yes, look at SQL Server Agent in Books Online, it has a concept called
scheduled jobs that provide exactly this functionality. You can see
http://www.aspfaq.com/2403 for a very casual walk through the GUI.
http://www.aspfaq.com/
(Reverse address to reply.)
"kameron" <kameron@.discussions.microsoft.com> wrote in message
news:58B18A59-7566-4F56-82FF-18CDEE3BF0DF@.microsoft.com...
> is there a way to run a stored procedure every 30 minutes or so? its a
> simple insert statement that i want to run.
>
> thanks,
> kam
|||> Via the Enterprise Manager for your database choose
> Management -> Jobs
Well, just to be explicit, the management node is at the server level, not
the database level.
A
|||Sure, I wasn't clear. Insert a comma between "Manager" and "for" in
the sentence. Mr Granfield would kill me if he saw that run on sentence.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u8v8i00yEHA.2788@.TK2MSFTNGP15.phx.gbl...
> Well, just to be explicit, the management node is at the server level, not
> the database level.
> A
>
|||Actually, what I thought you were implying was that there was a
management/jobs section within each database within a specific server in
Enterprise Manager, when of course management is server-wide so it appears
at the same level as databases.
http://www.aspfaq.com/
(Reverse address to reply.)
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:#o2SE50yEHA.2692@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Sure, I wasn't clear. Insert a comma between "Manager" and "for" in
> the sentence. Mr Granfield would kill me if he saw that run on sentence.
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:u8v8i00yEHA.2788@.TK2MSFTNGP15.phx.gbl...
not
>

running stored procedure every 30 minutes

is there a way to run a stored procedure every 30 minutes or so? its a
simple insert statement that i want to run.
thanks,
kamSet up a SQL Server Agent Job
Via the Enterprise Manager for your database choose
Management -> Jobs
"kameron" <kameron@.discussions.microsoft.com> wrote in message
news:58B18A59-7566-4F56-82FF-18CDEE3BF0DF@.microsoft.com...
> is there a way to run a stored procedure every 30 minutes or so? its a
> simple insert statement that i want to run.
>
> thanks,
> kam|||Yes, look at SQL Server Agent in Books Online, it has a concept called
scheduled jobs that provide exactly this functionality. You can see
http://www.aspfaq.com/2403 for a very casual walk through the GUI.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"kameron" <kameron@.discussions.microsoft.com> wrote in message
news:58B18A59-7566-4F56-82FF-18CDEE3BF0DF@.microsoft.com...
> is there a way to run a stored procedure every 30 minutes or so? its a
> simple insert statement that i want to run.
>
> thanks,
> kam|||> Via the Enterprise Manager for your database choose
> Management -> Jobs
Well, just to be explicit, the management node is at the server level, not
the database level.
A|||Sure, I wasn't clear. Insert a comma between "Manager" and "for" in
the sentence. Mr Granfield would kill me if he saw that run on sentence.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u8v8i00yEHA.2788@.TK2MSFTNGP15.phx.gbl...
> > Via the Enterprise Manager for your database choose
> >
> > Management -> Jobs
> Well, just to be explicit, the management node is at the server level, not
> the database level.
> A
>|||Actually, what I thought you were implying was that there was a
management/jobs section within each database within a specific server in
Enterprise Manager, when of course management is server-wide so it appears
at the same level as databases.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:#o2SE50yEHA.2692@.TK2MSFTNGP10.phx.gbl...
> Sure, I wasn't clear. Insert a comma between "Manager" and "for" in
> the sentence. Mr Granfield would kill me if he saw that run on sentence.
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:u8v8i00yEHA.2788@.TK2MSFTNGP15.phx.gbl...
> > > Via the Enterprise Manager for your database choose
> > >
> > > Management -> Jobs
> >
> > Well, just to be explicit, the management node is at the server level,
not
> > the database level.
> >
> > A
> >
> >
>

running stored procedure every 30 minutes

is there a way to run a stored procedure every 30 minutes or so? its a
simple insert statement that i want to run.
thanks,
kamSet up a SQL Server Agent Job
Via the Enterprise Manager for your database choose
Management -> Jobs
"kameron" <kameron@.discussions.microsoft.com> wrote in message
news:58B18A59-7566-4F56-82FF-18CDEE3BF0DF@.microsoft.com...
> is there a way to run a stored procedure every 30 minutes or so? its a
> simple insert statement that i want to run.
>
> thanks,
> kam|||Yes, look at SQL Server Agent in Books Online, it has a concept called
scheduled jobs that provide exactly this functionality. You can see
http://www.aspfaq.com/2403 for a very casual walk through the GUI.
http://www.aspfaq.com/
(Reverse address to reply.)
"kameron" <kameron@.discussions.microsoft.com> wrote in message
news:58B18A59-7566-4F56-82FF-18CDEE3BF0DF@.microsoft.com...
> is there a way to run a stored procedure every 30 minutes or so? its a
> simple insert statement that i want to run.
>
> thanks,
> kam|||> Via the Enterprise Manager for your database choose
> Management -> Jobs
Well, just to be explicit, the management node is at the server level, not
the database level.
A|||Sure, I wasn't clear. Insert a comma between "Manager" and "for" in
the sentence. Mr Granfield would kill me if he saw that run on sentence.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u8v8i00yEHA.2788@.TK2MSFTNGP15.phx.gbl...
> Well, just to be explicit, the management node is at the server level, not
> the database level.
> A
>|||Actually, what I thought you were implying was that there was a
management/jobs section within each database within a specific server in
Enterprise Manager, when of course management is server-wide so it appears
at the same level as databases.
http://www.aspfaq.com/
(Reverse address to reply.)
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:#o2SE50yEHA.2692@.TK2MSFTNGP10.phx.gbl...
> Sure, I wasn't clear. Insert a comma between "Manager" and "for" in
> the sentence. Mr Granfield would kill me if he saw that run on sentence.
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:u8v8i00yEHA.2788@.TK2MSFTNGP15.phx.gbl...
not[vbcol=seagreen]
>

Running SP with a different user

We are using SQL2000. User insert table in one database DB1 and trigger insert the record into another database DB2. In this scenario, is it possible have a trigger in DB1 to execute a stored procedure in DB2 with a different user?

try define trigger with execute as clause

CREATE TRIGGER YourNAme ON Yourtable
WITH EXECUTE AS 'CompanyDomain\SqlUser1'

AFTER INSERT, UPDATE AS.......

--call your procedure here

GO

Running SP with a different user

We are using SQL2000. User insert table in one database DB1 and trigger insert the record into another database DB2. In this scenario, is it possible have a trigger in DB1 to execute a stored procedure in DB2 with a different user?

I think you may use openrowset in the trigger to update the other database. it is not the best practice but it is the only solution I know

OPENROWSET('SQLOLEDB','ServerName';'user';'Password',

'SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname')|||

hello,

if you will be doing this most of the times

you might as well use the sp_addlinkserver options "useself" set to false to create a

linked server

Syntax
sp_addlinkedsrvlogin [ @.rmtsrvname = ] 'rmtsrvname'
[ , [ @.useself = ] 'useself' ]
[ , [ @.locallogin = ] 'locallogin' ]
[ , [ @.rmtuser = ] 'rmtuser' ]
[ , [ @.rmtpassword = ] 'rmtpassword' ]

by setting useself to false you can enter the desired username and password

regards

|||

USE DB1

Go

EXEC sp_

Trigger ON INSERT

USE DB2

Go

EXEC sp_

Adamus

|||i wish the new sp for 200 would allow "execute as" feature in 2k5|||

If this is in the trigger, is there any way I can run stored procedure with OpenRowSet and make trigger code invisible to the user who is inserting record since the password is there.
Thanks,