Can't join on date field due to nulls 1

[hamking11]

I have to tables in the following layout:

Order Table:
quantity
currency
date

Currency Table:
rate
date

Is there a way to get the the following is a one select statement w/out using a temp table:

Get the 'quantity', 'rate', 'quantity X rate' based on the date in the order table if the currency = 1. If the date in the order table doesn't exist in the currency table get the rate of previous existing date.

Thanks.

 

[ptheriault]

what are you joining these tables on?

SELECT quantity
      ,rate
      ,quantity * rate as value
FROM Order JOIN Currency
      ON ???

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[hamking11]

at first i joined on the dates, until i noticed that not all date existed for the currency table.

basic idea is i need to join on the date, but if date doesn't exist for currency table get the rate of previous existing date.

 

[ptheriault]

This might work. I didn't test it.

SELECT quantity
      ,rate
      ,quantity * rate as value
FROM Order o JOIN Currency c
      ON o.DATE = CASE WHEN c.DATE IS NULL 
                       THEN (SELECT MAX(DATE)
                             FROM Currency
                              WHERE DATE IS NOT NULL)
WHERE currency = 1

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

My syntax isn't correct. It should more like this.

SELECT quantity
      ,rate
      ,quantity * rate as value
FROM Order o JOIN Currency c
      ON o.DATE = (CASE WHEN c.DATE IS NULL
                       THEN (SELECT MAX(DATE)
                             FROM Currency
                              WHERE DATE IS NOT NULL)
                        ELSE c.DATE
                        END)
WHERE currency = 1

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[hamking11]

That'll always return the same rate when a date is null. But I need to return the previous rate.

For example on the Currency table:

1/1/06 35.11
1/2/06 36.48
1/3/06 NULL
1/4/06 34.35
1/5/06 NULL

If order table has 1/3/06, I need to return 36.48 and if 1/5/06, 34.35 will be returned.

 

[ESquared]

Too bad you can't use the First() or Last() aggregate function that Access has. Strange how the low-end product can do things the high-end product can't.

A correlated subquery is easy:

SELECT
   quantity,
   rate = (SELECT TOP 1 rate FROM Currency WHERE date <= c.date ORDER BY date DESC),
   value = quantity * (SELECT TOP 1 rate FROM Currency WHERE date <= c.date ORDER BY date DESC)
FROM Order o
WHERE currency = 1

Or you could try derived tables:

SELECT
   O.Date,
   R.Rate,
   O.Quantity * R.Rate
FROM
   Order O
   INNER JOIN (
      SELECT
         X.Date,
         MaxDate = Max(T.Date)
      FROM
         Rate T
         INNER JOIN (
            SELECT DISTINCT Date FROM Order WHERE Currency = 1
         ) X ON T.date <= O.date
      GROUP BY
         X.Date
   ) J ON O.Date = J.Date
   INNER JOIN Rate R ON J.MaxDate = R.Date
WHERE
   O.Currency = 1

Make sure you put any conditions on both the inner derived tables and the final outer query (like Currency = 1 is) to limit the derived table's rows, for best performance.

Query cost will scale with the number of rows in the Currency table. If you know that there is a maximum number of skipped days in the Currency table, then a condition on the deepest join with that in mind could really help. If it was, say, 20 days max that could be empty, then join:
ON T.date BETWEEN O.date AND O.date - 21

I have no idea which query will be faster. For small resultsets the correlated subquery will probably be faster. For resultsets that have a large portion of the rows from the orders table, the derived table method could win, depending on your data.

 

[hamking11]

Thanks guys. Got it working over the weekend with the following query:

select
orderqty,
orderdt,
exrate,
isnull(exrate, (select exrate from exrate as e1 where exdate = (select max(e1.exdate) from exrate as e1 where e1.exdate < orderdt)))
from orders o
left join exrate e
on o.orderdt = e.exdate
order by orderdt

I added 1000 records in each table and it took 20+ seconds to run. Everything worked fine once I added a nonclustered index on exrate & exdate in the rate table.

ESquared, I'll try ur queries too, to see if they're any faster. Thanks

 

[ESquared]

I got the aliases wrong in my correlated subquery, but you should be able to fix that.