CODEWITHSUNDEEP
sqldatewhere-clause
GigaFluxx
GigaFluxx

Reputation: 31

If result = 0/null then change where?

I have the following query:

select
C.[Exchange Rate Amount]

from [Canada].[vCurrencyRate] as C

Where
C.[Currency Code] = 'CAD'
AND cast(C.Date as date) = cast( getdate() as date)

What I would like it to do is if the result is 0, then roll back the date one day

Where
C.[Currency Code] = 'CAD'
AND IF C.[Exchange Rate Amount] = 0 then cast(C.Date as date) = datediff( day, -1, cast( getdate() as date)) else cast(C.Date as date) = cast( getdate() as date)

However I can't figure out how to make it work

Upvotes: 1

Views: 31

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

If you want one row, use order by and limit to one row:

select top (1) C.[Exchange Rate Amount]
from [Canada].[vCurrencyRate] as C
Where C.[Currency Code] = 'CAD' AND cast(C.Date as date) <= cast( getdate() as date)
order by c.date DESC;

Because of the square braces and getdate(), I'm assuming this is SQL Server. Other databases would use limit or fetch first 1 row only.

Upvotes: 1

Related Questions