Add values

[ynnepztem]

SQL Server 2000
Windows NT 4.0

Table 1
NumberOfItems (int)

Table 2
ItemProcessed (char)
Date (datetime)

I need a stored procedure that will add the count from ItemProcessed for the current date in Table2 to the NumberOfItem in Table1. If there is already a value in Table1, the new count should be added to it.

 

[Ankor]

It will probably be something like this:

declare @curdate datetime
declare @n int
declare @curcount int
set @curdate = (select getdate())
set @n = (select count(ItemProcessed) from table2 where date=@curdate)
set @curcount = (select NumberOfItems from table1)
update table1
set NumberOfItems=@curcount+@n

You might need to change the date reference since getdate() returnes date and time.

 

[tlbroadbent]

Variables are not needed in this . One SQL statement with a sub-query can perform the update.

Update table1
Set NumberOfItems = curcount +
(Select count(*) From table2
Where [date] = convert(char(10), getdate(), 120)) If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[SQLBill]

Terry,

Shouldn't that be:

Update table1
Set NumberOfItems = NumberOfItems +
(Select count(*) From table2
Where [date] = convert(char(10), getdate(), 120))

-SQLBill

 

[tlbroadbent]

You are corect, SQLBill. Thanks for catching the error. If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[ynnepztem]

Thanks for your quick responses. My table is now updated.