February 25, 2011 at 12:41 am
Hi,
I am facing some problem when I was populating the records in a table.
i.e after populating the records in a destination table I have checked the start date and end date its showing that end date is less then start date.So I am providing the script this is only for one member id but I am facing such type of problem for more the one member id's.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tmp_mbr_first]') AND type in (N'U'))
DROP TABLE [tmp_mbr_first]
CREATE TABLE [dbo].[tmp_mbr_first](
[Member_ID] [int] NULL,
[Start_Date] [datetime] NULL,
[Member_Status] [varchar](10) NOT NULL,
[Club_Id] [int] NULL,
[Member_Period] [bigint] NULL
) ON [PRIMARY]
insert into tmp_mbr_first values(1113826,'2005-07-02 00:00:00.000','Terminated',1234,1)
go
insert into tmp_mbr_first values(1113826,'2000-04-25 13:54:00.000','Terminated',1236,2)
go
insert into tmp_mbr_first values(1113826,'2000-04-25 00:00:00.000','Active',1234,3)
go
insert into tmp_mbr_first values(1113826,'1986-01-01 00:00:00.000','Active',1236,4)
go
------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tmp_mbr_second]') AND type in (N'U'))
DROP TABLE [dbo].[tmp_mbr_second]
GO
CREATE TABLE [dbo].[tmp_mbr_second](
[member_id] [int] NULL,
[start_date] [datetime] NULL,
[end_date] [datetime] NULL,
[member_status] [varchar](10) NULL,
[club_id] [int] NULL,
[member_period] [int] NULL
) ON [PRIMARY]
insert into [tmp_mbr_second]
SELECT a.[member_id]
,a.[start_date]
,CASE
WHEN a.member_period = 1 THEN '2079-06-06'
ELSE b.Start_Date-1
END end_date
,a.[member_status]
,a.[club_id]
,a.[member_period]
FROM [tmp_mbr_first] a with(tablock)
left JOIN [tmp_mbr_first] b with(tablock)
ON a.member_id = b.member_id
AND a.member_period = b.member_period + 1
I want to change the end date for those records which having end date is less then start date,
and end date I would see there same as start date or start date+1 and other records should be as it is.
So request you to please help me this problem.
Let me know in case of any information you need.
Best Regards,
Kiran
February 25, 2011 at 1:53 am
The prob is with case stmt.
CASE
WHEN a.member_period = 1 THEN '2079-06-06'
ELSE b.Start_Date-1
END end_date
If member_period is not equal to 1 u r making end date as startdate - 1. Instead of this use StartDate or StartDate + 1. This will solve u r prob.
February 25, 2011 at 2:00 am
Hi,
I have had used that condition start_date + 1 but my other end dates are coming wrong.
So request you to please suggest solution that I changed only that end dates.
Or you can modified my above script means I will implement your logic in my sql and will let you know the coming result.
Best Regards,
Kiran
February 25, 2011 at 2:29 am
It seems that you are trying to create a linked list where the EndDate of the Membership is 1 Less than the start date of the next member_period.
The problem you have is due to bad data in the records for member_period 2 and 3, as the start date in Member_Period 2 has a time allocated.
You could change the case to be
,CASE
WHEN a.member_period = 1 THEN '2079-06-06'
ELSE DateAdd(minute,-1,b.Start_Date)
END end_date
Solve this issue, however it will not solve other issues where the Dates between periods are midnight on the same date, so you may need to look at some additional logic that checks for the same start_date on both records.
Eg
,CASE
WHEN a.member_period = 1 THEN '2079-06-06'
WHEN b.Start_Date=a.Start_Date THEN b.Start_Date
ELSE DateAdd(minute,-1,b.Start_Date)
END end_date
It would need testing with a full data set and there are other possible issues.
HTH.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
February 25, 2011 at 5:25 am
Hi,
I have implemented your logic in my code,its worked fine.
I did not get any end date which is less then start date.
Thanks for your valuable help for doing this.
Best Regards,
Kiran
February 25, 2011 at 6:00 am
No Problem, It sometimes helps to have a second pair of eyes look at a problem. :crazy:
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply