DateDiff question - 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

General SQL Server Forums

New to SQL Server Programming

DateDiff question

Author

Topic

drymnfr
Starting Member

9 Posts

Posted - 2010-04-20 : 13:03:39

I have the following structure and data example:


CustomerID      DatePurchased           ItemID          Quantity
1         	1/1/2007 12:00:00 AM	123       	1
1         	1/1/2008 12:00:00 AM	124       	2
1         	1/1/2009 12:00:00 AM	125       	3
2         	12/1/2007 12:00:00 AM	123       	1
2         	1/1/2008 12:00:00 AM	124       	2
2         	12/2/2008 12:00:00 AM	125       	3
2         	1/1/2009 12:00:00 AM	126       	4

I would like to know, If I can use DateDiff to calculate the date difference between the last two transactions a customer made.
For example: for Customer # 2, the last two transactions were on 12/2/2008 and 1/1/2009 and the difference is 30 days.

Thanks in advance for your help.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-20 : 13:43:08

Just to start with:

Create table #TestData
(
CustomerId int,
DatePurchased datetime)

Insert into #TestData
Select 2,'2007-01-12' union
Select 2,'2008-01-01' union
Select 2,'2008-12-02' union
Select 2,'2009-01-01'

Select TD.CustomerId, Datediff(dd, St.DatePurchased,max(TD.DatePurchased))
from #TestData TD
inner join
(Select Rank() over (Partition by CustomerId order by DatePurchased desc) as Rank,
CustomerId,DatePurchased from #TestData
) St
On Td.CustomerId = St.CustomerId
and ST.Rank = 2
group by TD.CustomerId, St.DatePurchased

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

drymnfr
Starting Member

9 Posts

Posted - 2010-04-20 : 13:54:39

Wow! Works great. Thanks again.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-20 : 14:12:33

You are welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources