Count days between encounters without cursor

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Count days between encounters without cursor

Author

Topic

oasis1
Starting Member

35 Posts

Posted - 2013-11-01 : 12:10:46

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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-11-01 : 12:39:18

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-01 : 12:41:49

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources