Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

End Date Issue Expand / Collapse
Author
Message
Posted Wednesday, October 17, 2012 3:15 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 9, 2014 7:21 AM
Points: 98, Visits: 251
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
Posted Wednesday, October 17, 2012 3:21 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:50 AM
Points: 1,074, Visits: 6,357
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
Posted Wednesday, October 17, 2012 3:38 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 9, 2014 7:21 AM
Points: 98, Visits: 251
Hi Thanks for your reply :)
But its not giving me proper result.
Post #1373704
Posted Wednesday, October 17, 2012 3:39 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 9, 2014 7:21 AM
Points: 98, Visits: 251
I have to update my end date column as like above sample example.
Post #1373705
Posted Wednesday, October 17, 2012 3:54 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 5:30 AM
Points: 880, Visits: 669
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
Posted Wednesday, October 17, 2012 4:13 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 9, 2014 7:21 AM
Points: 98, Visits: 251
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
Posted Wednesday, October 17, 2012 8:19 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 24, 2014 9:12 AM
Points: 285, Visits: 504
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



---------------------------------------------------------------
Mike Hahn - Future MCM 2025
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar
I want a personal webpage
I want to win the lotto
I want a gf like Tiffa
Post #1373823
Posted Wednesday, October 17, 2012 8:34 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 9, 2014 7:21 AM
Points: 98, Visits: 251
Thanks Expert..Its working as expected :)
Post #1373843
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse