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

Question

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

Answer 1

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.

Answer 2

I'm having a bit of trouble understanding your query, but this is a general solution to what I think you're trying to solve. If you have potentially many children per parent then you might want to limit by the date to start with (for example, include criteria in the LEFT OUTER JOIN and subquery such that it limits to only children within 3 days of @my_date (as an example). At least then an index on the date might get some use, whereas the below code won't be able to use it at all.

SELECT
    P.parent_id,
    C.stuff
FROM
    Parent P
LEFT OUTER JOIN Child C ON
    C.parent_id = P.parent_id
WHERE
    NOT EXISTS
    (
        SELECT
            *
        FROM
            Child C2
        WHERE
            C2.parent_id = P.parent_id AND
            ABS(DATEDIFF(ss, C2.my_date, @my_date)) < ABS(DATEDIFF(ss, C.my_date, @my_date))
    )

Answer 3

I can not try with MS SQL, but something like that should work for you.

It should return the value with the smallest date-difference (same date if possible).

SELECT * FROM (
    SELECT  t1.[TransakcjeGotowkoweKwota],  
            t1.TypyWaluty,
            t2.[kurs_sredni],
            ROW_NUMBER() OVER( PARTITION BY t1.[TransakcjeGotowkoweData] ORDER BY ABS(cast(t1.[TransakcjeGotowkoweData] - t2.[data publikacji] AS FLOAT)) ) rank
    FROM    [BazaZarzadzanie].[dbo].[TransakcjeGotowkowe] t1
    LEFT JOIN [BazaZarzadzanie].[dbo].[KursyWalutNBP] t2
    ON t1.TypyWaluty = t2.[kod waluty]
    WHERE   [TypyWaluty] = 'EUR'
) x
WHERE rank = 1

Answer 4

Something like this should do it:

SELECT  t1.[TransakcjeGotowkoweKwota],  
        t1.TypyWaluty,  
        t1.[TransakcjeGotowkoweData],  
        t2.[kurs_sredni]  
FROM    [BazaZarzadzanie].[dbo].[TransakcjeGotowkowe] t1
LEFT JOIN (
    select ta.TypyWaluty, ta.[TransakcjeGotowkoweData], min(ABS(cast(ta.[TransakcjeGotowkoweData] - tb.[data publikacji] as float))) as ClosestDate
    FROM    [BazaZarzadzanie].[dbo].[TransakcjeGotowkowe] ta
    inner join [BazaZarzadzanie].[dbo].[KursyWalutNBP] tb ON ta.TypyWaluty = tb.[kod waluty] 
    group by ta.TypyWaluty, ta.[TransakcjeGotowkoweData]
) t2c ON t1.TypyWaluty = t2c.TypyWaluty
    AND t1.[TransakcjeGotowkoweData] = t2c.[TransakcjeGotowkoweData]
LEFT JOIN [BazaZarzadzanie].[dbo].[KursyWalutNBP] t2 ON t2c.TypyWaluty = t1.TypyWaluty
    AND t1.[TransakcjeGotowkoweData] = t2.[TransakcjeGotowkoweData]
    AND t2c.ClosestDate = ABS(cast(t1.[TransakcjeGotowkoweData] - t2.[data publikacji] as float))
WHERE   t1.[TypyWaluty] = 'EUR'

Answer 5

Best aproach to this was Mitchel's one. I've created

CREATE FUNCTION KursWaluty
(
  @typWaluty nvarchar(15),
  @dataWaluty DATETIME
)
RETURNS varchar(30)
AS BEGIN
RETURN ( SELECT TOP 1
                kurs_sredni
         FROM   [BazaZarzadzanie].[dbo].[KursyWalutNBP]
         WHERE  [kod waluty] = @typWaluty
                AND [data publikacji] >= @dataWaluty
         ORDER BY [data publikacji]
       )

end

And used this query to get it:

SELECT  t1.[TransakcjeGotowkoweKwota],
    TypyWaluty,
    [TransakcjeGotowkoweData],
    CASE WHEN [kurs_sredni] IS NULL
         THEN BazaZarzadzanie.dbo.KursWaluty(TypyWaluty, [TransakcjeGotowkoweData])
         ELSE [kurs_sredni]
    END AS 'Currency'

FROM    [BazaZarzadzanie].[dbo].[TransakcjeGotowkowe] t1
    LEFT JOIN [BazaZarzadzanie].[dbo].[KlienciPortfeleKonta] t2
    ON t1.[KlienciPortfeleKontaID] = t2.[KlienciPortfeleKontaID]
    LEFT JOIN [BazaZarzadzanie].[dbo].[KursyWalutNBP]
    ON TypyWaluty = [kod waluty]
       AND [data publikacji] = [TransakcjeGotowkoweData]
 WHERE   [TypyWaluty] = 'EUR' -- AND [kurs_sredni] IS NULL

This works and seems to be preety fast (2 seconds). I have used AND [kurs_sredni] IS NULL to verify that the null values got the right values now.

Answer 6

A helper table of dates is useful for this; joining this to KursyWalutNBP you can easily work out the right exchange rate for each date. I'm presenting this as a view to make the final query simpler:

CREATE VIEW CurrencyNearRates (kod_waluty, data, kurs_sredni)
AS 
SELECT currencydates.kod_waluty, currencydates.data, ratenow.kurs_sredni FROM (SELECT currencies.kod_waluty, Helper_Dates.data FROM currencies CROSS JOIN Helper_Dates) currencydates
LEFT OUTER JOIN KursyWalutNBP ratenow
ON 
currencydates.kod_waluty = ratenow.kod_waluty
AND currencydates.data <= ratenow.data_publikacji
AND ratenow.data_publikacji = 
(
  SELECT MIN(futurerates.data_publikacji)
  FROM KursyWalutNBP futurerates
  WHERE ratenow.kod_waluty = futurerates.kod_waluty
  AND currencydates.data <= futurerates.data_publikacji
) 

That gives you data like this:

SELECT * FROM CurrencyNearRates ORDER BY kod_waluty, data;
|kod_waluty     |data        |kurs_sredni|
|----------------------------------------|
|EUR            |2009-01-04  |4.1137     |
|EUR            |2009-01-05  |4.1137     |
|EUR            |2009-01-06  |4.4157     |
|EUR            |2009-01-07  |4.4157     |
 ----------------------------------------

Then you just do a simple join between the transactions in TransakcjeGotowkowe and the rates in CurrencyNearRates:

SELECT
 t1.[TransakcjeGotowkoweKwota],  
 t1.[TypyWaluty],  
 t1.[TransakcjeGotowkoweData],  
 CurrencyNearRates.[kurs_sredni]
FROM
dbo.[TransakcjeGotowkowe] t1
LEFT OUTER JOIN CurrencyNearRates
 ON t1.[TypyWaluty] = CurrencyNearRates.[kod_waluty]
 AND t1.[TransakcjeGotowkoweData] = CurrencyNearRates.[data]
WHERE t1.[TypyWaluty] = 'EUR'  
ORDER BY t1.[TransakcjeGotowkoweData]

And that gives you output like this:

|TransakcjeGotowkoweKwota   |TypyWaluty   |TransakcjeGotowkoweData   |kurs_sredni   |
|-----------------------------------------------------------------------------------|
|-18.36                     |EUR          |2009-07-01                |4.4157        |
|-14153.04                  |EUR          |2009-01-05                |4.1137        |
|4.61                       |EUR          |2007-09-30                |4.5678        |
|55.5                       |EUR          |2007-09-30                |4.5678        |
 -----------------------------------------------------------------------------------