If value is null, use previous records value

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » If value is null, use previous records value

If value is null, use previous records value

Rate Topic

Display Mode

Topic Options

Author

Message

adb2303

Posted Wednesday, December 19, 2012 6:41 AM

SSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 8:02 AM
Points: 655, Visits: 1,860

Hi,
I have a table with exchange rates between 2 countries. There's 90 days of history for every exchange rate. I've found that there's some NULL's in the trade rate column, which I need to fill. It's been agreed that for the purposes of the report I'm working on, I can use the previous days trade rate for the exchange. howerver, I can't quite figure out how to get it.

Sample DDL and data, with expected results below:


create table test(
currency_to varchar(3),
currency_from varchar(3),
tradedate datetime,
traderate decimal(27,7)
)
go
insert into test(currency_to, currency_from, tradedate, traderate)
select 'GBP', 'EUR', CURRENT_TIMESTAMP, 1.2
UNION
SELECT 'GBP', 'EUR', CURRENT_TIMESTAMP -1, NULL
UNION
SELECT 'GBP', 'EUR', CURRENT_TIMESTAMP -2, 2.3
UNION
SELECT 'GBP', 'EUR', CURRENT_TIMESTAMP -3, 1.9
UNION
SELECT 'GBP', 'EUR', CURRENT_TIMESTAMP -4, 1.6
UNION
SELECT 'GBP', 'EUR', CURRENT_TIMESTAMP -5, NULL
UNION
SELECT 'GBP', 'EUR', CURRENT_TIMESTAMP -6, 2.4
UNION
SELECT 'GBP', 'EUR', CURRENT_TIMESTAMP -7, 1.3
UNION
SELECT 'GBP', 'EUR', CURRENT_TIMESTAMP -8, 1.1

SELECT currency_to, currency_from, tradedate, 
CASE WHEN traderate = NULL THEN tradedate = (select previousdaystraderateforsamecurrencymatch) ELSE traderate END FROM test

--EXPECTED OUTPUT

select 'GBP', 'EUR', CURRENT_TIMESTAMP, 1.2
UNION
SELECT 'GBP', 'EUR', CURRENT_TIMESTAMP -1, 2.3
UNION
SELECT 'GBP', 'EUR', CURRENT_TIMESTAMP -2, 2.3
UNION
SELECT 'GBP', 'EUR', CURRENT_TIMESTAMP -3, 1.9
UNION
SELECT 'GBP', 'EUR', CURRENT_TIMESTAMP -4, 1.6
UNION
SELECT 'GBP', 'EUR', CURRENT_TIMESTAMP -5, 2.4
UNION
SELECT 'GBP', 'EUR', CURRENT_TIMESTAMP -6, 2.4
UNION
SELECT 'GBP', 'EUR', CURRENT_TIMESTAMP -7, 1.3
UNION
SELECT 'GBP', 'EUR', CURRENT_TIMESTAMP -8, 1.1

Many thanks, Andrew

Post #1398368

sturner

Posted Wednesday, December 19, 2012 7:19 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 12:33 PM
Points: 1,315, Visits: 2,887

If you maintain the previous days trade rate for each currency (or a default trade rate) in a table (or a CTE from historical table) you can obtain the default rate from it in case the current rate is NULL using coalesce something like this:

SELECT currency_to, currency_from, tradedate,
COALESCE(A.traderate, B.traderate)
from test A
JOIN previousdaystraderate B on A.currency_to = B.currency_to
and A.currency_from = B.currency_from

The probability of survival is inversely proportional to the angle of arrival.

Post #1398397

GSquared

Posted Wednesday, December 19, 2012 7:32 AM

SSCoach

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:55 PM
Points: 15,442, Visits: 9,571

How's this:

IF OBJECT_ID(N'tempdb..#test') IS NOT NULL 
    DROP TABLE #test;
	
CREATE TABLE #test
    (currency_to VARCHAR(3),
     currency_from VARCHAR(3),
     tradedate DATETIME,
     traderate DECIMAL(27, 7));

