CREATE FUNCTION DBO.Exchange(@UserIdNumber INT, @DateStamp DATETIME, @BillDateStamp DATETIME, @CurrencyNumber INT)
RETURNS FLOAT AS  
/*
   NB20070627 Default Exchange Rate = 100 Same shit just better
   NB20070413 Default Exchange Rate = 100
   NB20070301 UserIdNumber By UserType
   NB20070226 Created

   Parameters
*/
BEGIN 
  DECLARE @ExchangeRate FLOAT
  IF @UserIdNumber IN (SELECT UserIdNumber FROM Personel WITH (NOLOCK) WHERE UserTypeNumber IN (3,4))
    SELECT TOP 1 @ExchangeRate= ISNULL(Product, 100) FROM CurrencyRate WITH (NOLOCK)
      INNER JOIN CountryCurrencyXTab WITH (NOLOCK) ON CurrencyRate.FK_Currency=CountryCurrencyXTab.FK_Currency AND DateStamp<= CONVERT(DATETIME, @DateStamp)
      INNER JOIN Country WITH (NOLOCK) ON PK_Country = CountryCurrencyXTab.FK_Currency AND FK_OldCountry= @CurrencyNumber
    ORDER BY FK_Log DESC
  ELSE
    SELECT TOP 1 @ExchangeRate= ISNULL(Product, 100) FROM CurrencyRate WITH (NOLOCK)
      INNER JOIN CountryCurrencyXTab WITH (NOLOCK) ON CurrencyRate.FK_Currency=CountryCurrencyXTab.FK_Currency AND DateStamp<= @BillDateStamp
      INNER JOIN Country WITH (NOLOCK) ON PK_Country = CountryCurrencyXTab.FK_Currency AND FK_OldCountry= @CurrencyNumber
    ORDER BY FK_Log DESC
  IF ISNULL(@ExchangeRate, 100) = 100 
    SELECT TOP 1 @ExchangeRate= ISNULL(Product, 100)  FROM CurrencyRate WITH (NOLOCK)
      INNER JOIN CountryCurrencyXTab WITH (NOLOCK) ON CurrencyRate.FK_Currency=CountryCurrencyXTab.FK_Currency 
      INNER JOIN Country WITH (NOLOCK) ON PK_Country = CountryCurrencyXTab.FK_Currency AND FK_OldCountry= @CurrencyNumber
    ORDER BY FK_Log DESC
  IF  ISNULL(@ExchangeRate, 0) = 0 
    SET @ExchangeRate = 100
  RETURN 100/@ExchangeRate
END

