Instantly share code, notes, and snippets.

Embed
What would you like to do?
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment