Calculated field value with last record value

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)

Calculated field value with last record value

Author

Topic

geossl
Yak Posting Veteran

85 Posts

Posted - 2004-06-02 : 23:51:26

Dear All,
There is a table:
Dept:
Time:
Date:

Can we calculate the elapse time with the previous record in a single SQL?

The result should be:

DeptA 3 days
DeptB 4 days...

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-06-03 : 00:14:10

Probably not the best way to do it, but here goes:

select a.dept, a.date, datediff(d, (select top 1 b.date from testtable b where b.dept < a.dept order by b.dept desc), a.ddate) as DaysDiff
from testtable a

Tim

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-06-03 : 00:14:25

One way is to create a user defined function to calculate the difference in days:

CREATE FUNCTION f_elapsed(@dept varchar(30), @depttime datetime) RETURNS int
AS
BEGIN
RETURN (
select IsNull(datediff(dd,max(depttime),@depttime),0) from depttable where depttime < @depttime and dept = @dept
)
END

Then you can use:

select dept, convert(varchar,dbo.f_elapsed(dept,depttime)) + ' days' Elapsed
from depttable

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources