Merge Join and Implementing type 2 need Suggestions

Question

Merge Join and Implementing type 2 need Suggestions

gopinathreddy.d

Mr or Mrs. 500

Points: 561
More actions
December 21, 2011 at 9:02 pm

#391185

CREATE TABLE [dbo].[StagingIssuer](
[AsODate] [datetime] not NULL,
[AssetSK] [varchar](100) not NULL,
[IssuerCode] [varchar](100) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[IssuerDim](
[IssuerSK] [int] IDENTITY(1,10) NOT NULL,
[AssetSK] [varchar](100) not NULL,
[IssuerCode] [varchar](100) NULL,
[StartDate] [datetime] not NULL,
[EndDate] [datetime] NULL
) ON [PRIMARY]
Data In [StagingIssuer] :
AsODateAssetSKIssuerCode
2011-03-18 00:00:00.000013TEST1 -------------- day 1 (I will pick the row Daily)
2011-03-19 00:00:00.000013TEST2 -------------- day 2
2011-03-25 00:00:00.000013TEST3 -------------- day 3
Inserting Rows[IssuerDim] Using Merge joint Implimenting Type 2 (Below Code Works For this Senerio)
IssuerSKAssetSKIssuerCodeStartDateEndDate
1013TEST12011-03-18 00:00:00.0002011-03-18 00:00:00.000
11013TEST22011-03-19 00:00:00.0002011-03-24 00:00:00.000
21013TEST32011-03-25 00:00:00.0002999-12-31 00:00:00.000
Below Query works Fine above seniero
INSERT INTO dbo.IssuerDim (AssetSK, IssuerCode, StartDate, EndDate)
SELECT Mrg.AssetSK,mrg.IssuerCode,mrg.AsODate,'2999-12-31'
FROM
(
MERGE INTO [IssuerDim] AS DST
USING [StagingIssuer] AS SRC
ON (DST.AssetSK = SRC.AssetSK) and
DST.EndDate = '2999-12-31 00:00:00.000' and
(DST.StartDate !=SRC.AsODate)
and (SRC.AsODate > DST.StartDate)
WHEN MATCHED
THEN UPDATE
SET DST.EndDate = (SRC.AsODate -1)
OUTPUT SRC.AssetSK, SRC.IssuerCode, SRC.AsODate,$Action AS MergeAction
) AS MRG
WHERE MRG.MergeAction = 'UPDATE'
;
But My New Problem is :
Data In [StagingIssuer] :
AsODateAssetSKIssuerCode
2011-03-18 00:00:00.000013TEST1 -------------- day 1
2011-03-19 00:00:00.000013TEST2 -------------- day 2
2011-03-25 00:00:00.000013TEST3 -------------- day 3
2011-03-22 00:00:00.000013TEST4 -------------- day 4
If i got Row on day 4 with AsODate (2011-03-22) in to table [StagingIssuer]
I need to insert and update as shown below
IssuerSKAssetSKIssuerCodeStartDateEndDate
1013TEST12011-03-18 00:00:00.0002011-03-18 00:00:00.000
11013TEST22011-03-19 00:00:00.0002011-03-21 00:00:00.000
31013TEST42011-03-22 00:00:00.0002011-03-24 00:00:00.000
21013TEST32011-03-25 00:00:00.0002999-12-31 00:00:00.000
Need suggestions how to implement above

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

Why is the EndDate column used for all the values of AssetSK?

Test4 is a new business key, so why should EndDate of other business keys be adjusted? This is not how SCD2 works, SCD2 keeps history for each unique value of the business key.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

gopinathreddy.d Mr or Mrs. 500 Points: 561 More actions · Answer 2

Thanks for your reply

"Koen Verbeeck" yes,may be or may not be a SCD type2 but its is a business requirement to handle if i get the previous records i have adjust it with in target rows can you please help me............