Solved

sql min and max time for multiple dates

Posted on 2016-09-26
15
48 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 49

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 49

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
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
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 18

Expert Comment

by:Pawan Kumar Khowal
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:
ScottPletcher 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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
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:ScottPletcher
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 18

Expert Comment

by:Pawan Kumar Khowal
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:ScottPletcher
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 18

Expert Comment

by:Pawan Kumar Khowal
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 49

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 18

Expert Comment

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

Cheers !!
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In this article I will describe the Detach & Attach 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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

746 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

10 Experts available now in Live!

Get 1:1 Help Now