help with date calculation - 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

SQL Server 2000 Forums

Transact-SQL (2000)

help with date calculation

Author

Topic

sign_seventh
Yak Posting Veteran

66 Posts

Posted - 2006-06-12 : 23:03:06

This is my select statement

select JBS_FIRST_NAME, JBS_RECORD_ADDED FROM JBS_JOBSEEKERS

THIS IS THE OUTPUT:
JBS_FIRSTNAME___|___JBS_RECORD_ADDED
----------------|----------------
JOSE MICHAEL____|__2006-06-09 08:53AM
MICHAEL FRANCIS_|__2006-06-09 09:13AM
CHARINA_________|__2006-06-09 09:21AM
ARCHILO_________|__2006-06-09 10:17AM
---------------------------------

BASED ON THE OUTPUT RECORD ABOVE, I WOULD LIKE TO
CREATE A QUERY THAT WOULD COMPUTE HOW LONG
EVERY RECORD IS ENCODED

I WOULD LIKE TO GET THIS OUTPUT USING THE RECORD ABOVE:

JBS_FIRSTNAME___|___JBS_RECORD_TIME
----------------|------------------
JOSE MICHAEL____|___20 mins._________(09:13AM - 08:53AM)
MICHAEL FRANCIS_|___8 mins._________(09:21AM - 09:13AM)
CHARINA_________|___56 mins._________(10:17AM - 09:21AM

I WILL APPRECIATE YOUR HELP.
THNX

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-13 : 02:49:32

[code]-- Populate test data
declare @test table (FirstName varchar(50), Record datetime)

insert @test
select 'JOSE MICHAEL', '2006-06-09 08:53AM' union all
select 'MICHAEL FRANCIS', '2006-06-09 09:13AM' union all
select 'CHARINA', '2006-06-09 09:21AM' union all
select 'ARCHILO', '2006-06-09 10:17AM'

-- Do the work
SELECT FirstName 'First name',
CONVERT(VARCHAR, DATEDIFF(mi, t.Record, (SELECT MIN(Record) FROM @Test z WHERE z.Record > t.Record))) + ' mins' 'Record time',
RIGHT('0' + LTRIM(RIGHT(CONVERT(VARCHAR, (SELECT MIN(Record) FROM @Test z WHERE z.Record > t.Record), 100), 7)),7) + ' - ' + RIGHT('0' + LTRIM(RIGHT(CONVERT(VARCHAR, Record, 100), 7)),7) 'Duration'
FROM @Test t
WHERE (SELECT MIN(Record) FROM @Test z WHERE z.Record > t.Record) IS NOT NULL[/code]

Peter Larsson
Helsingborg, Sweden

sign_seventh
Yak Posting Veteran

66 Posts

Posted - 2006-06-13 : 03:44:02

ei.. thnx so much for the information

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-13 : 06:51:43

sign_seventh - If it's important to you, you can make Peso's method a bit quicker like this...

--data
declare @test table (FirstName varchar(50), Record datetime)
insert @test
          select 'JOSE MICHAEL', '2006-06-09 08:53AM'
union all select 'MICHAEL FRANCIS', '2006-06-09 09:13AM'
union all select 'CHARINA', '2006-06-09 09:21AM'
union all select 'ARCHILO', '2006-06-09 10:17AM'

--calculation
select FirstName as 'First name',
	cast(DATEDIFF(mi, Record, ToDate) as varchar(10)) + ' mins' as 'Record time',
	RIGHT('0' + LTRIM(RIGHT(CONVERT(VARCHAR, ToDate, 100), 7)),7) + ' - ' + RIGHT('0' + LTRIM(RIGHT(CONVERT(VARCHAR, Record, 100), 7)),7) as 'Duration'
from (select *, (SELECT MIN(Record) FROM @Test WHERE Record > t.Record) as ToDate from @test t) a
where ToDate is not null

/*results
First name                                         Record time     Duration          
-------------------------------------------------- --------------- ----------------- 
JOSE MICHAEL                                       20 mins         09:13AM - 08:53AM
MICHAEL FRANCIS                                    8 mins          09:21AM - 09:13AM
CHARINA                                            56 mins         10:17AM - 09:21AM
*/

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