Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

sql min and max time for multiple dates

Posted on 2016-09-26
15
Medium Priority
?
82 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 53

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 53

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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
LVL 30

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 30

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 70

Accepted Solution

by:
Scott Pletcher earned 2000 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 30

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 70

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 30

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 70

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 30

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 53

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 30

Expert Comment

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

Cheers !!
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
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 UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

670 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