Set end date for a row to start date of next row minus 1 day

Question

Post reply

Set end date for a row to start date of next row minus 1 day

smw147

SSCommitted

Points: 1544
More actions
April 12, 2016 at 8:11 am

#325630

Hi all,
I've looked for a solution for this, but have so far not found something that works correctly everytime. I'm trying to run a SQL against a table that has a date field, returning an additional column called enddate which should be the day before the startdate of the next record.
CREATE TABLE ##myTable (PremiumID INT, PolicyID INT, startdate DATE)
INSERT INTO ##myTable (PremiumID, PolicyID, startdate) VALUES (1000001,1000001,'01-Jan-2014')
INSERT INTO ##myTable (PremiumID, PolicyID, startdate) VALUES (1000002,1000001,'01-Feb-2015')
INSERT INTO ##myTable (PremiumID, PolicyID, startdate) VALUES (1000003,1000001,'01-Mar-2016')
INSERT INTO ##myTable (PremiumID, PolicyID, startdate) VALUES (1000004,1000002,'10-Jun-2015')
INSERT INTO ##myTable (PremiumID, PolicyID, startdate) VALUES (1000005,1000002,'01-Apr-2016')
PremiumID is the Primary Key of this table.
PolicyID is a foreign key link to a Policy table, and indicates the policy the premium is for.
What I'm looking to achieve is this:
| PremiumID | PolicyID | startdate | enddate |
|-----------|----------|-------------|-------------|
| 1000001 | 1000001 | 01-Jan-2014 | 31-Jan-2015 |
|-----------|----------|-------------|-------------|
| 1000002 | 1000001 | 01-Feb-2015 | 29-Feb-2016 |
|-----------|----------|-------------|-------------|
| 1000003 | 1000001 | 01-Mar-2016 | NULL |
|-----------|----------|-------------|-------------|
| 1000004 | 1000002 | 10-Jun-2015 | 31-Mar-2016 |
|-----------|----------|-------------|-------------|
| 1000005 | 1000002 | 01-Apr-2016 | NULL |
|------------------------------------|-------------|
I hope this makes sense.
Thanks
Steve
Regards
Steve

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

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

I know that you're posting on the 2008 forum, but I'll include the 2012 option just in case.

--SQL Server 2008/2005

WITH CTE AS(

SELECT *, ROW_NUMBER() OVER(PARTITION BY PolicyID ORDER BY startdate) rn

FROM ##myTable

)

SELECT a.PremiumID,

a.PolicyID,

a.startdate,

DATEADD( dd, -1, b.startdate) AS enddate

FROM CTE a

LEFT

JOIN CTE b ON a.PolicyID = b.PolicyID

AND a.rn = b.rn - 1;

--SQL Server 2012+

SELECT *,

DATEADD(dd, -1, LEAD(startdate) OVER(PARTITION BY PolicyID ORDER BY startdate))

FROM ##myTable;

Feel free to ask any questions you might have.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Neil Burton SSC-Insane Points: 23013 More actions · Answer 2

WITH dates AS

(

SELECT

*

,RowNo = ROW_NUMBER() OVER(PARTITION BY policyID ORDER BY startdate)

FROM ##myTable

)

SELECT

d1.PremiumID

,d1.PolicyID

,d1.StartDate

,EndDate = DATEADD(day,-1,d2.startdate)

FROM dates d1

LEFT JOIN dates d2 ON d2.PolicyID = d1.PolicyID

AND d2.RowNo = d1.RowNo + 1;

This does it too, Luis beat me to it.

I think there's a mistake on your results though. You've got 2014-02-29 for the end date for PremiumID2. Should that be 2016-02-29?

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

smw147 SSCommitted Points: 1544 More actions · Answer 3

Thanks for that 🙂 Never heard of LEAD before. Very interesting.

Regards

Steve

smw147 SSCommitted Points: 1544 More actions · Answer 4

BWFC (4/12/2016)
WITH dates AS
I think there's a mistake on your results though. You've got 2014-02-29 for the end date for PremiumID2. Should that be 2016-02-29?

Thanks 🙂 Yes, it's a typo error :doze: Edited now 🙂

Regards

Steve

drew.allen SSC Guru Points: 76998 More actions · Answer 5

smw147 (4/12/2016)
Thanks for that 🙂 Never heard of LEAD before. Very interesting.

LEAD and LAG were introduced with SQL2012. You won't be able to use them if you are still on SQL2008.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

smw147 SSCommitted Points: 1544 More actions · Answer 6

drew.allen (4/12/2016)
smw147 (4/12/2016)
Thanks for that 🙂 Never heard of LEAD before. Very interesting.
LEAD and LAG were introduced with SQL2012. You won't be able to use them if you are still on SQL2008.
Drew

Thanks. I'll use the CTE route as the script I'm writing will be re-used on all versions of SQL from 2005+ so I need to be sure it'll work for all versions.

Regards

Steve

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

smw147 (4/13/2016)
drew.allen (4/12/2016)
smw147 (4/12/2016)
Thanks for that 🙂 Never heard of LEAD before. Very interesting.
LEAD and LAG were introduced with SQL2012. You won't be able to use them if you are still on SQL2008.
Drew
Thanks. I'll use the CTE route as the script I'm writing will be re-used on all versions of SQL from 2005+ so I need to be sure it'll work for all versions.

That's what I expected, but I wanted to give you the vision on the new features available. That way you'd be aware if you manage to upgrade your servers. 😉

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

smw147 SSCommitted Points: 1544 More actions · Answer 8

smw147

SSCommitted

Points: 1544

April 13, 2016 at 9:49 am

#1871767

Thanks for all your help guys 🙂

Regards

Steve