September 21, 2012 at 7:44 am
Hi.
I'm new to SQL and on a step learning curve.
Can anyone help me with this.
I have a table (tbl_Cluster) with the following data
NHS_No Cluster_No Cluster_Start_Date Cluster_End_Date
1234 5 01/04/12
5567 6 30/05/12 06/06/12
1234 6 01/05/12 18/05/12
If there is no data in Cluster_End_date I need it to look for anyone matching NHS_No and choose the date that is closest after the first start date.
So in this example the missing end date would be 01/05/12 which is the nearest start date after the first one.
Hope this makes sense.
Thanks
Andy
September 21, 2012 at 8:05 am
There are a few ways to do this. I see that you are new around here and don't know about posting ddl (create table scripts) and sample data (insert statements) along with desired output based on your sample data. I created some for you but you should take a look at the first link in my signature for best practices when posting questions.
create table #Cluster
(
NHS_No int,
ClusterNum int,
StartDate datetime,
EndDate datetime
)
insert #Cluster
select 1234, 5, '20120104', null union all
select 5567, 6, '20120530', '20120606' union all
select 1234, 6, '20120501', '20120518'
select *, ISNULL(EndDate, (select MIN(EndDate) from #Cluster where NHS_No = c.NHS_No and EndDate is not null)) as NewEndDate
from #Cluster c
drop table #Cluster
I also took the liberty of removing the tbl_ prefix from the table in addition to taking off the current table name prefix for the columns. Prefixes are not generally considered a good idea because then you have column names changing when they are foreign keys in other tables.
Also, if what you posted is the entirety of your table I would strongly recommend you add a primary key to the table. From what you posted you don't have anything that can be a primary so perhaps an IDENTITY column would server this purpose well.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 21, 2012 at 9:02 am
Sean has a good solution, but I'm wondering, do you need to search on end date, and if that's not there, then search on the number?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply