End Date Issue

Question

End Date Issue

kiran.rajenimbalkar

SSCrazy

Points: 2603
More actions
October 17, 2012 at 3:15 am

#269697

Hi Professional,
I need your help for solving below issue,
CREATE TABLE tmp_end_date
(ID INT,
tgs_rec_eff_dt DATE,
tgs_rec_end_dt DATE,
SEQNUM INT )
GO
insert into tmp_end_date values(12345,'15-Mar-2012',NULL,4)
insert into tmp_end_date values(12345,'10-Feb-2012',NULL,3)
insert into tmp_end_date values(12345,'2-Feb-2012',NULL,2)
insert into tmp_end_date values(12345,'05-Jan-2012',NULL,1)
GO
I want to show end date as like below example.
12345 15-Mar-2012 NULL
12345 10-Feb-2012 14-Mar-2012 (one day less than 15-Mar-2012)
12345 2-Feb-2012 9-Feb-2012 (one day less than 10-Feb-2012)
12345 05-Jan-2012 1-Feb-2012 (one day less than 2-Feb-2012)
But In some cases I have two dates which are same but differ with time then in that case the enddate shoud be as like below example
12345'2012-10-08 12:59:36'
12345'2012-10-08 12:59:03' '2012-10-08 12:59:36'
Please suggest me how to do this

Viewing 8 posts - 1 through 7 (of 7 total)

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

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 1

You have two sequence columns in your table - do either of them have gaps? If one of them is guaranteed to have no gaps, then join the table to itself, like this;

SELECT *

FROM Table a

LEFT JOIN Table b ON b.ID = a.ID + 1

Have a look at the results. If you are not sure how to proceed, post back.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

kiran.rajenimbalkar SSCrazy Points: 2603 More actions · Answer 2

Hi Thanks for your reply 🙂

But its not giving me proper result.

kiran.rajenimbalkar SSCrazy Points: 2603 More actions · Answer 3

I have to update my end date column as like above sample example.

🙂

subbu1 Hall of Fame Points: 3281 More actions · Answer 4

PLS TRY BELOW CODE:

CREATE TABLE #tmp_end_date

(ID INT,

tgs_rec_eff_dt DATE,

tgs_rec_end_dt DATE,

SEQNUM INT )

GO

insert into #tmp_end_date values(12345,'15-Mar-2012',NULL,4)

insert into #tmp_end_date values(12345,'10-Feb-2012',NULL,3)

insert into #tmp_end_date values(12345,'2-Feb-2012',NULL,2)

insert into #tmp_end_date values(12345,'05-Jan-2012',NULL,1)

;WITH A

AS(SELECT *,DENSE_RANK()OVER(PARTITION BY ID ORDER BY tgs_rec_eff_dt DESC ) RANK FROM #tmp_end_date)

--SELECT * FROM A

SELECT T.ID,T.tgs_rec_eff_dt,(SELECT DATEADD(DD,-1,T1.tgs_rec_eff_dt) FROM A T1

WHERE T1.RANK+1=T.RANK),SEQNUM FROM A T

ORDER BY 2 DESC

kiran.rajenimbalkar SSCrazy Points: 2603 More actions · Answer 5

Hi Expert,

Its worked but failes for below data....when i was inserting and testing

CREATE TABLE #tmp_end_date

(ID INT,

tgs_rec_eff_dt DATE,

tgs_rec_end_dt DATE,

SEQNUM INT )

GO

insert into #tmp_end_date values(12345,'15-Mar-2012',NULL,4)

insert into #tmp_end_date values(12345,'10-Feb-2012',NULL,3)

insert into #tmp_end_date values(12345,'2-Feb-2012',NULL,2)

insert into #tmp_end_date values(12345,'05-Jan-2012',NULL,1)

insert into #tmp_end_date values(33333,'31-Jan-2012',NULL,2)

insert into #tmp_end_date values(33333,'12-Dec-2011',NULL,1)

insert into #tmp_end_date values(1300470,'2012-10-08 12:59:36',NULL,3)

insert into #tmp_end_date values(1300470,'2012-10-08 12:59:03',NULL,2)

insert into #tmp_end_date values(1300470,'1905-07-01',NULL,1)

WITH A

AS(SELECT *,DENSE_RANK()OVER(PARTITION BY ID ORDER BY tgs_rec_eff_dt) RANK FROM #tmp_end_date)

SELECT T.ID,T.tgs_rec_eff_dt,(SELECT DATEADD(DD,-1,T1.tgs_rec_eff_dt) FROM A T1

WHERE T1.RANK+1=T.RANK),SEQNUM FROM A T

Mike.Hahn SSCarpal Tunnel Points: 4053 More actions · Answer 6

Here's a tweak to the code above to get rid of the error...

still not sure how to get your 2nd requirement to concatenate the 2 dates if they're on the same day but different timestamp without doing it through an sp... or a table function...

CREATE TABLE #tmp_end_date

(ID INT,

tgs_rec_eff_dt DATETIME,

tgs_rec_end_dt DATETIME,