We
can able to maintain old historical and current data (SCD) using the date
column. Let me show you people how to write a small query to achieve it.
For
example, Assume we have a column called currency which has currency values of
all the countries and your business needs it to keep it uniform, say convert
all currency of different countries to USD, so it will be easy for Business
judgement and analysis.
For
this, let me take an example of Yahoo! Finance. Yahoo! Finance API provides us currency
conversion of all country based on time. I am not here going to tell how to
pull the data from Yahoo! Finance API, But I am using such data which I pulled
to explain how SCD can be achieved.
Below
is a image, which depicts, the data which we got from Yahoo! Finance.
We
wanted this to get converted as shown below:
Where
we wanted to derive Effective_To column, which indicates, from one date to other
date, the currency was so & so for a country against USD.
We
can achieve this using Row_Number(). (Remember, we have built in Function in
SQL Server from 2012 onwards to achieve the same)
Because,
I am working on SQL Server 2008, this may help few people and below is query to
do that.
SELECT From_Currency
,To_Currency
,Effective_From
,Exchange_Rate
,Row_Number() OVER (
PARTITION
BY From_Currency ORDER
BY Effective_Date DESC
) AS RnkNxt
INTO #TEMP1
FROM Yahoo_Exchange_Rate
SELECT From_Currency
,To_Currency
,Effective_From
,Exchange_Rate
,Row_Number() OVER (
PARTITION
BY From_Currency ORDER
BY Effective_Date DESC
) + 1 AS RnkNxt
INTO #TEMP2
FROM Yahoo_Exchange_Rate
SELECT Distinct O.From_Currency
,O.To_Currency
,Cast(Effective_Date
As Datetime) As Effective_From
,Cast(Exchange_Rate
As Decimal(15,8)) As Exchange_Rate
,(
SELECT
DateAdd(dd, - 1, Effective_Date)
FROM
#Temp1 T
WHERE
T.RnkNxt = O.Rnk
AND
T.From_Currency =
O.From_Currency
) AS Effective_To
FROM #TEMP2 O
|
Comments
Post a Comment