Solved

sql min and max time for multiple dates

Posted on 2016-09-26
15
56 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SSRS 2012 r2 - Parm Drop Down has Date/Time 12 30
Linked Server Issue with SQL2012 3 22
Return 0 on SQL count 24 28
SQL View nearest date 5 34
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

816 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now