Solved

sql min and max time for multiple dates

Posted on 2016-09-26
15
64 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
  • 6
  • 3
  • 3
  • +1
15 Comments
 
LVL 50

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 50

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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 28

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 28

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 28

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 28

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 28

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 50

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 28

Expert Comment

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

Cheers !!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
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.

809 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