Nearest time from every record

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)

Nearest time from every record

Author

Topic

Nowy
Yak Posting Veteran

57 Posts

Posted - 2007-06-01 : 05:23:17

How can I get from every record the nearest time? Where the column ( Reactor ) value is "test"

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-06-01 : 05:37:45

Yester day you told that you got the answer, isn't it?

please post some sample data and required output

--------------------------------------------------
S.Ahamed

Nowy
Yak Posting Veteran

57 Posts

Posted - 2007-06-01 : 05:40:17

no, it look likes the same but its not. Now I want the nearest time of every record, not of 1 record.

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-06-01 : 05:44:51

please post sample data and the output u want..i did not clear about 'every record, not 1 record'

--------------------------------------------------
S.Ahamed

Nowy
Yak Posting Veteran

57 Posts

Posted - 2007-06-01 : 05:52:04

In table Test there are about 200 records. Every record have an value SampleTime.

Now I want from every 200 records the nearest SampleTime.

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-06-01 : 06:04:01

with respect to what?,how can u get the nearest time of all the records...

if u want make others understatnd easily, always post sample data and required output u want!!

Please Post sample data

--------------------------------------------------
S.Ahamed

Nowy
Yak Posting Veteran

57 Posts

Posted - 2007-06-01 : 06:14:47

input

RecordID SampleTime
165079 21-Apr-2007 7:53:26
179918 22-Apr-2007 5:29:36
194758 24-Apr-2007 22:18:1
209591 23-Apr-2007 3:05:37
224384 24-Apr-2007 0:41:46

output
RecordID SampleTime
165079 22-Apr-2007 5:29:36
179918 21-Apr-2007 7:53:26
194758 24-Apr-2007 0:41:46
209591 24-Apr-2007 0:41:46
224384 24-Apr-2007 22:18:1

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-06-01 : 06:31:33

How you deciding the record 165079 's nearest time is 22-Apr-2007 5:29:36 when it has already
21-Apr-2007 7:53:26 as SampleTime

--------------------------------------------------
S.Ahamed

Nowy
Yak Posting Veteran

57 Posts

Posted - 2007-06-01 : 07:02:43

The reason is very complex, the question is how it is possible

bpgupta
Yak Posting Veteran

75 Posts

Posted - 2007-06-01 : 07:50:56

Please clarify the question with examples.

Nowy
Yak Posting Veteran

57 Posts

Posted - 2007-06-01 : 08:00:51

For example I want de nearest SampleTime of RecordID 165079 that is the SampleTime of RecordID 179918

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-06-01 : 08:44:25

select top 1 * from mytable a
inner joint mytableb
where a.sampletime >= b.sampletime
and b.id = 165079
order by a.sampletime

Nowy
Yak Posting Veteran

57 Posts

Posted - 2007-06-01 : 08:47:13

that's only for 1 record I want an query that do for all records

I have try something like this but doesn't work:
SELECT a.RecordID, a.SampleTime DATEDIFF(s, a.SampleTime, b.SampleTime )
FROM Test AS a , Test AS b
WHERE a.RecordID = b.RecordID

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-06-04 : 00:19:11

I think your output is not corect.. try this query
declare @t table(RecordID int, SampleTime datetime)
insert @t
select 165079, '21-Apr-2007 7:53:26' union all
select 179918, '22-Apr-2007 5:29:36' union all
select 194758, '24-Apr-2007 22:18:1' union all
select 209591, '23-Apr-2007 3:05:37' union all
select 224384, '24-Apr-2007 0:41:46'

Select RecordID, SAmpletime = (Select top 1 sampletime
from @t
where abs(datediff(s, sampletime, b.sampletime )) <> 0
order by abs(datediff(s, sampletime, b.sampletime )))
from @t b

and i am getting output as

165079 2007-04-22 05:29:36.000
179918 2007-04-23 03:05:37.000
194758 2007-04-24 00:41:46.000
209591 2007-04-22 05:29:36.000
224384 2007-04-23 03:05:37.000

--------------------------------------------------
S.Ahamed

Nowy
Yak Posting Veteran

57 Posts

Posted - 2007-06-06 : 03:47:03

Thnx, but could you explain what you exactly doing? So I can learn it

Nowy
Yak Posting Veteran

57 Posts

Posted - 2007-06-06 : 03:51:21

Because I have 1 extra Column "Reactor" with values from 1-64. I want the nearest "SampleTime" where the "Reactor" value = 1

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-06 : 03:58:42

Where is the column Reactor come from ? it was not in your original sample data ?
Please post your table DDL and sample data for all related columns.

Nowy
Yak Posting Veteran

57 Posts

Posted - 2007-06-06 : 04:04:07

I try this, but doesn't work

declare @t table(RecordID int, SampleTime datetime, Reactor char)
insert @t
select RecordID, SampleTime, Reactor From Test

Select RecordID, SAmpletime = (Select top 1 SampleTime, Reactor
from @t
where abs(datediff(s, sampletime, b.sampletime )) <> 0 and Reactor = 1
order by abs(datediff(s, sampletime, b.sampletime )))
from @t b

I get this error:
Server: Msg 116, Level 16, State 1, Line 5
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-06 : 04:05:37

Then please post your table DDL, sample data and the expected result.

Nowy
Yak Posting Veteran

57 Posts

Posted - 2007-06-06 : 04:17:48

RecordID SampleTime Reactor
256151 2007-04-26 21:18:58.000 1
327452 2007-05-10 19:21:19.000 27
327782 2007-05-10 19:49:27.000 59
328442 2007-05-10 20:17:38.000 60
329763 2007-05-10 22:10:16.000 1

output:
RecordID SampleTime Reactor
256151 2007-04-26 21:18:58.000 1
327452 2007-05-10 22:10:16.000 27
327782 2007-05-10 22:10:16.000 59
328442 2007-05-10 22:10:16.000 60
329763 2007-05-10 22:10:16.000 1

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-06 : 04:29:59

this ?


declare @table table
(
	RecordID 	int,
	SampleTime 	datetime,
	Reactor		int
)
insert into @table
select	256151, '2007-04-26 21:18:58.000',  1 union all
select	327452, '2007-05-10 19:21:19.000', 27 union all
select	327782, '2007-05-10 19:49:27.000', 59 union all
select	328442, '2007-05-10 20:17:38.000', 60 union all
select	329763, '2007-05-10 22:10:16.000',  1

select	t.RecordID, 
	t.SampleTime,
	New_SampleTime	= case 	when 	t.Reactor <> 1
				then	(
						select 	top 1 SampleTime 
						from 	@table x 	
						where 	x.RecordID 	<> t.RecordID 
						and	x.Reactor	= 1
						order by abs(datediff(second, x.SampleTime, t.SampleTime))
					)
				else	t.SampleTime	
				end,
	t.Reactor
from	@table t

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources