Date data manipulation.

Question

Date data manipulation.

sql_2005_fan

SSCommitted

Points: 1871
More actions
June 9, 2016 at 8:47 pm

#327443

I have the following data
ID startDate EndDate
1 1/1/2001 1/9/2001
2 3/1/2001 6/1/2001
3 6/2/2001 1/1/2005
4 3/1/2005 5/7/2005
5 9/1/2006 10/11/2007
6 10/10/2007 3/1/2008
I am looking for a query to get the records for which ' the difference between the Enddate of the previous Id and StartDate of the current ID should be less than or equal to 15'
The result set should be like the following.
2 3/1/2001 6/1/2001
3 6/2/2001 1/1/2005
5 9/1/2006 10/11/207
6 10/10/2007 3/1/2008
Thank You.

Viewing 5 posts - 1 through 4 (of 4 total)

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

LinksUp SSCertifiable Points: 6526 More actions · Answer 1

Your stated requirements do not match up with your expected output. This is the output of a query showing the previous endDate in the row with the startDate that it is going to compare. The last column is the actual days elapsed between them. Note that only 2 rows meet your criteria or <= 15 days.

idstartDateEndDate prevDate DaysDiff

12001-01-012001-01-09NULL NULL

22001-03-012001-06-012001-01-0951

32001-06-022005-01-012001-06-011

42005-03-012005-05-072005-01-0159

52006-09-012007-10-112005-05-07482

62007-10-102008-03-012007-10-11-1

If that is what you really want then this query should do the trick:

select *, datediff(dd, x.prevDate, x.startDate )

from (Select id, startDate, EndDate, lag(endDate) over(order by id) prevDate

from someTable) x

where datediff(dd, x.prevDate, startDate) <= 15

I neglected to note that the above query will return rows 3 and 6. These are the only 2 that are <= 15 days.

__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

sql_2005_fan SSCommitted Points: 1871 More actions · Answer 2

I need to run this query also on sql 2008 database and getting the following error on 2008.

The Parallel Data Warehouse (PDW) features are not enabled.

How can I rewrite the above query for 2008.

Thanks.

LinksUp SSCertifiable Points: 6526 More actions · Answer 3

sql_2005_fan (6/10/2016)
I need to run this query also on sql 2008 database and getting the following error on 2008.
The Parallel Data Warehouse (PDW) features are not enabled.
How can I rewrite the above query for 2008.

Well, you are posting on a 2012 forum. SQL 2012 has lead and lag. And, you did not mention 2008. To do it in 2008 requires a self join. But you have not answered the question about your expected output.

select c.id, c.StartDate, c.EndDate, p.EndDate, datediff(dd, p.EndDate, c.startDate)

from someTable c

left outer join someTable p on p.id = c.id - 1

where datediff(dd, p.EndDate, c.startDate) <= 15

__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

Jeff Moden SSC Guru Points: 1003921 More actions · Answer 4

sql_2005_fan (6/10/2016)
I need to run this query also on sql 2008 database and getting the following error on 2008.
The Parallel Data Warehouse (PDW) features are not enabled.
How can I rewrite the above query for 2008.
Thanks.

Post your example data in a readily consumable format (see the first link in my signature line below for how to do that) and I'll show you.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)