February 13, 2012 at 12:25 pm
I am currently trying to complete the final part of a query that loads scd type 2 data into a dimension.
Based on the data provided below I would like to produce an output that can be inserted into a dimension in addition to
expiring old records and tracking history etc. The data is such that I have the most current records where attributes have changed. The changed values are found in Lookup columns along with and dateOfchange i.e. the date the change took place. This dateOfchange should obviously become the validTo date of the most current record.
The sample data is as follows:
CREATE TABLE #tstDimPortfolio
(
[ID][INT] IDENTITY (1,1) NOT NULL,
[UPI] [varchar](20) NOT NULL,
[MF_CODE] [varchar](10) NULL,
[BH_Code] [varchar](10) NULL,
[CR_Code] [varchar](10) NULL,
[ValidFrom][varchar](10) NOT NULL,
[ValidTo][varchar](10) NULL,
[IsCurrent] [CHAR] (1) NULL,
[DateofChange] [varchar](10) NULL,
[LookupMF_CODE] [varchar](10) NULL,
[LookupBH_Code] [varchar](10) NULL,
[LookupCR_Code] [varchar](10) NULL,
)
INSERT INTO #tstDimPortfolio
SELECT 'B06531','B06531','','B06531','20111230',NULL,'Y','20120101','','B06531', ''UNION ALL
SELECT 'BLI003','','BLI003','BBL_WORLD','20111230',NULL,'Y','20120102','BLI004','', ''UNION ALL
SELECT 'BLI003','','BLI003','BBL_WORLD','20111230',NULL,'Y','20120103','BLI005','', ''UNION ALL
SELECT 'BLI027','BLI027','L147','BBL_GBN','20111230',NULL,'Y','20120104','','L146', ''
With this datathe new ouput should be
UPIMF_CODE BH_Code CR_Code ValidFrom ValidTo IsCurrent
_______________________________________________________________________
B06531B06531B06531 20111230 20120101N
B06531B06531B06531B06531 20120101NULL Y
BLI003BLI003BBL_WORLD 2011123020120102 N
BLI003BLI004BLI003BBL_WORLD 2012010220120103 N
BLI003BLI005BLI003BBL_WORLD 20120103NULL Y
BLI027BLI027L147BBL_GBN 2011123020120104 N
BLI027BLI027L146BBL_GBN 20120104NULL Y
any ideas?
February 14, 2012 at 4:45 am
This was removed by the editor as SPAM
February 14, 2012 at 5:59 am
HI this is almost what I want need. However if a change occurs on two different attributes for one upi how can i bring those chnages through to the subsequent rows. For example I have amended to illustrate. For UPI BLI003 there was a change of the MF_Code and BH_Code. Therefore the most current output should display both these changes.
the data is amended as follows
CREATE TABLE #tstDimPortfolio
(
[ID][INT] IDENTITY (1,1) NOT NULL,
[UPI] [varchar](20) NOT NULL,
[MF_CODE] [varchar](10) NULL,
[BH_Code] [varchar](10) NULL,
[CR_Code] [varchar](10) NULL,
[ValidFrom][varchar](10) NOT NULL,
[ValidTo][varchar](10) NULL,
[IsCurrent] [CHAR] (1) NULL,
[DateofChange] [varchar](10) NULL,
[LookupMF_CODE] [varchar](10) NULL,
[LookupBH_Code] [varchar](10) NULL,
[LookupCR_Code] [varchar](10) NULL,
)
INSERT INTO #tstDimPortfolio
SELECT 'B06531','B06531','','B06531','20111230',NULL,'Y','20120101','','B06531', ''UNION ALL
SELECT 'BLI003','','BLI003','BBL_WORLD','20111230',NULL,'Y','20120102','BLI004','', ''UNION ALL
SELECT 'BLI003','','BLI003','BBL_WORLD','20111230',NULL,'Y','20120103','','BLI005', ''UNION ALL
SELECT 'BLI027','BLI027','L147','BBL_GBN','20111230',NULL,'Y','20120104','','L146', ''
SELECT UPI,
CASE WHEN LookupMF_CODE <> '' THEN LookupMF_CODE ELSE MF_CODE END MF_CODE,
CASE WHEN LookupBH_Code <> '' THEN LookupBH_Code ELSE BH_Code END BH_Code,
CASE WHEN LookupCR_Code <> '' THEN LookupCR_Code ELSE CR_Code END CR_Code,
DateofChange AS ValidFrom, ValidTo, IsCurrent, DateofChange
INTO #Dimension_Table
FROM #tstDimPortfolio
;WITH AddedDim AS
(select UPI, MF_CODE, BH_Code, CR_Code, ValidFrom, ValidTo, IsCurrent, DateofChange, ROW_NUMBER() OVER (PARTITION BY UPI ORDER BY UPI, ValidFrom) Sequence
from #Dimension_Table
WHERE IsCurrent = 'Y')
UPDATE nr
set ValidTo = ur.ValidFrom,
IsCurrent = 'N'
--select *
FROM AddedDim nr
JOIN AddedDim ur ON ur.UPI = nr.UPI
AND ur.Sequence = nr.Sequence +1
select UPI, MF_CODE, BH_Code, CR_Code, ValidFrom, ValidTo, IsCurrent
from #Dimension_Table
order by UPI, ValidFrom
Output for BLI003 should be
UPI MF_CODE BH_CodeCR_Code ValidFrom ValidToIsCurrent
_________________________________________________________________
BLI003BLI004BLI003BBL_WORLD2012010220120103N
BLI003BLI004BLI005BBL_WORLD20120103NULL Y
is some sort of recursion required
February 14, 2012 at 9:38 am
I managed to get the desired result by using a recursive CTE as follows.
CREATE TABLE #tstDimPortfolio
(
[ID][INT] IDENTITY (1,1) NOT NULL,
[UPI] [varchar](20) NOT NULL,
[MF_CODE] [varchar](10) NULL,
[BH_Code] [varchar](10) NULL,
[CR_Code] [varchar](10) NULL,
[ValidFrom][varchar](10) NOT NULL,
[ValidTo][varchar](10) NULL,
[IsCurrent] [CHAR] (1) NULL,
[DateofChange] [varchar](10) NULL,
[LookupMF_CODE] [varchar](10) NULL,
[LookupBH_Code] [varchar](10) NULL,
[LookupCR_Code] [varchar](10) NULL,
)
INSERT INTO #tstDimPortfolio
SELECT 'B06531','B06531','','B06531','20111230',NULL,'Y','20120101','','B06531', ''UNION ALL
SELECT 'BLI003','','BLI003','BBL_WORLD','20111230',NULL,'Y','20120102','BLI004','', ''UNION ALL
SELECT 'BLI003','','BLI003','BBL_WORLD','20111230',NULL,'Y','20120103','','BLI005', ''UNION ALL
SELECT 'BLI027','BLI027','L147','BBL_GBN','20111230',NULL,'Y','20120104','','L146', ''
SELECT * FROM #tstDimPortfolio
SELECT * FROM #tstDimPortfolio
SELECT ROW_NUMBER() OVER (PARTITION BY UPI ORDER BY UPI, DateofChange) Sequence,
UPI,
CASE WHEN LookupMF_CODE <> '' THEN LookupMF_CODE ELSE MF_CODE END MF_CODE,
CASE WHEN LookupMF_CODE <> '' THEN LookupBH_Code ELSE BH_Code END BH_Code,
CASE WHEN LookupCR_Code <> '' THEN LookupCR_Code ELSE CR_Code END CR_Code,
ValidFrom, ValidTo, IsCurrent, DateofChange,
LookupMF_CODE,
LookupBH_CODE,
LookupCR_CODE
INTO #Dimension_Table
FROM #tstDimPortfolio
;WITH AddedDim AS
(SELECT * FROM
(
SELECT
ROW_NUMBER() OVER (PARTITION BY UPI ORDER BY UPI, DateofChange) Sequence,
UPI,
CASE WHEN LookupMF_CODE <> '' THEN LookupMF_CODE ELSE MF_CODE END MF_CODE,
CASE WHEN LookupBH_CODE <> '' THEN LookupBH_Code ELSE BH_Code END BH_Code,
CASE WHEN LookupCR_Code <> '' THEN LookupCR_Code ELSE CR_Code END CR_Code,
DateofChange AS ValidFrom,
ValidTo,
IsCurrent
FROM #tstDimPortfolio
)A
WHERE SEQUENCE = 1
UNION ALL
SELECT
DT.Sequence
, DT.UPI
,CASE WHEN DT.LookupMF_CODE <> '' THEN DT.LookupMF_CODE ELSE DM.MF_CODE END MF_CODE
,CASE WHEN DT.LookupBH_CODE <> '' THEN DT.LookupBH_Code ELSE DM.BH_Code END BH_Code
,CASE WHEN DT.LookupCR_Code <> '' THEN DT.LookupCR_Code ELSE DM.CR_Code END CR_Code
,DT.DateofChange AS ValidFrom
,DT.ValidTo
,DT.IsCurrent
FROM #Dimension_Table dt inner join
AddedDim dm
on DT.UPI = DM.UPI
AND dt.Sequence = DM.Sequence + 1
)
SELECT *
INTO #temp
FROM AddedDim
Thanks for the initial help that got me thinking. Much appreciated
February 15, 2012 at 12:20 am
This was removed by the editor as SPAM
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply