Start Date and End Date? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Start Date and End Date?

Author

Topic

jay83091
Starting Member

14 Posts

Posted - 2011-06-06 : 00:02:33

Hi Experts,

I am trying to get the start date and end date based on the date ranking.

i.e. based on the date ranking, return the start date and end dates.
If first date is 31/05/2010 and 2nd ranked date is 30/06/2010 then start date willb e 31/05/2010 and end date will be 29/06/2010.

I got up to doing the ranking but can't really get the start, end date columns.

Please help T_T

Source data contains dates and ID.

Date ID
31/05/2010 57
31/05/2010 57
30/06/2010 57
31/07/2010 57
31/08/2010 57
30/09/2010 57
31/10/2010 57
31/12/2010 57
31/01/2011 57
31/03/2011 57
30/04/2011 57
31/05/2011 57
31/05/2010 4004
31/07/2010 4004
30/11/2010 4004
30/04/2011 4004

Desired Output
Date ID RANK Effective Start Effective End
31/05/2010 57 1 31/05/2010 29/06/2010
31/05/2010 57 1 31/05/2010 29/06/2010
30/06/2010 57 3 30/06/2010 30/07/2010
31/07/2010 57 4 31/07/2010 30/08/2010
31/08/2010 57 5 31/08/2010 29/09/2010
30/09/2010 57 6 30/09/2010 30/09/2010
31/10/2010 57 7 31/10/2010 30/11/2010
31/12/2010 57 8 31/12/2010 30/01/2011
31/01/2011 57 9 31/01/2011 30/03/2011
31/03/2011 57 10 31/03/2011 29/04/2011
30/04/2011 57 11 30/04/2011 30/05/2011
31/05/2011 57 12 31/05/2011 31/12/9999
31/05/2010 4004 1 31/05/2010 30/07/2010
31/07/2010 4004 2 31/07/2010 29/11/2010
30/11/2010 4004 3 30/11/2010 29/04/2011
30/04/2011 4004 4 30/04/2011 31/12/9999

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-06-06 : 03:48:01

[code]
; with
cte as
(
select *, row_no = row_number() over (partition by ID order by DATE)
from yourtable
)
select c1.DATE, c1.ID, start_date = c1.DATE, end_date = isnull(dateadd(day, -1, c2.DATE), '9999-12-31')
from cte c1
left join cte c2 on c1.ID = c2.ID
and c1.row_no = c2.row_no - 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]