INSERT  INTO #test
        (currency_to,
         currency_from,
         tradedate,
         traderate)
        SELECT  'GBP',
                'EUR',
                CURRENT_TIMESTAMP,
                1.2
        UNION
        SELECT  'GBP',
                'EUR',
                CURRENT_TIMESTAMP - 1,
                NULL
        UNION
        SELECT  'GBP',
                'EUR',
                CURRENT_TIMESTAMP - 2,
                2.3
        UNION
        SELECT  'GBP',
                'EUR',
                CURRENT_TIMESTAMP - 3,
                1.9
        UNION
        SELECT  'GBP',
                'EUR',
                CURRENT_TIMESTAMP - 4,
                1.6
        UNION
        SELECT  'GBP',
                'EUR',
                CURRENT_TIMESTAMP - 5,
                NULL
        UNION
        SELECT  'GBP',
                'EUR',
                CURRENT_TIMESTAMP - 6,
                2.4
        UNION
        SELECT  'GBP',
                'EUR',
                CURRENT_TIMESTAMP - 7,
                1.3
        UNION
        SELECT  'GBP',
                'EUR',
                CURRENT_TIMESTAMP - 8,
                1.1;

SELECT  T.currency_to,
        T.currency_from,
        T.tradedate,
        COALESCE(T.traderate, PriorRate.traderate) AS traderate
FROM    #test AS T
        OUTER APPLY (SELECT TOP (1)
                            T2.traderate
                     FROM   #test AS T2
                     WHERE  T2.currency_from = T.currency_from
                            AND T2.currency_to = T.currency_to
                            AND T2.traderate IS NOT NULL
                            AND T2.tradedate <= T.tradedate
                     ORDER BY T2.tradedate DESC) AS PriorRate;

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Post #1398410

Saurv

Posted Wednesday, December 19, 2012 7:38 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 7:04 AM
Points: 12, Visits: 346

If you can add a identity column to your table then ...you can write code like this...

---------------------------------

create table test(
id int identity(1,1),
currency_to varchar(3),
currency_from varchar(3),
tradedate datetime,
traderate decimal(27,7)
)
go
insert into test(currency_to, currency_from, tradedate, traderate)
select 'GBP', 'EUR', CURRENT_TIMESTAMP, 1.2
UNION
SELECT 'GBP', 'EUR', CURRENT_TIMESTAMP -1, NULL
UNION
SELECT 'GBP', 'EUR', CURRENT_TIMESTAMP -2, 2.3
UNION
SELECT 'GBP', 'EUR', CURRENT_TIMESTAMP -3, 1.9
UNION
SELECT 'GBP', 'EUR', CURRENT_TIMESTAMP -4, 1.6
UNION
SELECT 'GBP', 'EUR', CURRENT_TIMESTAMP -5, NULL
UNION
SELECT 'GBP', 'EUR', CURRENT_TIMESTAMP -6, 2.4
UNION
SELECT 'GBP', 'EUR', CURRENT_TIMESTAMP -7, 1.3
UNION
SELECT 'GBP', 'EUR', CURRENT_TIMESTAMP -8, 1.1

select t1.currency_to, t1.currency_from, t1.tradedate, isnull(t1.traderate ,t2.traderate) as traderate
from test t1 left join test t2 on
t1.id=t2.id+1
order by t1.tradedate desc

Post #1398413

Cadavre

Posted Wednesday, December 19, 2012 7:42 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:16 AM
Points: 2,236, Visits: 6,486

Another data smudge question, seem to come in waves Smile

SELECT currency_to, currency_from, tradedate, ad.traderate
FROM test a
CROSS APPLY (SELECT TOP 1 traderate
FROM test b
WHERE b.tradedate <= a.tradedate
AND b.traderate IS NOT NULL
AND b.currency_from = a.currency_from
AND b.currency_to = a.currency_to
ORDER BY b.tradedate DESC) ad;

Which returns: -

currency_to currency_from tradedate               traderate
----------- ------------- ----------------------- ---------------------------------------
GBP         EUR           2012-12-11 14:35:45.820 1.1000000
GBP         EUR           2012-12-12 14:35:45.820 1.3000000
GBP         EUR           2012-12-13 14:35:45.820 2.4000000
GBP         EUR           2012-12-14 14:35:45.820 2.4000000
GBP         EUR           2012-12-15 14:35:45.820 1.6000000
GBP         EUR           2012-12-16 14:35:45.820 1.9000000
GBP         EUR           2012-12-17 14:35:45.820 2.3000000
GBP         EUR           2012-12-18 14:35:45.820 2.3000000
GBP         EUR           2012-12-19 14:35:45.820 1.2000000

Or: -
SELECT currency_to, currency_from, tradedate, ad.traderate
FROM test a
CROSS APPLY (SELECT TOP 1 CAST(traderate AS FLOAT)
FROM test b
WHERE b.tradedate <= a.tradedate
AND b.traderate IS NOT NULL
AND b.currency_from = a.currency_from
AND b.currency_to = a.currency_to
ORDER BY b.tradedate DESC) ad(traderate);

For: -

currency_to currency_from tradedate               traderate
----------- ------------- ----------------------- ----------------------
GBP         EUR           2012-12-11 14:42:05.897 1.1
GBP         EUR           2012-12-12 14:42:05.897 1.3
GBP         EUR           2012-12-13 14:42:05.897 2.4
GBP         EUR           2012-12-14 14:42:05.897 2.4
GBP         EUR           2012-12-15 14:42:05.897 1.6
GBP         EUR           2012-12-16 14:42:05.897 1.9
GBP         EUR           2012-12-17 14:42:05.897 2.3
GBP         EUR           2012-12-18 14:42:05.897 2.3
GBP         EUR           2012-12-19 14:42:05.897 1.2

If you want to do the presentation layer task of formatting the result-set in the database.

--EDIT--

GSquared beat me to it by a mile Tongue

--Another EDIT--

Striked through the CROSS APPLY as GSquared raises a good point.

Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...
(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock

LinkedIn | Blog coming soon (for sufficiently large values of "soon" Wink

Post #1398416

GSquared

Posted Wednesday, December 19, 2012 7:48 AM

SSCoach

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:55 PM
Points: 15,442, Visits: 9,571

As a note on the variations using Apply, I recommend using Outer Apply, in case you have a currency pair that has never had a valid exchange rate in your data. That way, you'll get a row for that. Otherwise, if you use Cross Apply, the currency pair simply won't generate a row at all.

Same for a currency pair that started out NULL and doesn't have a prior exchange rate recorded. Even if it has later valid rows, you won't get the earlier ones.

So, unless you want to exclude those entirely, use Outer Apply instead of Cross Apply. That will at least give people reviewing the data a chance to notice the issue.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Post #1398420

adb2303

Posted Wednesday, December 19, 2012 8:04 AM

SSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 8:02 AM
Points: 655, Visits: 1,860

perfect. Thank you

Post #1398428

CELKO

Posted Wednesday, December 19, 2012 7:17 PM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782

I have a table with exchange rates between 2 countries. There's 90 days of history for every exchange rate. I've found that there's some NULL's in the trade rate column, which I need to fill.

To track the history of, say, Exchange rates we need to see time as a continuum and model it as (begin_date, end_date) pairs that define when the exchange rate had a particular value. Currency codes are always CHAR(3); did you want garbage data for some reason? Do hate ISO currency codes for some reason? Here is the skeleton.

CREATE TABLE Exchange_History
(source_currency_code CHAR(3) NOT NULL
CHECK (source_currency_code LIKE '[A-Z][A-Z][A-Z]'),
target_currency CHAR(3) NOT NULL
CHECK (target_currency_code LIKE '[A-Z][A-Z][A-Z]'),
CHECK (source_currency_code <> target_currency),
exchange_rate_start_date DATE NOT NULL,
exchange_rate_end_date DATETIME, --null means still current
CHECK (rate_start_date <= rate_end_date),
exchange_rate DECIMAL (10,5) NOT NULL,
PRIMARY KEY (source_currency_code, target_currency_code,
exchange_rate_start_date));

When the end_date is NULL, that state of being is still current. Instead of a temporal continuum, you are modeling slips of paper with missing calendar pages. That is not RDBMS.

You use a simple query for the status on any particular date;

SELECT *
FROM Exchange_History
WHERE @in_cal_date
BETWEEN exchange_rate_start_date
AND COALESCE (exchange_rate_end_date, CURRENT_TIMESTAMP);

There are more tricks in the DDL to prevent gaps, etc. Do you want them or can you Google it? Clifford Simak was wrong; time is not a simple thing at all!

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Post #1398732

GSquared

Posted Thursday, December 20, 2012 7:06 AM

SSCoach

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:55 PM
Points: 15,442, Visits: 9,571

Joe, I've seen your "tricks" that pretend to avoid date gaps.

I've already proved to you that they do not work. They allow gaps, they allow overlaps. I showed you this a LONG time ago.

Why do you keep insisting that they work? It's dishonest.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Post #1398956

« Prev Topic | Next Topic »

Permissions