March 10, 2016 at 8:06 pm
I am trying to eliminate overlap spans , but meeting dead ends. Not sure what the approach would be for this.
Scenario 1
fromdate todate
2015/12/01 9999/99/99
2016/01/01 9999/99/99
I need to correct the first span to 2015/12/01 to 2015/12/31 .
Scenario 2
fromdate todate
2015/10/14 2015/10/14
2015/10/15 2015/11/20
2015/11/01 2100/12/31---eliminate
2015/11/21 2015/12/31
2016/01/01 2100/12/31
I need to correct it to
2015/10/14 2015/10/14
2015/10/15 2015/11/20
2015/11/21 2015/12/31
2016/01/01 2100/12/31
Below is the code I am using
select c1.memberid, c1.fromdate, c1.todate, c1.adddate, c1.AddInit, c1.status, c2.fromdate, c2.todate, c2.addresstype, c2.AddDate, c2.addinit, c2.status
from prod.dbo.memcontact c1
inner join prod.dbo.memcontact c2
on c1.memberid = c2.memberid
and c1.addresstype = c2.addresstype
where c1.addresstype = 'eresidence'
and convert(date, c2.todate, 101) >= convert(date, c1.fromdate, 101)
and convert(date, c2.todate, 101) <= convert(date, c1.todate, 101)
and convert(date, c1.adddate, 101) > convert(date, c2.adddate, 101)
and c1.status = 'Valid'
and c2.status = 'valid'
Any help would be appreciated. Spent a lot of time on this and unable to achieve it.TIA
March 11, 2016 at 5:16 am
It helps if you post CREATE TABLE and INSERT statements for the table layout and sample data. Without that, I can only provide a generic pointer.
Join the table, using APPLY, to a query on itself with a filter of inner.fromdate > outer.todate and an aggregation to find min(fromdate). Compare that to the todate of the outer to see if there is an overlap, and to find the "correct" todate.
March 11, 2016 at 5:58 am
Hi Hugo,
Below is the create table and values inserted.
create table membercontact
(memberid int,
addresstype varchar(20),
status varchar(20),
fromdate date,
todate date,
adddate date,
addinit varchar(20),
changedate date,
changeinit varchar(20))
insert into membercontact values
(100001 , 'residence','valid','2015/12/01','2100/12/31','2015/12/01','deamon','2016/03/10',''deamon')
(100001 , 'residence','valid','2016/01/01','2100/12/31','2015/12/01','deamon','2016/03/10',''deamon')
second scenario
insert into membercontact values
(111000 , 'residence','valid','2014/10/14','2014/10/14','2016/03/01','deamon','2016/03/01',''deamon')
(111000 , 'residence','valid','2015/10/15','2015/11/20','2015/10/15','deamon','2016/03/10',''deamon')
(111000 , 'residence','valid','2015/11/01','2100/12/31','2015/10/07','deamon','2016/03/10',''deamon')
(111000 , 'residence','valid','2015/11/21','2015/12/31','2015/11/22','deamon','2016/02/26',''deamon')
(111000 , 'residence','valid','2016/01/01','2100/12/31','2015/11/25','deamon','2016/03/10',''deamon')
March 11, 2016 at 6:50 am
Try this:
SELECT m1.memberid, m1.fromdate,
m1.todate AS TodateEntered,
COALESCE(DATEADD(DAY, -1, m3.MinFromDate), '21001231') AS TodateCorrect
FROM dbo.membercontact AS m1
OUTER APPLY
(SELECT MIN(m2.fromdate) AS MinFromDate
FROM dbo.membercontact AS m2
WHERE m2.fromdate > m1.fromdate) AS m3;
March 11, 2016 at 7:58 am
Hi Hugo ,
Thank you for the quick reply. I checked the code ...the TOCORRECTDATE is all 11/30/2015. I am looking only for specific dates that need to change. How do I achieve that?
For the second scenario , I might have to probably just eliminate the record with fromdate 11/01/2015 to 2100/12/31 , because both fromdate and todate are overlap. ANy advice on this?
Even for the 1st scenario 1 I just need to correct the first record and the second needs to be the same.
March 11, 2016 at 8:19 am
So maybe my mindreading skills are not as good as I hoped they were. Perhaps this is what you actually intended?
SELECT m1.memberid, m1.fromdate,
m1.todate AS TodateEntered,
COALESCE(DATEADD(DAY, -1, m3.MinFromDate), '21001231') AS TodateCorrect
FROM dbo.membercontact AS m1
OUTER APPLY
(SELECT MIN(m2.fromdate) AS MinFromDate
FROM dbo.membercontact AS m2
WHERE m2.fromdate > m1.fromdate
AND m2.memberid = m1.memberid) AS m3;
For the second scenario , I might have to probably just eliminate the record with fromdate 11/01/2015 to 2100/12/31 , because both fromdate and todate are overlap. ANy advice on this?
Yes. Step away from the keyboard and go in a meeting room with the domain expert. Your "might have" and "probably" are red flags. You first need to get the specs fleshed out. There cannot be any uncertainty, either you have to delete the row or you don't, and that choice has to follow from a decision rule that is okay'ed by the domain expert.
Also, consider just dropping the todate column completely. If all intervals are always non-overlapping and consecutive, then you can always reconstruct the todate by looking at the first next fromdate. (Which is incendtally exactly what I did in the query to give you the corrected enddate).
March 11, 2016 at 9:02 am
Thank you Hugo . I have sent an email to the business user asking for how to handle that particular record.
I will check the code and get back.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply