SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Get the Next Date
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

angtez
Starting Member

USA
4 Posts

Posted - 09/16/2011 :  11:05:58  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1631 Posts

Posted - 09/16/2011 :  11:14:55  Show Profile  Reply with Quote
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.

Edited by - ehorn on 09/16/2011 11:17:07
Go to Top of Page

flamblaster
Constraint Violating Yak Guru

380 Posts

Posted - 09/16/2011 :  11:21:03  Show Profile  Reply with Quote
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

USA
4 Posts

Posted - 09/16/2011 :  11:41:37  Show Profile  Reply with Quote
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

380 Posts

Posted - 09/16/2011 :  13:31:46  Show Profile  Reply with Quote
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

380 Posts

Posted - 09/16/2011 :  19:38:34  Show Profile  Reply with Quote
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:



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
Go to Top of Page

angtez
Starting Member

USA
4 Posts

Posted - 09/18/2011 :  15:12:55  Show Profile  Reply with Quote
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:



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


Go to Top of Page

flamblaster
Constraint Violating Yak Guru

380 Posts

Posted - 09/18/2011 :  16:34:19  Show Profile  Reply with Quote
Welcome :)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000