how to select the earliest time aross a number of fields for each line

Question

Hi, I need to select the earliest time across a number of time fields for each incident. Please see below the table. The last column 'recalculated' is the field I want to populate and value should be the earliest one from timestampe 1 to 4.

INCIDENT_NUMBER	timestamp1	timestamp2	timestamp3	timestamp4	recalculated (the earliest of timestampe 1 to 4)
1	02:29.0	03:29.0	03:37.0	02:38.0	02:38.0
2	NULL	NULL	06:22.0	03:24.0	03:24.0
3	08:40.0	09:40.0	NULL	08:48.0	08:48.0
4	09:59.0	10:59.0	11:10.0	10:05.0	10:05.0
5	10:02.0	11:02.0	NULL	10:29.0	10:29.0
6	11:40.0	12:40.0	13:19.0	12:15.0	12:15.0
7	13:27.0	14:27.0	14:48.0	14:16.0	14:16.0
8	14:59.0	15:59.0	15:29.0	15:10.0	15:10.0
9	NULL	NULL	NULL	25:56.0	25:56.0
10	17:36.0	18:36.0	20:03.0	19:11.0	18:36.0
11	19:46.0	20:46.0	NULL	19:57.0	19:46.0
12	19:53.0	20:53.0	21:30.0	20:35.0	20:35.0

I tried to use MIN(Timestampe1, timestampe2, timestampe3, timestampe4), but it did not work.

Answer 1

Hi,

So try select the minimum number of the last column

SELECT INCIDENT_NUMBER, MIN(recalculated)
FROM TableName

I hope this is helpful.

 

Elmozamil Elamir

MyBlog

---

Please Mark it as Answered if it answered your question
OR mark it as Helpful if it help you to solve your problem
Elmozamil Elamir Hamid
http://elmozamil.blogspot.com

Answer 2

Hi, the recalculated field is the one I want to populate, but i can not get it auto populated using

select MIN(timestample1, timestample2, timestample3, timestample4) as 'recalculated'

from incident_table

where .......

 

Answer 3

MIN works on 1 column.  So you can simply unpivot the columns and do a min on the union of them like this.  I've included the UNION ALL subquery approach.  As well as the UNPIVOT approach.  Hope this helps. 

One note.  I don't follow some of your sample recalculated times.  Several aren't actually the earliest times.  I'm using a straight MIN in the queries below.  If you have a different logic in mind, please post additional details.

 

use tempdb
go

create table test
(
	INCIDENT_NUMBER int
	,timestamp1 time
	,timestamp2 time
	,timestamp3 time
	,timestamp4 time
)

--truncate table test
insert test (INCIDENT_NUMBER,timestamp1,timestamp2,timestamp3,timestamp4)
values (1,'02:29','03:29','03:37','02:38')
,(2,NULL,NULL,'06:22','03:24')
,(3,'08:40','09:40',NULL,'08:48')
,(4,'09:59','10:59','11:10','10:05')
,(5,'10:02','11:02',NULL,'10:29')
,(6,'11:40','12:40','13:19','12:15')
,(7,'13:27','14:27','14:48','14:16')
,(8,'14:59','15:59','15:29','15:10')
,(9,NULL,NULL,NULL,'21:56')
,(10,'17:36','18:36','20:03','19:11')
,(11,'19:46','20:46',NULL,'19:57')
,(12,'19:53','20:53','21:30','20:35')

--
select * from test

--
select 
	incident_number,min(timestampX)
from
(
	select incident_number,timestamp1 as timestampX from test
	UNION ALL
	select incident_number,timestamp2 from test
	UNION ALL
	select incident_number,timestamp3 from test
	UNION ALL
	select incident_number,timestamp4 from test
	--order by 1
)x
group by INCIDENT_NUMBER


--------------
--Unpivot 
select incident_number,min(timestampX) as MinTime
from
(
SELECT incident_number, timeSlot, timeStampX
FROM 
   (SELECT incident_number, timestamp1, timestamp2, timestamp3, timestamp4
   FROM test) p
UNPIVOT
   (timeStampX FOR timeslot IN 
      (timestamp1, timestamp2, timestamp3, timestamp4)
)AS unpvt
)x
group by INCIDENT_NUMBER

 

---

Brent Greenwood, MS, MCITP, CBIP
// Please mark correct answers and helpful posts //
http://brentgreenwood.blogspot.com

Answer 4

Hi Brent

Thank you for your help. Yes you are right. There is logic I did not put down correctly. The timestampe 1, 3 and 4 are original data (generated as the incidents develope). Timestampe 2 is timestampe 1 plus 60 seconds. What I want SQL to do is to give me the earliest timestampe from Timestampe 2, 3 and 4. and put in the recalculated column.

The number of incidents can reach 900 a day. To do above insert seems to be quite a task.

 

Answer 5

Ignore the create table and the inserts.  I included those so you could run the queries against the same test data I pulled together to test the queries. 

What you need is one of the select statements at the end.  And just leave out the references to timestamp1 if you don't want that included in the logic.

Let me know if that helps.

---

Brent Greenwood, MS, MCITP, CBIP
// Please mark correct answers and helpful posts //
http://brentgreenwood.blogspot.com

Answer 6

; with cte as (

SELECT *

,      (   SELECT  min(val) 

  FROM    (VALUES (timestamp2)

  ,   (timestamp3)

  ,   (timestamp4)

  ) AS value(val)

  ) AS MinVal 

FROM TableName

)

update cte set timestamp5 = MinVal

Answer 7

with [MinTime] as (
select INCIDENT_NUMBER, MIN(MinTimeStamp)  as MinTimeStamp
from (
 select * from MyTable
 unpivot (MinTimeStamp for srcCol in (timestamp1,timestamp2,timestamp3,timestamp4)) as upv
) t
group by INCIDENT_NUMBER
)

select MyTable.*,[MinTime].MinTimeStamp as [Recalculated] 
from [MinTime]
join MyTable on MyTable.INCIDENT_NUMBER = [MinTime].INCIDENT_NUMBER