Merge Join and Implementing type 2 need Suggestions

Question

Post reply

Merge Join and Implementing type 2 need Suggestions

gopinathreddy.d

Mr or Mrs. 500

Points: 561
More actions
December 21, 2011 at 5:49 pm

#99572

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
Hope you understand my problem i need suggestion,how to implement it...
here is my mail ID : gopinathreddy.d@gmail.com

Viewing 5 posts - 1 through 4 (of 4 total)

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

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

The easiest way that I've found to do this is to write an INSERT Trigger for the process.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 2

Duplicate Post.

http://www.sqlservercentral.com/Forums/Topic1225529-392-1.aspx

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

Dev (12/22/2011)
Duplicate Post.
http://www.sqlservercentral.com/Forums/Topic1225529-392-1.aspx

Normally when you take on the responsibility of identifying a duplicate post, you also take on the responsibility of a little traffic control. This post had an answer on it before the other one so you should have put the duplicate post notice on the other one.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 4

Jeff Moden (12/22/2011)
Dev (12/22/2011)
Duplicate Post.
http://www.sqlservercentral.com/Forums/Topic1225529-392-1.aspx
Normally when you take on the responsibility of identifying a duplicate post, you also take on the responsibility of a little traffic control. This post had an answer on it before the other one so you should have put the duplicate post notice on the other one.

Unfortunately, I was little late in marking it Duplicate. Both the posts had replies on it.