November 9, 2009 at 9:12 am
I have some complicated task (sure for me :)) to accomplish, but I do not know how.
I have a following table:
ID Date Curr Currency Rate DeltaRate
11/1/2009 100USD1.6700NULL
21/1/2009 200EUR2.3500NULL
31/2/2009 100USD1.6900NULL
41/2/2009 200EUR2.4000NULL
51/3/2009 100USD1.6200NULL
61/3/2009 200EUR2.3000NULL
What I need to perfom is to calculate the Deltarate based on the following
For the first records with date 01/01/2009 I do not need it to be calculated. Delta is calculated as next day rate - previous date rate
In the filed DeltaRate for the date 1/2/2009 I have to have the following record for USD 0.02 (Ihave got it by 1.69 (rate for 1/2/2009) - 1.67 (rate for 1/2/2009). I have difficulties to write query where it will do for multy currency and somehow will match date and currency and calculate the delta.
Expected result for this tabe are:
ID Date Curr Currency Rate DeltaRate
11/1/2009 100USD1.6700NULL
21/1/2009 200EUR2.3500NULL
31/2/2009 100USD1.69000.02
41/2/2009 200EUR2.40000.05
51/3/2009 100USD1.62000.07
61/3/2009 200EUR2.3000-0.1
Thank you for help in advance.
November 9, 2009 at 9:58 am
Hi try the following example, a table is created with the dummy data in and then an update is performed on the table where the table is joined into itself using the currency code and the date minus one day, and the difference is then calculcated from the two values
DECLARE @table AS TABLE
(ID INT NULL,
DATE DATETIME NULL,
curr INT NULL,
currency VARCHAR(3) NULL,
rate DECIMAL(8,4) NULL,
deltarate DECIMAL(8,4) NULL
)
INSERT INTO @table VALUES (1, '2009-01-01',100 ,'USD', 1.6700, NULL)
INSERT INTO @table VALUES (2, '2009-01-01',200 ,'EUR', 2.3500, NULL)
INSERT INTO @table VALUES (3, '2009-01-02',100 ,'USD', 1.6900, NULL)
INSERT INTO @table VALUES (4, '2009-01-02',200 ,'EUR', 2.4000, NULL)
INSERT INTO @table VALUES (5, '2009-01-03',100 ,'USD', 1.6200, NULL)
INSERT INTO @table VALUES (6, '2009-01-03',200 ,'EUR', 2.3000, NULL)
;
UPDATE a
SET deltarate = a.rate - b.rate
FROM @table AS a
LEFT OUTER JOIN @table AS b
ON a.curr = b.curr
AND a.date-1 = b.date
SELECT * FROM @table
November 9, 2009 at 11:31 pm
Thank you Nick for respond. I will try it today and let you know the result.:-)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply