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)
 Get the Next Date

Author  Topic 

angtez
Starting Member

4 Posts

Posted - 2011-09-16 : 11:05:58
Hi All,

I have a question that I'm sure has been answered at some point in time, but I searched and have not found, so here goes.

I have a table (member_dates) that looks like:

memberid visit_date
123456 2011-01-17 00:00:00.000
123456 2011-02-10 00:00:00.000
123456 2011-03-12 00:00:00.000
645321 2011-02-15 00:00:00.000
645321 2011-05-15 00:00:00.000
645321 2011-08-15 00:00:00.000

I want to create a table that looks like:

memberid visit_date next_visit_date
123456 2011-01-17 00:00:00.000 2011-02-10 00:00:00.000
123456 2011-02-10 00:00:00.000 2011-03-12 00:00:00.000
645321 2011-02-15 00:00:00.000 2011-05-15 00:00:00.000
645321 2011-05-15 00:00:00.000 2011-08-15 00:00:00.000

Is this possible in SQL Server 2005 or am I trying to defy the laws of nature?

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-16 : 11:14:55
Hello,

Perhaps something like;

SELECT 
memberid,
visit_date,
next_visit_date
FROM
(
SELECT memberid, visit_date, (SELECT MIN(visit_date) AS visit_date FROM member_dates n WHERE n.memberid = t.memberid AND n.visit_date > t.visit_date) as next_visit_date
FROM member_dates t
) d
WHERE next_visit_date IS NOT NULL


HTH.

EDIT... replaced table name with OP's table name.
Go to Top of Page

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-09-16 : 11:21:03
Antez,
How do you get the next_visit_date? Is it manual entry at the time of the insert? Or is this something that needs to be calculated based on the visit_date?
Go to Top of Page

angtez
Starting Member

4 Posts

Posted - 2011-09-16 : 11:41:37
quote:
Originally posted by flamblaster

Antez,
How do you get the next_visit_date? Is it manual entry at the time of the insert? Or is this something that needs to be calculated based on the visit_date?



The next date is the date after the first date. For example, the first date for Member 123456 is 2011-01-17 00:00:00.000. The next date for that same Member is 2011-02-10 00:00:00.000. The date that follows the first date becomes the next visit date.
Go to Top of Page

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-09-16 : 13:31:46
Right, but what I'm not clear on is how the initial entry comes up. So are you just transferring data from the original members_dates table into another table? Are you altering the original table to allow for the new column and only doing this once? Is it going to be an ongoing thing that always needs to be recalculated?
Go to Top of Page

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-09-16 : 19:38:34
If you only need it to show in a Select statement, or if you want to pull data from a select statement and update the table, you could use this Select. Still not clear how you'd like to get the data to update and/or alter your table structure. Try this though:

[CODE]

declare @member_dates table (MemberId int, VisitDate date) --Changed to date rather than datetime
insert into @member_dates (MemberId, VisitDate)
values
(123456, '2011-01-17'),
(123456, '2011-02-10'),
(123456, '2011-03-12'),
(645321, '2011-02-15'),
(645321, '2011-05-15'),
(645321, '2011-08-15')

SELECT A.MemberId, A.VisitDate, B.VisitDate AS NextVisitDate
FROM (select MemberId, VisitDate, ROW_NUMBER() over (Partition by MemberId Order by VisitDate) as Seq
from @member_dates) A
JOIN
(select MemberId, VisitDate, ROW_NUMBER() over (Partition by MemberId Order by VisitDate) as Seq
from @member_dates) B ON B.MemberId=A.MemberId AND B.Seq=A.Seq+1
[/CODE]
Go to Top of Page

angtez
Starting Member

4 Posts

Posted - 2011-09-18 : 15:12:55
Thanks Flamblaster. Works like a charm!

quote:
Originally posted by flamblaster

If you only need it to show in a Select statement, or if you want to pull data from a select statement and update the table, you could use this Select. Still not clear how you'd like to get the data to update and/or alter your table structure. Try this though:

[CODE]

declare @member_dates table (MemberId int, VisitDate date) --Changed to date rather than datetime
insert into @member_dates (MemberId, VisitDate)
values
(123456, '2011-01-17'),
(123456, '2011-02-10'),
(123456, '2011-03-12'),
(645321, '2011-02-15'),
(645321, '2011-05-15'),
(645321, '2011-08-15')

SELECT A.MemberId, A.VisitDate, B.VisitDate AS NextVisitDate
FROM (select MemberId, VisitDate, ROW_NUMBER() over (Partition by MemberId Order by VisitDate) as Seq
from @member_dates) A
JOIN
(select MemberId, VisitDate, ROW_NUMBER() over (Partition by MemberId Order by VisitDate) as Seq
from @member_dates) B ON B.MemberId=A.MemberId AND B.Seq=A.Seq+1
[/CODE]

Go to Top of Page

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-09-18 : 16:34:19
Welcome :)
Go to Top of Page
   

- Advertisement -