October 5, 2015 at 10:40 am
Actually , for historical record I have to update End date as Updated Date where end date is Default date '2099-12-31'and for new record with same id , have to insert with Default date '2099-12-31'.
But it is not updating default the end date when there is date value. But when I loaded with Null as Enddate it is updating. It is working for Null and not working for Dates.
I am trying to figure out from one day , it is not helping me out. So please any one got a chance try to give some tips. Please refer below query for better understanding.
No Luck--- UPDATE [dbo].[DimMember] SET [EndDate] = GETDATE() , UpdateDate = GETDATE() WHERE [MemberNumber] = 1041 AND [EndDate] ='2015-07-19 00:00:00.000'
Working for this querry UPDATE [dbo].[DimMember] SET [EndDate] = GETDATE() , UpdateDate = GETDATE() WHERE [MemberNumber] = 1041 AND [EndDate] IS NULL
October 5, 2015 at 10:47 am
sqlmaverick (10/5/2015)
Actually , for historical record I have to update End date as Updated Date where end date is Default date '2099-12-31'and for new record with same id , have to insert with Default date '2099-12-31'.But it is not updating default the end date when there is date value. But when I loaded with Null as Enddate it is updating. It is working for Null and not working for Dates.
I am trying to figure out from one day , it is not helping me out. So please any one got a chance try to give some tips. Please refer below query for better understanding.
No Luck--- UPDATE [dbo].[DimMember] SET [EndDate] = GETDATE() , UpdateDate = GETDATE() WHERE [MemberNumber] = 1041 AND [EndDate] ='2015-07-19 00:00:00.000'
Working for this querry UPDATE [dbo].[DimMember] SET [EndDate] = GETDATE() , UpdateDate = GETDATE() WHERE [MemberNumber] = 1041 AND [EndDate] IS NULL
GetDate() returns a DateTime, not just a date.
If EndDate should contain only a date, update its Data Type to Date, to avoid storing the time component.
If EndDate should contain a datetime, your WHERE clause needs to change:
... WHERE [MemberNumber] = 1041 AND cast([EndDate] as date) ='20150719'
--Edit: Changed date format to ISO standard YYYYMMDD
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 5, 2015 at 11:13 am
Hi Phi Parkin,
I tried your idea, it didn't worked out. It is not at all going to update side at all. But thanks for reply and it is really appreciated.
October 5, 2015 at 11:25 am
OK. I don't fully understand what your data looks like.
Please provide the following:
1) The results of running
select MemberNumber, EndDate, UpdateDate
from dbo.DimMember
where MemberNumber = 1041
before any update and then
2) Please show the above results as you would like them to look after the update.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 5, 2015 at 12:23 pm
MemberNumberEndDate UpdateDate
1041 2099-12-31 00:00:00.0002015-10-05 12:10:11.000
1041 2099-12-31 00:00:00.0002015-10-05 12:10:30.000
October 5, 2015 at 12:30 pm
Below one is the data after SCD 2
MemberNumberMemberNameActivityStatusEndDateUpdateDate
1041Community BankInactive2099-12-31 00:00:00.0002015-10-05 12:10:11.000
1041Discover CreditActive2099-12-31 00:00:00.0002015-10-05 12:10:30.000
And data expected to be as shown below
MemberNumberMemberNameActivityStatusEndDateUpdateDate
1041Community BankInactive2015-10-04 12:10:11.0002015-10-05 12:10:11.000 (Historical Record)
1041Discover CreditActive2099-12-31 00:00:00.0002015-10-05 12:10:30.000 (New Record)
October 5, 2015 at 1:25 pm
You need to change the custom properties setting in SCD.
Click on SCD
1. Open show advance editor
2. Common Properties -> custom properties -> CurrentRowWhere
3. Use this condition [StartDate] IS NOT NULL AND [EndDate] ='2019-12-31 00:00:00.000'
October 5, 2015 at 2:22 pm
Hi Kumar4u (Chasing man),
your answer really worked out, it is appreciated. Thanks a lot for your prompt reply.
Thank you.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply