Solved

# How SP can get yesterday data and insert into table with current date?

Posted on 2009-04-12
162 Views
Hi

There is a exchanges table, which need to keep updated exchange rates everyday. If today exchange rates do not change, then just copy yesterday rates and insert to the table.

There are over 20 rates for a single day.

I wrote the following SP.

1)  How to select yesterday data? getDate()-1 ?
2) How to insert all yesterday data to today data?

``````ALTER PROCEDURE [dbo].[SP_Update_Exchanges]
@id int
AS
BEGIN
Declare @currency int
Declare @referencerate decimal(18,6)
Declare @sell decimal(18,6)
SET NOCOUNT ON;
BEGIN
END
END
``````
0
Question by:techques
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3

LVL 5

Expert Comment

ID: 24124944
CREATE PROCEDURE [dbo].[SP_Update_Exchanges]
(@fordate datetime,
@referencerate decimal(18,6),
@sell decimal(18,6))
AS
BEGIN
SET NOCOUNT ON;
select * from exchanges where systemdate = @fordate
if @@rowcount = 1 then
begin
where systemdate = @fordate
end
begin
SELECT @currency=currency,
where systemdate = datediff(day,1,@fordate)

end
else
END
0

LVL 5

Accepted Solution

mfhorizon earned 50 total points
ID: 24124951
You should write the procedure similar to this... and call the procedure like below:
SP_Update_Exchanges '02-APR-2009',22,20,20.5

IN CASE THERE IS NO CHANGE IN THE EXCHANGE CALL PROCEDURE LIKE THIS
SP_Update_Exchanges '02-APR-2009',0,0,0 (in this case it will copy from 1-APR-2009

``````CREATE PROCEDURE [dbo].[SP_Update_Exchanges]
(@fordate datetime,
@referencerate decimal(18,6),
@sell decimal(18,6))
AS
BEGIN
SET NOCOUNT ON;
select * from exchanges where systemdate = @fordate
if @@rowcount = 1 then
begin
where systemdate = @fordate
end
begin
SELECT @currency=currency,
where systemdate = datediff(day,1,@fordate)

end
else
END
``````
0

LVL 5

Expert Comment

ID: 24124960
Believing procedure is being called from a program, let me read out the procedure commands.

1. Procedure accepts
@fordate: the date for which exchange rate to be updated
@referencerate: reference rate for the date @fordate
@sellrate for the date @fordate
2. Procedure first checks if there is already an entry available in exchangerate table for the date @fordate (in case of multiple updation of exchange rate in same date).
3. If there is already a record for @fordate system will update buy and sell value with @buy and @sell (passed from your program)
4. Else system will check if @buy and @sell values are zero (means there is no change in the exchange rate) system will copy the previous date buy and sell value and will insert a record for date @fordate
5. Finally If above both conditions are not met which means it's a new valid entry procedure will insert a new record for date @fordate in exchange table using the exchange rate passed in procedure paramters.

0

Author Comment

ID: 24371516
Hi, sorry for my very late reply. I tried to change your SP for my need. But, it just copy the last record of exchanges to today.

There are 30 exchanges rates a day. But, it just copy and insert the last record.

And, I do not need to input the buy, sell, reference rate as I write a aspx page to update them.

``````ALTER PROCEDURE [dbo].[SP_Update_Exchanges]
@fordate datetime
AS
BEGIN
BEGIN TRAN TRAN_SP_Ex
Declare @currencyid int
Declare @currency nvarchar(50)
Declare @referencerate decimal(18,6)
Declare @sell decimal(18,6)

SET NOCOUNT ON;
select * from exchanges where systemdate = @fordate
if @@rowcount <> 1
begin
SELECT @currencyid=currencyid, @currency=currency,
where systemdate = datediff(day,1,@fordate)

end
IF @@ERROR <> 0
ROLLBACK TRAN TRAN_SP_Ex
ELSE
COMMIT TRAN TRAN_SP_Ex
END
``````
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
###### Suggested Courses
Course of the Month2 days, 22 hours left to enroll