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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -