Finding the longest time between dates....

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 2005 Forums

Transact-SQL (2005)

Finding the longest time between dates....

Author

Topic

offspring22
Starting Member

38 Posts

Posted - 2012-07-05 : 18:24:40

Good day everyone,

I have a situation arise that I haven't come across before...

I need to create some statistics for clients who have taken courses with us. I have a table that has a record for each course a customer has taken. For example, the registration table looks as follows:

Course | CustNo | Date
class1 | 1234 | 12/3/2005
class1 | 3456 | 2/5/2007
class2 | 7890 | 4/27/2009
class3 | 1234 | 11/8/2006
class4 | 3456 | 1/9/2011
class5 | 7890 | 7/27/2005
class4 | 1234 | 11/3/2010
class9 | 3456 | 12/5/2010
class4 | 7890 | 8/26/2008

I need to find the longest time each CustNo went without taking a course. Ie, for CustNo 1234 the longest time they went was 11/8/2006 - 11/3/2010, or about 4 years.... There can be 50 courses for 1 particular CustNO.

Any idea on where to start with this?

Thanks in advance!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-05 : 21:59:00

Here is one approach:

SELECT
	a.CustNo,
	MAX(DATEDIFF(dd,a.Date,COALESCE(b.Date,GETDATE()))) AS MaxDays
FROM
	YourTable a
	OUTER APPLY
	(
		SELECT TOP (1) b.Date
		FROM YourTable b
		WHERE b.CustNo = a.CustNo
		AND b.Date > a.Date
		ORDER BY b.Date
	)b
GROUP BY
	a.CustNo;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-05 : 23:05:09

[code]
;With CTE
AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY CustNo ORDER BY Date) AS Rn,*
FROM Table
)

SELECT CustNo,Duration
FROM
(
SELECT c1.CustNo,ROW_NUMBER() OVER (PARTITION BY CustNo ORDER BY DATEDIFF(dd,c1.Date,c2.Date) DESC) AS Seq, DATEDIFF(dd,c1.Date,c2.Date) AS Duration
FROM CTE c1
INNER JOIN CTE c2
ON c2.CustNo = c1.CustNo
AND c2.Rn = c1.Rn -1
)t
WHERE Seq=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-06 : 07:34:51

Visakh, are you trying to confuse me? I switched from CTE's to OUTER APPLY (after much kicking and screaming, I might add) after learning the OUTER APPLY from a "Very Important crosS Applying yaK Herder". Are you now going back on me and reverting to CTE's?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-06 : 09:37:16

quote:
Originally posted by sunitabeck

Visakh, are you trying to confuse me? I switched from CTE's to OUTER APPLY (after much kicking and screaming, I might add) after learning the OUTER APPLY from a "Very Important crosS Applying yaK Herder". Are you now going back on me and reverting to CTE's?

I just played myself as Sunita here

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources