End Date Issue

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » End Date Issue

End Date Issue

Rate Topic

Display Mode

Topic Options

Author

Message

kiran.rajenimbalkar

Posted Wednesday, October 17, 2012 3:15 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 16, 2013 3:45 AM
Points: 82, Visits: 220

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

Post #1373695

ChrisM@home

Posted Wednesday, October 17, 2012 3:21 AM

SSC Eights!

Group: General Forum Members
Last Login: Today @ 4:44 AM
Points: 921, Visits: 3,747

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.

^{Low-hanging fruit picker and defender of the moggies}

For better assistance in answering your questions, please read this.

Understanding and using APPLY, (I) and (II) Paul White

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

Post #1373698

kiran.rajenimbalkar

Posted Wednesday, October 17, 2012 3:38 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 16, 2013 3:45 AM
Points: 82, Visits: 220

Hi Thanks for your reply :)
But its not giving me proper result.

Post #1373704

kiran.rajenimbalkar

Posted Wednesday, October 17, 2012 3:39 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 16, 2013 3:45 AM
Points: 82, Visits: 220

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

Post #1373705

subbareddy542

Posted Wednesday, October 17, 2012 3:54 AM

SSC Eights!

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 4:17 AM
Points: 832, Visits: 613

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

Post #1373708

kiran.rajenimbalkar

Posted Wednesday, October 17, 2012 4:13 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 16, 2013 3:45 AM
Points: 82, Visits: 220

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

Post #1373716

Want a cool Sig

Posted Wednesday, October 17, 2012 8:19 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, April 26, 2013 2:58 PM
Points: 221, Visits: 452

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,
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 DESC ) 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
	and T.ID = T1.ID) End_Date
	,SEQNUM 
	FROM A T
ORDER BY 1,2 DESC

drop table #tmp_end_date