SQL SELECT Statement help (calculations and date)


I am trying to find out how to write my SELECT statement to calculate milage.

I have a Table named Price. It has 2 column.. Primary key are the month of the year and then the 2nd column is the price amount per miles (ex: 1.5)

So exemple:
January is 1.5$/miles
February is 2$/mile

Then i have a 2nd table name Route. This table has a Date(dd/mm/yyyy) column and a column name TotalMileage

ex: 30/01/2018 and the milage is 55

I am trying to find out how to do a select using that date, taking the number of the month (01) and converting that to January to then after take the month (january) matching it to the Price table to then do a calculation of 55 * 1.5$

I hope this is clear. I am new to SQL and english isn't my 1st language.


SELECT R.*, R.TotalMileage * P.Price AS MilagePrice
FROM dbo.Route R
LEFT OUTER JOIN Price P ON P.Month = SUBSTRING('January  February March    April    May      June     July     August   SeptemberOctober  November December ', 
    (DATEPART(MONTH, R.Date) - 1) * 9 + 1, 9)