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
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 5, 2015 at 12:23 pm
MemberNumber EndDate UpdateDate
1041 2099-12-31 00:00:00.000 2015-10-05 12:10:11.000
1041 2099-12-31 00:00:00.000 2015-10-05 12:10:30.000
October 5, 2015 at 12:30 pm
Below one is the data after SCD 2
MemberNumber MemberName ActivityStatus EndDate UpdateDate
1041 Community Bank Inactive 2099-12-31 00:00:00.000 2015-10-05 12:10:11.000
1041 Discover Credit Active 2099-12-31 00:00:00.000 2015-10-05 12:10:30.000
And data expected to be as shown below
MemberNumber MemberName ActivityStatus EndDate UpdateDate
1041 Community Bank Inactive 2015-10-04 12:10:11.000 2015-10-05 12:10:11.000 (Historical Record)
1041 Discover Credit Active 2099-12-31 00:00:00.000 2015-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