iVend Database Database : RPT_ExchangeRate Stored Procedure

Creation Date

8/11/2019 8:38 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@FromDate

In

VarChar

8

@ToDate

In

VarChar

8

@IsZero

In

Boolean

1

@RETURN_VALUE

Return Value

Integer

4

CREATE PROCEDURE [dbo].[RPT_ExchangeRate](@FromDate Varchar(8), @ToDate Varchar(8), @IsZero bit)

AS

BEGIN

IF OBJECT_ID('tempdb..#DateTable') IS NOT NULL

  DROP TABLE #DateTable

---    EXEC RPT_ExchangeRate '20151101','20151231', 1

  ----Declare @FromDate Varchar(8)='20190101', @ToDate Varchar(8)='20190228', @IsZero bit=0

DECLARE @DayCount INT

SET @DayCount = DATEDIFF(D, @FromDate,@ToDate)+1

DECLARE @Temp Table (EffectiveDate DateTime, Rn INT, DR INT, Currency NVARCHAR(400), BaseCurrency NVARCHAR(400), BuyRate Decimal(20,4),

SellRate DECIMAL(20,4), Subsidiary NVARCHAR(100), SubsidiaryDesc NVARCHAR(400), CultureInfo NVARCHAR(100))

CREATE TABLE #DateTable (RN int Identity(1,1), [Date] Datetime)

INSERT INTO #DateTable([Date])

SELECT DATEADD(DAY, RN-1, @FromDate) AS [Date] FROM (

SELECT TOP (DATEDIFF(DAY, @FromDate, @ToDate) + 1)

   RN = ROW_NUMBER() OVER (ORDER BY [object_id])

FROM sys.all_objects

 ) AS N

;WITH CTE AS

(

Select P.Id AS Currency, BaseC.Id As BaseCurrency, EffectiveDate, BuyRate, SellRate, P.CultureInfo,

ISNULL(Sub.Id,'') As Subsidiary, ISNULL(Sub.Description,'') As SubsidiaryDesc

From

PmtExchangeRate E

LEFT JOIN PmtCurrency P ON E.CurrencyKey = P.CurrencyKey

LEFT JOIN PmtCurrency BaseC ON E.BaseCurrencyKey = BaseC.CurrencyKey

LEFT OUTER JOIN RtlSubsidiary Sub ON P.CurrencyKey = Sub.LocalCurrencyKey

Where Convert(Varchar,EffectiveDate,112) Between Case When @FromDate <>'''' Then Convert(datetime,@FromDate,112) Else Convert(Varchar,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0),112) END

AND Case When @ToDate <>'''' Then Convert(datetime,@ToDate,112) Else Convert(Varchar,DATEADD(yy,0,Getdate()),112) END

-- AND (BuyRate <=0 OR SellRate<=0)

)

INSERT INTO @Temp (EffectiveDate, Rn, DR, Currency, BaseCurrency, BuyRate, SellRate, Subsidiary, SubsidiaryDesc, CultureInfo)

Select [Date] AS EffectiveDate, Rn, NTILE(@DayCount) Over(Partition By Rn Order By RN) AS DR, S.Currency AS Currency,

S.BaseCurrency AS BaseCurrency, ISNULL(BuyRate,0) AS BuyRate, ISNULL(SellRate,0) AS SellRate, S.Subsidiary, S.SubsidiaryDesc, CultureInfo

From

(

Select R.Date, Rn, S1.Currency, S1.BaseCurrency, S1.Subsidiary, S1.SubsidiaryDesc

From #DateTable R LEFT OUTER JOIN

  (

  Select Distinct Currency, BaseCurrency, Subsidiary, SubsidiaryDesc FROM CTE

  ) AS S1 ON 1=1

 )

AS T LEFT OUTER JOIN CTE S ON T.Date=S.EffectiveDate AND T.Currency = S.Currency AND T.Subsidiary = S.Subsidiary

ORDER BY NTILE(@DayCount) Over(Partition By Rn Order By RN), RN

IF EXISTS (Select 1 From @Temp Where (Currency IS NULL OR BaseCurrency IS NULL OR Subsidiary IS NULL OR SubsidiaryDesc IS NULL))

BEGIN

UPDATE T

  SET T.Currency = (SELECT Top 1 T1.Currency FROM @Temp T1 WHERE T1.Currency IS NOT NULL

                  AND T.Rn < T1.Rn AND T.Dr= T1.Dr)

 ,T.BaseCurrency = (SELECT Top 1 T1.BaseCurrency FROM @Temp T1 WHERE T1.BaseCurrency IS NOT NULL

                  AND T.Rn < T1.Rn AND T.Dr = T1.Dr)

 ,T.CultureInfo = (SELECT Top 1 T1.CultureInfo FROM @Temp T1 WHERE T1.CultureInfo IS NOT NULL

                  AND T.Rn < T1.Rn AND T.Dr= T1.Dr)

 ,T.Subsidiary =(SELECT Top 1 T1.Subsidiary FROM @Temp T1 WHERE T1.Subsidiary IS NOT NULL

                  AND T.Rn < T1.Rn AND T.Dr = T1.Dr)

 , T.SubsidiaryDesc = (SELECT Top 1 T1.SubsidiaryDesc FROM @Temp T1 WHERE T1.SubsidiaryDesc IS NOT NULL

                  AND T.Rn < T1.Rn AND T.Dr = T1.Dr)

  From @Temp T

WHERE (T.Currency IS NULL OR T.BaseCurrency IS NULL OR T.CultureInfo IS NULL OR T.Subsidiary IS NULL OR T.SubsidiaryDesc IS NULL)

END

IF EXISTS (Select 1 From @Temp Where (Currency IS NULL OR BaseCurrency IS NULL OR Subsidiary IS NULL OR SubsidiaryDesc IS NULL))

BEGIN

UPDATE T

  SET T.Currency = (SELECT Top 1 T1.Currency FROM @Temp T1 WHERE T1.Currency IS NOT NULL

                  AND T1.Rn < T.Rn AND T1.Dr = T.Dr)

 ,T.BaseCurrency = (SELECT Top 1 T1.BaseCurrency FROM @Temp T1 WHERE T1.BaseCurrency IS NOT NULL

                  AND T1.Rn < T.Rn AND T1.Dr = T.Dr)

 ,T.CultureInfo = (SELECT Top 1 T1.CultureInfo FROM @Temp T1 WHERE T1.CultureInfo IS NOT NULL

                  AND T1.Rn < T.Rn AND T1.Dr = T.Dr)

 ,T.Subsidiary = (SELECT Top 1 T1.Subsidiary FROM @Temp T1 WHERE T1.Subsidiary IS NOT NULL

                  AND T1.Rn < T.Rn AND T1.Dr = T.Dr)

 ,T.SubsidiaryDesc = (SELECT Top 1 T1.SubsidiaryDesc FROM @Temp T1 WHERE T1.SubsidiaryDesc IS NOT NULL

                  AND T1.Rn < T.Rn AND T1.Dr = T.Dr)

From @Temp T

WHERE (T.Currency IS NULL OR T.BaseCurrency IS NULL OR T.CultureInfo IS NULL OR T.Subsidiary IS NULL OR T.SubsidiaryDesc IS NULL)

END

Select * From @Temp

WHERE SellRate <= CASE WHEN @IsZero =1 THEN 0 ELSE SellRate END

ORDER BY DR, RN

END