Case statement

[adnancanada]

I have rate table that contains effective and expiry date and rates for example



effective_date expiry_date Rate
4/28/2024 8/31/2024 50.39
9/1/2024 12/28/2024 70.56

I have anther table where I need to calculate revenue based on rates. It has also has PO_date

I have to incorporate this rate if it falls between effective and expiry date pickup above rates if it out this range pickup latest rate , in this it should pickup 70.56 from 1/1/2024 to 4/27/2024

I need to build SQL query

case
when PO_date between effective_date and expiry_date then rate *wg/100
End

how can I setup second condition if it is out of range then it should pickup last latest rate.

Kindly help me out.

[Minty]

Simply add an Else :

CASE WHEN PO_date between effective_date and expiry_date then rate *wg/100

ELSE (SELECT TOP 1 RATE FROM RateTable Order By ExpiryDate Desc) * wg/100

End as YourCalc

[pbaldy]

I'd consider a non-equi join. Off the top of my head:

SELECT M.Field1, M.Field2, R.Rate
FROM MainTable M
  LEFT JOIN RateTable R ON M.PO_date >= R.effective_date and M.PO_date <= R.expiry_date

[CJ_London]

or use a cartesian query -adapting Pauls sql

SELECT M.Field1, M.Field2, R.Rate
FROM MainTable M, RateTable R
WHERE  M.PO_date >= R.effective_date and M.PO_date <= R.expiry_date

and if you need the current rate where an expirydate is unknown (and therefore null)

SELECT M.Field1, M.Field2, R.Rate
FROM MainTable M, RateTable R
WHERE  M.PO_date >= R.effective_date and M.PO_date <= nz(R.expiry_date, date)

this assumes a PO date can't be later than today. If it can, substitute date for some future date such as #2100-01-01# or ensure the expiry date field has a default value of a future date

[NoellaG]

one issue: SQL server probably won't like the cartesian query, just compare the query plans. You can use the sql from reply #3 and create a view with it. Then link it to Access as a table.