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.
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_date123456 2011-01-17 00:00:00.000123456 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_date123456 2011-01-17 00:00:00.000 2011-02-10 00:00:00.000123456 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_dateFROM ( 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) dWHERE next_visit_date IS NOT NULL HTH.EDIT... replaced table name with OP's table name. |
|
|
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? |
|
|
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. |
|
|
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? |
|
|
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 datetimeinsert 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 NextVisitDateFROM (select MemberId, VisitDate, ROW_NUMBER() over (Partition by MemberId Order by VisitDate) as Seq from @member_dates) AJOIN (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] |
|
|
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 datetimeinsert 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 NextVisitDateFROM (select MemberId, VisitDate, ROW_NUMBER() over (Partition by MemberId Order by VisitDate) as Seq from @member_dates) AJOIN (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]
|
|
|
flamblaster
Constraint Violating Yak Guru
384 Posts |
Posted - 2011-09-18 : 16:34:19
|
Welcome :) |
|
|
|
|
|
|
|