CODEWITHSUNDEEP
sqlsql-serverjoin
Adam Levitt
Adam Levitt

Reputation: 10476

SQL Conditional Date COALESCE

I have a case where I want to return the exchange rate of a currency, but if the date of the job is before 1/1/1999, then I want to just return the rate of 1/1/1999 since the exchange_rate table has no information before 1/1/1999. So in this case, if the returned date from the COALESCE is 1/1/1985, the query should just instead join against 1/1/1999. 

SELECT j.offer_date, j.accepted_date, j.reported_date, j.start_date, salary, salary_format_id, j.currency_id 
FROM job j
LEFT JOIN exchange_rate c1 ON CAST(COALESCE(j.offer_date, j.accepted_date, j.reported_date, j.start_date) AS date) = c1.date AND c1.currency_id = j.currency_id
LEFT JOIN exchange_rate c2 ON CAST(COALESCE(j.offer_date, j.accepted_date, j.reported_date, j.start_date) AS date) = c2.date AND c2.currency_id = 1
WHERE j.job_id = 4793

Upvotes: 1

Views: 666

Answers (4)

jpw
jpw

Reputation: 44891

Maybe something like this is what you want:

LEFT JOIN exchange_rate c2 ON (
CASE 
 WHEN (CAST(COALESCE(j.offer_date, j.accepted_date, j.reported_date, j.start_date) AS date)) < '1999-01-01' THEN '1999-01-01'
 ELSE CAST(COALESCE(j.offer_date, j.accepted_date, j.reported_date, j.start_date) AS date) END) = c2.date AND c2.currency_id = 1

Not sure if you actually need the casts with coalesce. (And removing them seem to work fine, but maybe there is some case when you need to cast.)

Upvotes: 0

Victor
Victor

Reputation: 11

I personally would use case statement in select part

SELECT 
   CASE 
      WHEN date < '1999-01-01' THEN (SELECT TOP 1 [COLUMN_YOU_WANT] FROM exchange_rate AS c3 WHERE date = '1999-01-01' AND c3.currency_id = j.currency_id)
      ELSE c1.[COLUMN_YOU_WANT]
   END, j.offer_date, j.accepted_date, ...

Basically it says if date < '1999-01-01' select the rate for '1999-01-01', otherwise just use what you get from JOIN.

Upvotes: 1

Andrew
Andrew

Reputation: 8713

Your question isn't super clear, but I think this is all you need:

    LEFT JOIN exchange_rate c1
    on
    (case when 
    CAST(COALESCE(j.offer_date, j.accepted_date, j.reported_date, j.start_date) AS date) < '1999-01-01'
    then '1999-01-01'
    else
    CAST(COALESCE(j.offer_date, j.accepted_date, j.reported_date, j.start_date) AS date)
   end ) = c1.date

Upvotes: 0

Joel Coehoorn
Joel Coehoorn

Reputation: 415931

Do this:

CASE 
  WHEN COALESCE(j.offer_date, j.accepted_date, j.reported_date, j.start_date) < '1999-01-01' THEN '1999-01-01'
  ELSE COALESCE(j.offer_date, j.accepted_date, j.reported_date, j.start_date)
END

And, yes, I know that's repetitive and a lot to type, but it will get the job done.

Upvotes: 2

Related Questions