how to match two different date columns in same table and update third dat column

Question

Post reply

how to match two different date columns in same table and update third dat column

Learner44

SSCarpal Tunnel

Points: 4466
More actions
May 30, 2015 at 4:00 pm

#318673

Hi friends,
I am returnnnig this blog after long time.
I need a little help.
I want to compare two columns in the same table called start date and end date for one clientId.
if clientId is having continuous refenceid and sartdate and enddate of reference that I don't need any caseopendate but if clientID has new reference id and it's start date is not continuous to its previous reference id then I need to set that start date as caseopendate.
I hope my problem is clear to you.
here is an example what I am looking for!
I have table containing 5 columns.
caseid
referenceid
startdate
enddate
caseopendate
my table shpuld look like this:
SourceSystemClientIdreferenceId servicestartdateserviceenddate caseopeneddateid
8901 r43 2008-04-01 2008-06-28
8901 r45 2008-06-27 2009-02-12
8901 r48 2009-02-13 2012-11-16
8901 r49 2013-02-13 2013-03-13 2013-02-13
8903 r50 2015-11-15 2015-12-16
8902 r45 2008-06-27 2009-02-12
8902 r48 2009-02-13 2012-11-16
8903 r50 2015-12-17 2015-12-29
8903 r51 2016-12-15 2013-12-29 2016-12-15

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

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

Learner44 SSCarpal Tunnel Points: 4466 More actions · Answer 1

i got almost 90% but need a help to finish it...

here is what I got,

use AdventureWorks2012

CREATE TABLE tbl

([MemberCode] int, [ClaimID] int, [StartDate] date, [EndDate] date, [CaseDate] date);

INSERT INTO tbl

([MemberCode], [ClaimID], [StartDate], [EndDate], [CaseDate])

VALUES

(00001, 012345, '2010-01-15 ', '2010-01-20 ',''),

(00001, 012350, '2010-01-19 ', '2010-01-22 ',''),

(00001, 012352, '2010-01-20 ', '2010-01-25 ',''),

(00001, 012355, '2010-01-26 ', '2010-01-30 ',''),

(00002, 012357, '2010-01-20 ', '2010-01-25 ',''),

(00002, 012359, '2010-01-30 ', '2010-02-05 ',''),

(00002, 012360, '2010-02-04 ', '2010-02-15 ',''),

(00003, 012365, '2010-02-15 ', '2010-03-02 ','');

select * from tbl

with s as

(

select *, row_number() over(partition by membercode order by startdate) rn

from tbl

)

select a.membercode, a.startdate, a.enddate, b.startdate as nextstartdate

,datediff(d, a.enddate, b.startdate) as gap

from s a

join s b on b.membercode = a.membercode and b.rn = a.rn + 1;

now I want to update the nextsartdate with startdate where gap value is > 1

sgmunson SSC Guru Points: 110640 More actions · Answer 2

Have you looked into the LEAD and LAG functions? The latter is what you appear to need. See the following blog for an example usage:

http://blog.sqlauthority.com/2011/11/15/sql-server-introduction-to-lead-and-lag-analytic-functions-introduced-in-sql-server-2012/[/url]

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

SQL aus HH SSC Eights! Points: 833 More actions · Answer 3

Many thanks to you for giving me this url !

Never heard of this lead/lag before (never searched for such a function 😉 )

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 4

Learner44 (5/30/2015)
i got almost 90% but need a help to finish it...
here is what I got,
use AdventureWorks2012
CREATE TABLE tbl
([MemberCode] int, [ClaimID] int, [StartDate] date, [EndDate] date, [CaseDate] date);
INSERT INTO tbl
([MemberCode], [ClaimID], [StartDate], [EndDate], [CaseDate])
VALUES
(00001, 012345, '2010-01-15 ', '2010-01-20 ',''),
(00001, 012350, '2010-01-19 ', '2010-01-22 ',''),
(00001, 012352, '2010-01-20 ', '2010-01-25 ',''),
(00001, 012355, '2010-01-26 ', '2010-01-30 ',''),
(00002, 012357, '2010-01-20 ', '2010-01-25 ',''),
(00002, 012359, '2010-01-30 ', '2010-02-05 ',''),
(00002, 012360, '2010-02-04 ', '2010-02-15 ',''),
(00003, 012365, '2010-02-15 ', '2010-03-02 ','');
select * from tbl
with s as
(
select *, row_number() over(partition by membercode order by startdate) rn
from tbl
)
select a.membercode, a.startdate, a.enddate, b.startdate as nextstartdate
,datediff(d, a.enddate, b.startdate) as gap
from s a
join s b on b.membercode = a.membercode and b.rn = a.rn + 1;
now I want to update the nextsartdate with startdate where gap value is > 1

Using that example (thank you for posting readily consumable code), all of the items for MemberCode 1 have overlapping dates making all 4 periods part of the overall period of 2010-01-15 thru 2010-01-30. Is that what you're expecting in that example?

--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)