Calculating Difference between rows

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

Transact-SQL (2000)

Calculating Difference between rows

Author

Topic

Job
Yak Posting Veteran

69 Posts

Posted - 2006-02-09 : 13:14:47

I have a set of invoices that have different dates of service. I'm trying to get an avg number of days between invoices. Here is a set of data;
Dept,Div,DtofServ,ClientNum,CountofInv
Dept of Surgery,UROLOGY,10/7/2000,0001,1
Dept of Surgery,UROLOGY,12/15/2000,0001,1
Dept of Surgery,UROLOGY,5/18/1999,0001,1
Dept of Surgery,UROLOGY,9/15/1999,0001,1

What I'm trying to return would be each lines day diff if the data is in ascending order. Something like this.

Dept,Div,DtofServ,ClientNum,CountofInv,DayGap
Dept of Surgery,UROLOGY,5/18/1999,0001,1,NULL
Dept of Surgery,UROLOGY,9/15/1999,0001,1,120
Dept of Surgery,UROLOGY,10/7/2000,0001,1,388
Dept of Surgery,UROLOGY,12/15/2000,0001,1,69

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-09 : 13:35:15

will this do it?


set nocount on
create table #inv (clientNum int, Dt datetime)
insert #inv
select 1, '10/7/2000' union all
select 1, '12/15/2000' union all
select 1, '5/18/1999' union all
select 1, '9/15/1999'
go

select	clientNum
	,Dt
	,datediff(day, 
		(select max(Dt) from #inv where Dt < a.Dt and clientNum = a.ClientNum)
		, Dt)
from	#inv a
order by Dt

EDIT:
Output


clientNum   Dt                                                                 
----------- ------------------------------------------------------ ----------- 
1           1999-05-18 00:00:00.000                                NULL
1           1999-09-15 00:00:00.000                                120
1           2000-10-07 00:00:00.000                                388
1           2000-12-15 00:00:00.000                                69

Be One with the Optimizer
TG

Job
Yak Posting Veteran

69 Posts

Posted - 2006-02-09 : 14:15:34

Exactly. Thanks! I changed a little because i had a few other columns of data I was pulling, so the final query look like this;

SELECT InvGroupNme, InvDivNme,InvServDt,ClientNum, InvCount

,DysBtwnSrvc =DATEDIFF(DAY,
(SELECT MAX(InvServDt) FROM tmp_DaysBetweenSrvce WHERE InvServDt < a.InvServDt and
ClientNum= a.ClientNum and InvDivNme = a.InvDivNme)
, InvServDt)
FROM tmp_DaysBetweenSrvce a
ORDER BY InvDivNme,ClientNum,InvServDt

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources