crytsl link tables based on nearest date 1

[pmgatwork]

Hi all
I have a table which contains the fuel rates and dates when entered. e.g. 01/01/15 = 120 and 01/07/15 = 114. This denotes that the rate at 01/01/15 was 120p per litre and on 01/07/15 was 114p per litre (uk dates)
I have another table which has transactions processed on most days.
What I am trying to do is report on the fuel rate that was current on the transaction date.
Is this possible

thanks
Peter

 

[tomcat24]

Yes, You can. He is how to do it.

Parms
Start Transaction Date === make it a Date field
End Transaction Date === make it a Date field

Table linkage will be Transaction Date Table (Date) Left outer join Fuel Rates and Dates (Date)

SQL

SELECT
t.Transaction Date Table fields you needs in your report
,f.Fuel Rates and Dates fields you needs in your report
FROM TRANS t
LEFT OUTER JOIN FUEL f
WHERE Start Transaction Date => (What ever this Field is named Transaction Date) and <= (What ever this Field is named End Transaction Date