cv_Rates.cs

using System;
using PX.Data;

namespace PX.Objects.CM
{    
	[Serializable]
	public class cv_Rates : IBqlTable
	{
		#region TenantID
		[PXDBInt(IsKey = true)]
		[PXUIField(DisplayName = "Tenant ID")]
		public int? TenantID { get; set; }
		public class tenantID : IBqlField { }
		#endregion
		#region FromCuryID
		[PXDBString(5, IsKey = true, IsUnicode = true, InputMask = "")]
		[PXUIField(DisplayName = "From Cury ID")]
		public string FromCuryID { get; set; }
		public class fromCuryID : IBqlField { }
		#endregion
		#region ToCuryID
		[PXDBString(5, IsKey = true, IsUnicode = true, InputMask = "")]
		[PXUIField(DisplayName = "To Cury ID")]
		public string ToCuryID { get; set; }
		public class toCuryID : IBqlField { }
		#endregion
		#region CuryEffDate
		[PXDBDate(IsKey = true)]
		[PXUIField(DisplayName = "Cury Eff Date")]
		public DateTime? CuryEffDate { get; set; }
		public class curyEffDate : IBqlField { }
		#endregion
		#region RateType
		[PXDBString()]
		[PXUIField(DisplayName = "Rate Type")]
		public int? RateType { get; set; }
		public class rateType : IBqlField { }
		#endregion
		#region RateID
		[PXDBInt()]
		[PXUIField(DisplayName = "Rate ID")]
		public int? RateID { get; set; }
		public class rateID : IBqlField { }
		#endregion
	}
}

cv_Rates.sql

IF OBJECT_ID ('dbo.[cv_Rates]', 'V') IS NOT NULL 
    DROP VIEW [dbo].[cv_Rates]
GO
CREATE VIEW [dbo].[cv_Rates] AS
select FromCury.CompanyID as CompanyID, FromCury.CuryID as FromCuryID, ToCury.CuryID as ToCuryID, Date as CuryEffDate, 'SPOT' as RateType,
    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 and r.CuryRateType = 'SPOT'
		  order by CuryEffDate DESC), 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