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 2008 Forums
 Transact-SQL (2008)
 Count days between encounters without cursor
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

oasis1
Starting Member

34 Posts

Posted - 11/01/2013 :  12:10:46  Show Profile  Reply with Quote
Hi All, I was wondering how I could do the following without a cursor.

I have a results set that has patients with several different encounter dates I want to measure the time in days between each encounter like so with the last encounter being null: I was wondering if recursive CTE or cross apply maybe used here. Thanks for any help.

id contactdate daysbetween
1 01/01/2013 5
1 01/06/2013 8
1 01/14/2013 null
2 05/01/2013 9
2 05/10/2013 null
3 03/06/2013 null

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 11/01/2013 :  12:39:18  Show Profile  Reply with Quote
Here is one way:
;WITH cte AS
(
	SELECT
		id,
		contactdate,
		ROW_NUMBER() OVER (PARTITION BY id ORDER BY contactdate) AS RowNum
	FROM
		@Foo
)

SELECT 
	A.id,
	A.contactdate,
	DATEDIFF(DAY, A.contactdate, B.contactdate)
FROM 
	cte AS A
LEFT OUTER JOIN
	cte AS B
	ON A.id = B.id
	AND A.RowNum + 1 = B.RowNum
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/01/2013 :  12:41:49  Show Profile  Reply with Quote
Something like this
CTE method

;With CTE
AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY id ORDER BY contactdate) AS Rn,*
FROM table
)

SELECT c1.id,c1.contactdate,
DATEDIFF(dd,c1.contactdate,c2.contactdate) AS daysbetween
FROM CTE c1
LEFT JOIN CTE c2
ON c2.id = c1.id
AND c2.Rn = c1.Rn + 1



Apply method

SELECT t.id,t.contactdate,
DATEDIFF(dd,t.contactdate,t1.contactdate) AS daysbetween
FROM table t
OUTER APPLY (SELECT MIN(contactdate) AS nextdate
             FROM table
             WHERE id = t.id
             AND contactdate > t.contactdate
            )t1


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
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