Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

oasis1
Starting Member

35 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
52326 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  
 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.04 seconds. Powered By: Snitz Forums 2000