Solved

sql min and max time for multiple dates

Posted on 2016-09-26
15
77 Views
Last Modified: 2016-11-08
I have a table with three columns, Date, value and time
I need to select the min and max time for each date so I end up with two records for each date.
from the sample below, I'd like to get the result to look like:
2016-09-20 17:48:23.447      63      17:48
2016-09-20 12:37:04.723      96      12:37

Sample data
2016-09-20 17:48:23.447      63      17:48
2016-09-20 17:38:08.880      64      17:38
2016-09-20 17:32:03.247      65      17:32
2016-09-20 17:16:23.273      68      17:16
2016-09-20 17:06:27.223      69      17:06
2016-09-20 16:58:27.693      70      16:58
2016-09-20 16:46:27.030      71      16:46
2016-09-20 16:38:19.490      72      16:38
2016-09-20 16:27:07.743      74      16:27
2016-09-20 16:22:19.243      75      16:22
2016-09-20 16:08:24.197      77      16:08
2016-09-20 15:57:02.757      79      15:57
2016-09-20 15:47:23.227      80      15:47
2016-09-20 15:38:23.937      77      15:38
2016-09-20 15:26:22.017      73      15:26
2016-09-20 15:17:23.277      73      15:17
2016-09-20 15:06:36.990      74      15:06
2016-09-20 14:56:42.550      76      14:56
2016-09-20 14:47:07.823      77      14:47
2016-09-20 14:37:23.907      79      14:37
2016-09-20 14:27:38.387      81      14:27
2016-09-20 14:17:23.307      83      14:17
2016-09-20 14:07:23.687      85      14:07
2016-09-20 13:57:08.230      87      13:57
2016-09-20 13:47:23.307      88      13:47
2016-09-20 13:37:23.463      89      13:37
2016-09-20 13:27:44.067      90      13:27
2016-09-20 13:16:41.130      91      13:16
2016-09-20 13:06:22.003      92      13:06
2016-09-20 13:01:33.437      93      13:01
2016-09-20 12:47:23.217      95      12:47
2016-09-20 12:37:04.723      96      12:37
0
Comment
Question by:sleiman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 3
  • +1
15 Comments
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 41817001
try :

select date , value, time from urtable
where date in (select max(date) from urtable)
union
select date , value, time from urtable
where date in (select min(date) from urtable)
order by 1
0
 

Author Comment

by:sleiman
ID: 41817013
I need to get a min and max for each date.
I have multiple dates.
I also have other selection criteria.

If I add other columns in the select, I get no records.
0
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 41817086
probably you can try this instead:
select a.date , a.value, a.time from urtable a
left join (select cast(date as date) d, max(date) date from urtable group by cast(date as date) ) b
on cast(a.date as date) = b.d 
where a.date = b.date
union
select a.date , a.value, a.time from urtable a
left join (select cast(date as date) d, min(date) date from urtable group by cast(date as date) ) b
on cast(a.date as date) = b.d 
where a.date = b.date
order by 1 desc

Open in new window

0
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41817101
Complete code for you :)

--

CREATE TABLE testDate
(
	 [Date] DATETIME
	,[value] INT
	,[time] TIME
)
GO

INSERT INTO testDate VALUES
('2016-09-20 17:48:23.447',      63      ,'17:48'),
('2016-09-20 17:38:08.880',      64      ,'17:38'),
('2016-09-20 17:32:03.247',      65      ,'17:32'),
('2016-09-20 17:16:23.273',      68      ,'17:16'),
('2016-09-20 17:06:27.223',      69      ,'17:06'),
('2016-09-20 16:58:27.693',      70      ,'16:58'),
('2016-09-20 16:46:27.030',      71      ,'16:46'),
('2016-09-20 16:38:19.490',      72      ,'16:38'),
('2016-09-20 16:27:07.743',      74      ,'16:27'),
('2016-09-20 16:22:19.243',      75      ,'16:22'),
('2016-09-20 16:08:24.197',      77      ,'16:08'),
('2016-09-20 15:57:02.757',      79      ,'15:57'),
('2016-09-20 15:47:23.227',      80      ,'15:47'),
('2016-09-20 15:38:23.937',      77      ,'15:38'),
('2016-09-20 15:26:22.017',      73      ,'15:26'),
('2016-09-20 15:17:23.277',      73      ,'15:17'),
('2016-09-20 15:06:36.990',      74      ,'15:06'),
('2016-09-20 14:56:42.550',      76      ,'14:56'),
('2016-09-20 14:47:07.823',      77      ,'14:47'),
('2016-09-20 14:37:23.907',      79      ,'14:37'),
('2016-09-20 14:27:38.387',      81      ,'14:27'),
('2016-09-20 14:17:23.307',      83      ,'14:17'),
('2016-09-20 14:07:23.687',      85      ,'14:07'),
('2016-09-20 13:57:08.230',      87      ,'13:57'),
('2016-09-20 13:47:23.307',      88      ,'13:47'),
('2016-09-20 13:37:23.463',      89      ,'13:37'),
('2016-09-20 13:27:44.067',      90      ,'13:27'),
('2016-09-20 13:16:41.130',      91      ,'13:16'),
('2016-09-20 13:06:22.003',      92      ,'13:06'),
('2016-09-20 13:01:33.437',      93      ,'13:01'),
('2016-09-20 12:47:23.217',      95      ,'12:47'),
('2016-09-20 12:37:04.723',      96      ,'12:37')
GO

SELECT * FROM
(
	SELECT DISTINCT CAST(p.[Date] as DATE) [Date] FROM testDate p
)p
CROSS APPLY
(
	SELECT MIN([Date]) MinDate , MAX([Date]) MaxDate FROM testDate t
	WHERE CAST(t.[DATE] AS DATE) = ((p.[DATE]))
)r
CROSS APPLY
(
	SELECT [value],[Time] FROM testDate t
	WHERE (t.[Date] = r.MinDate OR  t.[Date] = r.MaxDate)
)s


--

Open in new window



Enjoy !!
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41817102
--

SELECT * FROM
(
	SELECT DISTINCT CAST(p.[Date] as DATE) [Date] FROM testDate p
)p
CROSS APPLY
(
	SELECT MIN([Date]) MinDate , MAX([Date]) MaxDate FROM testDate t
	WHERE CAST(t.[DATE] AS DATE) = ((p.[DATE]))
)r
CROSS APPLY
(
	SELECT [value],[Time] FROM testDate t
	WHERE (t.[Date] = r.MinDate OR  t.[Date] = r.MaxDate)
)s


--

Open in new window

0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 41818944
SELECT date, value, time
FROM (
    SELECT *,
        ROW_NUMBER() OVER(PARTITION BY CAST(Date AS date) ORDER BY time) AS min_time,
        ROW_NUMBER() OVER(PARTITION BY CAST(Date AS date) ORDER BY time DESC) AS max_time
    FROM testDate
) AS derived
WHERE min_time = 1 OR max_time = 1
ORDER BY date, time
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41873588
Sorry but I disagree. !! Other experts has already solved the question perfectly a day before the last comment was added.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 41874640
@Pawan:

Your code does three scans of the table, my code does one.  That large of a performance difference should be taken into account: the larger the table, the more important it is, of course.
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41876023
@ScottPletcher

Who says that scans are bad for performance. ?  Performance depends on N number of factors.

ALSO - Why you are considering the performance - It was not asked by the author. I and Ryan solved it before you. Don't you think its unfair the our solution are just ignored without any reason.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 41877176
If you were being fair, then wouldn't have pushed for Ryan to get the points, since he answered first?

I think it's fair to allow the asker to determine the best solution, not just accept the first answer.  That is what is of most value to anyone who would look at the q later.  For example, even if performance was not a major factor in OP's q -- although performance should always be considered, other things being equal -- maybe the next person to use this code has a really large table and can't afford 3 full scans of the table versus just one.
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41877199
I am fine if Ryan get all the points since Ryan solved it first.

Are your fine with this ?

Regards,
Pawan
0
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 41878039
Hi all,

well...

performance could be an issue for certain scenarios and what Scott suggested (and reviewed by angelIII) could be best suitable for this particular situation. of course, what I and Pawan proposed did resolved the issue.

since no reply from asker what solution he/she preferred,  I'm fine without having a single point. no worries for that cheers
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41878045
Okies, Lets close this. I'm also fine without having a single point.

Cheers !!
0

Featured Post

Want Experts Exchange at your fingertips?

With Experts Exchange’s latest app release, you can now experience our most recent features, updates, and the same community interface while on-the-go. Download our latest app release at the Android or Apple stores today!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question