Finding age between two dates of same order

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)

Finding age between two dates of same order

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-06-05 : 09:48:44

Sodi writes "Hi SQL Team,

I have following records

Serial#, partsname,FailDate,
CAOO1, WATCH-REP1,2003-07-29
CAOO1, WATCH-REP1, 2003-08-29
CAOO1, BATTERY,2003-08-29

i want the diffrence between those two dates which having same partsname..
could you please provide me the query.

thanks
Sodi"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-05 : 10:19:29

[code]select t1.FailDate, t2.FailDate
from tbl t1 inner join tbl t2
on t1.partsname = t2.partsname
and t1.FailDate <> t2.FailDate[/code]

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-05 : 11:14:29

Just to extend what khtan has written...

--data
declare @tbl table (Serial# varchar(10), partsname varchar(20), FailDate datetime) 
insert @tbl
          select 'CAOO1', 'WATCH-REP1', '20030729'
union all select 'CAOO1', 'WATCH-REP1', '20030829'
union all select 'CAOO1', 'BATTERY', '20030829'

--calculation
select t1.partsname, t1.FailDate, t2.FailDate,
    datediff(day, t1.FailDate, t2.FailDate) as DifferenceInDays
from   @tbl t1 inner join @tbl t2
on     t1.partsname = t2.partsname
and    t1.FailDate < t2.FailDate

/*results
partsname            FailDate    FailDate    DifferenceInDays 
-------------------- ----------- ----------- ---------------- 
WATCH-REP1           2003-07-29  2003-08-29  31
*/

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources