Sql value which isn’t for the needed date but first available

sql

I've got this SQL QUERY in MS SQL 2005/2008 database which gets me Money Amount, Money Currency, Money Time and Currency Converter. I left join it with Table that has gathered information from polish national bank about currency converter per each day.

Here's the query:

SELECT  t1.[TransakcjeGotowkoweKwota],  
        t1.TypyWaluty,  
        t1.[TransakcjeGotowkoweData],  
        t2.[kurs_sredni]  
FROM    [BazaZarzadzanie].[dbo].[TransakcjeGotowkowe] t1

LEFT JOIN [BazaZarzadzanie].[dbo].[KursyWalutNBP] t2
ON t1.TypyWaluty = t2.[kod waluty]  AND t2.[data publikacji] = t1.[TransakcjeGotowkoweData]  
WHERE   [TypyWaluty] = 'EUR'

Here's the output:

TransakcjeGotowkoweKwota TypyWaluty TransakcjeGotowkoweData kurs_sredni
-14153.04000000         EUR         2009-01-05 00:00:00.000    4,1137    
-18.36000000            EUR         2009-07-01 00:00:00.000    4,4157    
4.61000000              EUR         2007-09-30 00:00:00.000    NULL
55.50000000             EUR         2007-09-30 00:00:00.000    NULL

The problem is with NULL values for Kurs_sredni. It happens when [kurs_sredni] cannot be found in [KursyWalutNBP] for that particular day. What i would like to achieve is when it happens it should get the nearest possible date and get value for that day.

For example:
If value is NULL for [Kurs Sredni] for date 2007-09-30 it should get value from 2007-10-01 (if it has one of course).

How should i aproach this?

With regards,

MadBoy

Best Answer

I would personally move to a User Defined Function for this type of thing, rather than a join. This way you can have full control over the evaluation process. You could do it in a manner like the following, based on your notes.

SELECT TOP 1 kurs_sredni
FROM YourTable
WHERE (Your Comparison here)
ORDER BY Date

This way, you can do a >= comparison on date, and if it doesn't exist, you will get the next latest date value.

Related Solutions

Sql – How to return only the Date from a SQL Server DateTime datatype

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @your_date))

for example

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

gives me

2008-09-22 00:00:00.000

Pros:

No varchar<->datetime conversions required
No need to think about locale

Sql – Fetch the row which has the Max value for a column

I see many people use subqueries or else window functions to do this, but I often do this kind of query without subqueries in the following way. It uses plain, standard SQL so it should work in any brand of RDBMS.

SELECT t1.*
FROM mytable t1
  LEFT OUTER JOIN mytable t2
    ON (t1.UserId = t2.UserId AND t1."Date" < t2."Date")
WHERE t2.UserId IS NULL;

In other words: fetch the row from t1 where no other row exists with the same UserId and a greater Date.

(I put the identifier "Date" in delimiters because it's an SQL reserved word.)

In case if t1."Date" = t2."Date", doubling appears. Usually tables has auto_inc(seq) key, e.g. id. To avoid doubling can be used follows:

SELECT t1.*
FROM mytable t1
  LEFT OUTER JOIN mytable t2
    ON t1.UserId = t2.UserId AND ((t1."Date" < t2."Date") 
         OR (t1."Date" = t2."Date" AND t1.id < t2.id))
WHERE t2.UserId IS NULL;

Re comment from @Farhan:

Here's a more detailed explanation:

An outer join attempts to join t1 with t2. By default, all results of t1 are returned, and if there is a match in t2, it is also returned. If there is no match in t2 for a given row of t1, then the query still returns the row of t1, and uses NULL as a placeholder for all of t2's columns. That's just how outer joins work in general.

The trick in this query is to design the join's matching condition such that t2 must match the same userid, and a greater date. The idea being if a row exists in t2 that has a greater date, then the row in t1 it's compared against can't be the greatest date for that userid. But if there is no match -- i.e. if no row exists in t2 with a greater date than the row in t1 -- we know that the row in t1 was the row with the greatest date for the given userid.

In those cases (when there's no match), the columns of t2 will be NULL -- even the columns specified in the join condition. So that's why we use WHERE t2.UserId IS NULL, because we're searching for the cases where no row was found with a greater date for the given userid.

Best Answer

Related Solutions

Sql – How to return only the Date from a SQL Server DateTime datatype

Sql – Fetch the row which has the Max value for a column

Related Topic