Posts Tagged ‘ mssql

Get the average value from the nearest date in MS SQL

Needed to get the average exchange rate for the nearest invoice to a given date, and I found a quick and easy way to accomplish this in SQL Server:

SELECT TOP 1
   AVG(i.exchrate) / 100 AS exchrate
   , MIN(DATEDIFF(DAY, '" . $date . "', i.invoicedate)) AS accuracy
FROM InvoiceTable i
WHERE
   i.currencycode = '" . $currency_code . "'
   AND DATEDIFF(DAY, '" . $date . "', i.invoicedate) > 0
ORDER BY MIN(DATEDIFF(DAY, '" . $date . "', i.invoicedate))