Finding turnover time between records

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 turnover time between records

Author

Topic

offspring22
Starting Member

38 Posts

Posted - 2012-02-10 : 17:03:23

Hello,

We have a table that has 25,000 or so records of sold properties over the last 20 years or so. Each property has a unique number we call a LINC number, and a solddate.

I have a request come my way here that needs me to find the average time it takes for sold property to be sold again, or it's turnover time. Some may have only sold once, some may have sold 10 times - in that case I would need it to include the turn over between each sale.

My data looks like:

linc SoldDate
0028497410 2002-06-07 00:00:00.000
0028497428 2003-06-17 00:00:00.000
0028498475 2002-07-29 00:00:00.000
0028498475 2003-02-26 00:00:00.000
0028498483 2002-06-15 00:00:00.000
0028498491 2008-12-01 00:00:00.000
0028498491 2010-11-14 00:00:00.000

Any tips on how to achieve this?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-02-10 : 17:25:03

This, maybe?

DECLARE	@Sample TABLE
	(
		LINC CHAR(10) NOT NULL,
		SoldDate DATETIME NOT NULL
	)

INSERT	@Sample
SELECT	'0028497410', '20020607' UNION ALL
SELECT	'0028497428', '20030617' UNION ALL
SELECT	'0028498475', '20020729' UNION ALL
SELECT	'0028498475', '20030226' UNION ALL
SELECT	'0028498483', '20020615' UNION ALL
SELECT	'0028498491', '20081201' UNION ALL
SELECT	'0028498491', '20101114'

-- SwePeso
SELECT		LINC,
		1E * DATEDIFF(DAY, MIN(SoldDate), MAX(SoldDate)) / (COUNT(*) - 1) AS Turnover
FROM		@Sample
GROUP BY	LINC
HAVING		COUNT(*) > 1
ORDER BY	LINC

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-02-10 : 17:28:46

What about the LINC's that has been sold only once?

-- SwePeso 2
SELECT		LINC,
		CASE
			WHEN COUNT(*) = 1 THEN DATEDIFF(DAY, MIN(SoldDate), GETDATE())
			ELSE 1E * DATEDIFF(DAY, MIN(SoldDate), MAX(SoldDate)) / (COUNT(*) - 1)
		END AS Turnover
FROM		@Sample
GROUP BY	LINC
ORDER BY	LINC

-- SwePeso 3
SELECT		LINC,
		CASE
			WHEN COUNT(*) = 1 THEN NULL
			ELSE 1E * DATEDIFF(DAY, MIN(SoldDate), MAX(SoldDate)) / (COUNT(*) - 1)
		END AS Turnover
FROM		@Sample
GROUP BY	LINC
ORDER BY	LINC

N 56°04'39.26"
E 12°55'05.63"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-02-10 : 17:31:04

[code];WITH CTE(linc, SoldDate, rn) AS (
SELECT linc, SoldDate, ROW_NUMBER() OVER (PARTITION BY linc ORDER BY SoldDate) FROM myTable)
SELECT a.linc, AVG(DATEDIFF(DAY,a.SoldDate,b.SoldDate)) days
FROM CTE A
INNER JOIN CTE B ON A.linc=B.linc AND A.rn=B.rn-1
GROUP BY a.linc[/code]

offspring22
Starting Member

38 Posts

Posted - 2012-02-10 : 18:00:08

Thanks SwePeso - that seems to have done it for me. One question though - what exactly does the 1E * your code do? Haven't the 1E * doesn't seem to make a difference.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-02-10 : 18:05:18

Rob, if you have 4 dates (in chronological order; a, b, c, d) the average of them is written


(b - a) + (c - b) + (d - c)
---------------------------
             3

which is what you have written in your code.
But if we expand the formula, we find that


(b - a) + (c - b) + (d - c)     b - a + c - b + d - c     d - a
---------------------------  =  ---------------------  =  -----
             3                            3                 3

So there is no need to self-join and calculate the intermediate steps.

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-02-10 : 18:07:38

quote:
Originally posted by offspring22

Thanks SwePeso - that seems to have done it for me. One question though - what exactly does the 1E * your code do? Haven't the 1E * doesn't seem to make a difference.

Yes it does, if the turnover contains decimals, such as 212.92 days or something.
With the 1E you can get a decimal number back, otherwise the result is an integer division and returns 212.

So it comes down to what precision you want for the result.

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources