Solved

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

Posted on 2009-04-12
4
157 Views
Last Modified: 2012-05-06
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 @buy decimal(18,6)

Declare @sell decimal(18,6)

	SET NOCOUNT ON;

	SELECT @currency=currency, @referencerate=referencerate, @buy=buy, @sell=sell from exchanges where systemdate = getDate()-1

BEGIN

insert into exchanges (currency,referencerate,buy,sell,systemdate) 

values (@currency,@referencerate,@buy,@sell,getDate())

END

END

Open in new window

0
Comment
Question by:techques
  • 3
4 Comments
 
LVL 5

Expert Comment

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

            insert into exchanges (currency,referencerate,buy,sell,systemdate)
            values (@currency,@referencerate,@buy,@sell,@fordate)
      end
else
            insert into exchanges (currency,referencerate,buy,sell,systemdate)
            values (@currency,@referencerate,@buy,@sell,@fordate)
END
0
 
LVL 5

Accepted Solution

by:
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),

 @buy decimal(18,6),

 @sell decimal(18,6))

AS

BEGIN

SET NOCOUNT ON;

select * from exchanges where systemdate = @fordate

if @@rowcount = 1 then

	begin

		update exchanges set currency=@currency,referencerate=@referencerate,buy=@buy,sell=@sell

		where systemdate = @fordate

	end

else if (@buy=0 and @sell=0)

	begin

		SELECT @currency=currency, 

		@referencerate=referencerate, @buy=buy, @sell=sell from exchanges 

		where systemdate = datediff(day,1,@fordate)
 

		insert into exchanges (currency,referencerate,buy,sell,systemdate) 

		values (@currency,@referencerate,@buy,@sell,@fordate)

	end

else

		insert into exchanges (currency,referencerate,buy,sell,systemdate) 

		values (@currency,@referencerate,@buy,@sell,@fordate)

END

Open in new window

0
 
LVL 5

Expert Comment

by:mfhorizon
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
   @buyrate 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

by:techques
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 @buy 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, 

            @referencerate=referencerate, @buy=buy, @sell=sell from exchanges 

            where systemdate = datediff(day,1,@fordate)
 

            insert into exchanges (currencyid,currency,referencerate,buy,sell,systemdate) 

            values (@currencyid,@currency,@referencerate,@buy,@sell,@fordate)

      end

IF @@ERROR <> 0

	ROLLBACK TRAN TRAN_SP_Ex

ELSE

	COMMIT TRAN TRAN_SP_Ex

END

Open in new window

0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Having clause with Case 2 32
SQL help 5 51
Truncate vs Delete 63 102
How to place a condition in a filter criteria in t-sql (#2)? 10 42
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now