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
 General SQL Server Forums
 New to SQL Server Programming
 Missing Data
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

andygill
Starting Member

3 Posts

Posted - 09/25/2012 :  06:59:36  Show Profile  Reply with Quote
Hi All

Can anyone help me with some code (I’ve been struggling with it all day !!)

I have this table (clusters)

NHS_No Cluster_No startdate enddate
1234 6 01/04/12
5678 9 01/05/12 31/05/12
1234 7 01/06/12 13/06/12

If the enddate is empty I need it to be the nearest startdate of those records with a matching NHS_No.

So in this case it would be 01/06/12

Thanks for your help

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/25/2012 :  07:29:16  Show Profile  Reply with Quote
One way to approach it is by joining the table to itself 3 times. There may be simpler ways, but this is the only one that comes to my mind right now :)
;WITH cte AS
(
	SELECT *,
	ROW_NUMBER() OVER (PARTITION BY NHS_No ORDER BY StartDate) AS RN
	FROM YourTable
)
SELECT
	a.NHS_No,
	a.Cluster_no,
	a.StartDate,
	COALESCE(a.endDate,
		CASE 
			WHEN DATEDIFF(dd,COALESCE(b.startDate,'19000101'),a.startDate) >
				DATEDIFF(dd,a.startDate,COALESCE(c.startDate,'19000101')) THEN 
					b.startDate
			ELSE c.startDate
		END) AS EndDate
FROM
	cte a
	LEFT JOIN cte b ON b.RN+1 = a.RN
	LEFT JOIN cte c ON c.RN-1 = a.RN
ORDER BY
	a.NHS_No,
	a.ClusterNo,
	a.StartDate;
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 09/25/2012 :  07:37:08  Show Profile  Reply with Quote
SET DATEFORMAT DMY

DECLARE @Table TABLE (NHS_NO int,Cluster_No int,StartDate date, EndDate date)

INSERT INTO @Table
VALUES
(1234, 6,'01/04/12',null) ,
(5678, 9,'01/05/12','31/05/12'),
(1234, 7,'01/06/12','13/06/12')


UPDATE t
SET EndDate = tt.StartDate
FROM @Table t

CROSS APPLY(select top 1 StartDate
from @Table t2
where t.NHS_NO = t2.NHS_NO and t2.StartDate > t.StartDate
order by StartDate
)tt

SELECT * FROM @table


Jim

Everyday I learn something that somebody else already knew
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.06 seconds. Powered By: Snitz Forums 2000