find min, max time when start time is 6am

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 2005 Forums

Transact-SQL (2005)

find min, max time when start time is 6am

Author

Topic

onnorh
Starting Member

2 Posts

Posted - 2007-03-04 : 23:35:20

have a series of start and end time records. The problem is to select the min and max time from the series of records.

The following contraint applies. The start of broadcast time is 6:00 am. That is, minimum for start time is 6:00 am and maximum for end time is 5:59 am. So the following is illegal for start and end time, for example, 4:00 am - 6:30 am because it crosses the broadcast start time of 6:00 am.

Start End

10:00 pm ----- 2:00 am

6:30 am ----- 8:30 am

2:00 am ---- 3:45 am

11:00 am ---- 4:00pm

12:00 am ---- 3:40 am

You might be tempted to used -> Select MIN(Start), Max(End), but that will return 12:00 am - 4:00 pm, which is wrong, because sql server uses 12 midnight at the start time.

Can' t seem to come up with the tsql, please help

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-04 : 23:40:57

what is the table structure like ? and data type for the start & end ?

And what is your expected result for the sample data that you posted ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-04 : 23:58:44

This this what you want ?


declare @table table
(
	[start]	datetime,
	[end]	datetime
)

insert into @table
select	'10:00 PM', '02:00 AM'	union all
select	'06:30 AM', '08:30 AM'	union all
select	'02:00 AM', '03:45 AM'	union all
select	'11:00 AM', '04:00 PM'	union all
select	'12:00 AM', '03:40 AM'	

select	min_start = convert(varchar(10), min([start]), 108), 
	max_end   = convert(varchar(10), max([end]), 108)
from
(
	select	[start], [end] = case when [end] < '06:00' then [end] + 1 else [end] end
	from	@table
) t

/*
min_start  max_end    
---------- ---------- 
00:00:00   03:45:00
*/

onnorh
Starting Member

2 Posts

Posted - 2007-03-05 : 08:21:01

This is wrong. Expected result should be 6:30 am - 3:45am.

6:00 am is the smallest begin time 5:59 am is the max end time.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-05 : 08:29:27

[code]
declare @table table
(
[start] datetime,
[end] datetime
)

insert into @table
select '10:00 PM', '02:00 AM' union all
select '06:30 AM', '08:30 AM' union all
select '02:00 AM', '03:45 AM' union all
select '11:00 AM', '04:00 PM' union all
select '12:00 AM', '03:40 AM'

select min_start = convert(varchar(10), min([start]), 108),
max_end = convert(varchar(10), max([end]), 108)
from
(
select [start] = case when [start] >= '00:00' and [start] < '06:00' then [start] + 1 else [start] end,
[end] = case when [end] >= '00:00' and [end] < '06:00' then [end] + 1 else [end] end
from @table
) t

/*
min_start max_end
---------- ----------
06:30:00 03:45:00
*/
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources