Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to populate end date based on start date


How to populate end date based on start date

Author
Message
kiran.rajenimbalkar
kiran.rajenimbalkar
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 Visits: 316
Hi Professional ,

I have to populate end date based on start date of next record - 1
So please sugget me how to do this in my below SQL

CREATE TABLE tmp_end_date
(ID INT,
tgs_rec_eff_dt DATE)

GO




insert into tmp_end_date values(1300505,'2012-08-01 15:57:15')
insert into tmp_end_date values(1300505,'2012-08-01 15:55:16')
insert into tmp_end_date values(1300505,'2012-07-30 11:29:34')
insert into tmp_end_date values(1300505,'1905-07-01')

GO



My SQL

WITH aa AS
(
SELECT ID,tgs_rec_eff_dt FROM tmp_end_date
)
SELECT
ROW_NUMBER()over(partition by ID order by tgs_rec_eff_dt) rownumber,
ID,
tgs_rec_eff_dt,
NULL as end_date ---populate based on start date of next record - 1
FROM aa


Cadavre
Cadavre
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2594 Visits: 8436
Next record based on what? What is the ordering here?

Sample data: -
CREATE TABLE tmp_end_date(ID INT,tgs_rec_eff_dt DATETIME);
INSERT INTO tmp_end_date
SELECT ID, tgs_rec_eff_dt
FROM (VALUES(1300505,'2012-08-01 15:57:15'),(1300505,'2012-08-01 15:55:16'),
(1300505,'2012-07-30 11:29:34'),(1300505,'1905-07-01'))a(ID, tgs_rec_eff_dt);



My guess for what you want to do: -
SELECT a.ID, a.tgs_rec_eff_dt AS startDate, b.tgs_rec_eff_dt AS endDate
FROM tmp_end_date a
OUTER APPLY (SELECT TOP 1 tgs_rec_eff_dt
FROM tmp_end_date b
WHERE a.tgs_rec_eff_dt < tgs_rec_eff_dt
ORDER BY tgs_rec_eff_dt ASC) b;



Result: -
ID          startDate               endDate
----------- ----------------------- -----------------------
1300505 2012-08-01 15:57:15.000 NULL
1300505 2012-08-01 15:55:16.000 2012-08-01 15:57:15.000
1300505 2012-07-30 11:29:34.000 2012-08-01 15:55:16.000
1300505 1905-07-01 00:00:00.000 2012-07-30 11:29:34.000



Forever trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search