DateDiff for a column - SQL Server Forums

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)

DateDiff for a column

Author

Topic

sqlnewbie87
Starting Member

4 Posts

Posted - 2011-03-23 : 05:35:30

Hi all,

was wondering if I can get some help, I am relatively new to SQL and I am trying to solve the following problem and would appreciate your help.

I have a table with user IDs and transaction dates and I want to calculate a difference in between transactions for each user ID, see simplified example below:

UserID__________TransactionDate__________DifferenceDays
12345___________01/01/2000_______________Null
12345___________10/01/2000_______________9
12345___________15/01/2000_______________5
54321___________02/01/2000_______________Null
54321___________03/01/2000_______________1

I obviously know how to calculate this for just one column TransactionDate, thats easy, but how would I do that for each userID separately as I've done manually in the 3rd column DifferenceDays.

Hugely appreciate all the help I can get.

Many thanks,
Andrew

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-23 : 06:17:12

[code]
with cte as
(
select *, row_no = row_number() over (partition by UserID order by TransactionDate)
from yourtable
)
select c1.UserID, c1.TransactionDate,
DifferenceDays = datediff(day, c2.TransactionDate, c1.TransactionDate)
from cte c1
left join cte c2 on c1.UserID = c2.UserID
and c1.row_no = c2.row_no + 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-03-23 : 06:30:13

Try this -


CREATE TABLE #test ( UserID INT,TransactionDate DATETIME,DifferenceDays INT)

INSERT INTO #test 
SELECT 12345,'01/01/2000',NULL UNION ALL
SELECT 12345,'01/10/2000',NULL UNION ALL
SELECT 12345,'01/15/2000',NULL UNION ALL
SELECT 54321,'01/02/2000',NULL UNION ALL
SELECT 54321,'01/03/2000',NULL


UPDATE	T
SET		DifferenceDays = Diff
FROM	#test T
		INNER JOIN 
		(
			SELECT	T.UserID, TransactionDate, DATEDIFF(DAY, TDate, TransactionDate) Diff
			FROM	#test T
					INNER JOIN
					(
						SELECT		UserID, MIN(TransactionDate) TDate 
						FROM		#test
						GROUP BY	UserID
					) A ON T.UserID = A.UserID
		) A ON T.UserID = A.UserID AND T.TransactionDate = A.transactionDate
WHERE Diff <> 0

Vaibhav T

If I cant go back, I want to go fast...

sqlnewbie87
Starting Member

4 Posts

Posted - 2011-03-23 : 23:19:43

sorry what is 'cte'

quote:

Originally posted by khtan


with cte as
(
	select	*, row_no = row_number() over (partition by UserID order by TransactionDate)
	from	yourtable
)
select	c1.UserID, c1.TransactionDate, 
	DifferenceDays = datediff(day, c2.TransactionDate, c1.TransactionDate)
from	cte c1
	left join cte c2	on	c1.UserID	= c2.UserID
				and	c1.row_no	= c2.row_no + 1

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-23 : 23:54:19

CTE => Common Table Expressions
http://msdn.microsoft.com/en-us/library/ms190766.aspx

KH
[spoiler]Time is always against us[/spoiler]

sqlnewbie87
Starting Member

4 Posts

Posted - 2011-03-24 : 08:31:32

Many thanks, seems to work, now i need to work out how to do this for millions of records

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-03-24 : 08:57:46

Why would you want to do this to millions of rows?... and why would it be different anyway?

Corey

snSQL on previous signature "...Oh and by the way Seventhnight, your signature is so wrong! On so many levels, morally, gramatically and there is a typo!"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources