Calculating difference between two records in same column

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

SQL Server Development (2000)

Calculating difference between two records in same column

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-02-22 : 09:55:06

Carla writes "Hi
I have the following table:
TblPayments
Client RejectDate
002 05/12/2001
002 06/22/2001
002 07/25/2001
005 05/12/2001
005 08/15/2001

I need to see only those Clients that have a consecutive reject date (I must get the first date for the client (a), and get the next date (b), subtract (b)-(a), if the difference is less than 31 days ( 1 month) I need to know about this client

I there any way of doing it in sql?"

izaltsman
A custom title

1139 Posts

Posted - 2002-02-22 : 10:18:26

Do you mean something along these lines?


SELECT p1.client, DATEDIFF(dd, p1.RejectDate, p2.RejectDate) as No_days_btw_reject
FROM 
	tblPayments p1 
	INNER JOIN tblPayments p2
		ON p1.client = p2.client 
		   AND p1.RejectDate<p2.RejectDate
WHERE
		    DATEDIFF(dd, p1.RejectDate, p2.RejectDate)<31

Teroman
Posting Yak Master

115 Posts

Posted - 2002-02-22 : 10:19:01

There is, but its not obvious

try this

create table #TblPaymants( ID int identity(1,1), Client int, RejectDate datetime)

insert into #TblPaymants( Client, RejectDate)
select Client, RejectDate
from TblPaymants
order by Client, RejectDate

select A.Client, A.RejectDate, B.RejectDate
from #TblPaymants A
join #TblPaymants B
on A.Client = B.Client
and A.ID = B.ID - 1
and datediff(dd, A.RejectDate, B.RejectDate) < 31

that should do it, although you may want to change the date subtraction to do real months, i dont know

Teroman
Posting Yak Master

115 Posts

Posted - 2002-02-22 : 10:23:24

man, people round here post fast!

anyway, i dont think the first solution will work as it doesnt check the rejects are consecutive, if you had 1 a day for a year it would return loads of results which i dont think is wanted

or i have just missed the point.

I dont mind, only posted this in a seperate reply instead of an edit to see if my title changes after 50 posts ;-)

col

<edit>Which it did, woo hoo! Its a fine line between starter and veteran.</edit>

Edited by - teroman on 02/22/2002 10:24:21

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources