Skip to content

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
You can’t perform that action at this time.
You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.