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

How to populate end date based on start date Expand / Collapse
Author
Message
Posted Monday, October 15, 2012 5:35 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 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

Post #1372660
Posted Monday, October 15, 2012 5:42 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:30 AM
Points: 2,443, Visits: 7,559
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



Not a DBA, just 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


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1372664
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse