RatesView.sql

IF OBJECT_ID ('dbo.[px_Rates]', 'V') IS NOT NULL 
    DROP VIEW [dbo].[px_Rates]
GO

CREATE VIEW [dbo].[px_Rates] AS
select FromCury.CompanyID as CompanyID, FromCury.CuryID as FromCuryID, ToCury.CuryID as ToCuryID, Date as CuryEffDate, 
    Coalesce((Select top 1 CuryRateID
		  from CurrencyRate r
		  where r.CuryEffDate <= Date and r.CompanyID = FromCury.CompanyID and r.FromCuryID = FromCury.CuryID and r.ToCuryID = ToCury.CuryID
		  order by CuryEffDate), 0) as RateID
from (SELECT  DATEADD(DAY, nbr - 1, '20110901') as Date
	   FROM ( SELECT    ROW_NUMBER() OVER ( ORDER BY c.object_id ) AS Nbr
		    FROM      sys.columns c
		  ) nbrs
    WHERE   nbr - 1 <= DATEDIFF(DAY, '20160101', '20250101')) Dates
Cross Join Currency FromCury
Left Join Currency ToCury on FromCury.CompanyID = ToCury.CompanyID and FromCury.CuryID <> ToCury.CuryID
GO