asked on

Latest time in half hour chunks

Hi
I have a table as per the following .......... SELECT CPY, DATE_TIME, VALUE FROM MYTABLE
I only want to return values on the hour and half hour exactly. However, if there are no hourly or half hourly exact times then I want the nearest value.
Original data set .........
Cpy Date_Time Value
A001 11/06/2017 16:00:00 25 -- on the hour so ok
A001 11/06/2017 16:15:00 45 -- don't need this one
A001 11/06/2017 16:29:00 14 -- need this one as nearest to half hour as there is no exact 16:30:00
A001 11/06/2017 16:45:00 56 -- don't need this one
A001 11/06/2017 17:00:00 7 -- on the exact hour so ok
A001 11/06/2017 17:30:00 8 -- on the exact half hour so ok
A001 11/06/2017 17:48:00 4 -- need this one as nearest to the hour
A001 11/06/2017 18:01:00 etc
,.................
So, this is what I would like to return ......
Cpy Date_Time Value
A001 11/06/2017 16:00:00 25 -- on the hour so ok
A001 11/06/2017 16:29:00 14 -- need this one as nearest to half hour as there is no exact 16:30:00
A001 11/06/2017 17:00:00 7 -- on the exact hour so ok
A001 11/06/2017 17:30:00 8 -- on the exact half hour so ok
A001 11/06/2017 17:48:00 4 -- need this one as nearest to the hour
B005 ETC

Regards

slightwv (䄆 Netminder)

Is this not the same as the other previous questions? I'm having a hard time figuring out what is different from this one and several of your previous ones.

What is different here and the one yesterday?
https://www.experts-exchange.com/questions/29029280/Fill-Missing-Values.html

PHIL Sawyer

ASKER

Hi
This is different as I want to remove values rather than add - I am currently trying a Row_Number () logic to try and work out the nearest time values to the hour and half hour.

Geert G

closing the previous question would help to know if someone is on the right track

the etc in the value column, poses somewhat of a problem

and does the solution of yesterday also have to fit this question ?

Geert G

if you are going for the nearest to the half hour
then you would be going for 11/06/2017 18:01:00
and not for 11/06/2017 17:48:00

A001 11/06/2017 17:48:00 4 -- need this one as nearest to the hour
A001 11/06/2017 18:01:00 etc

PHIL Sawyer

ASKER

Hi
Sorry for the confusion - when I say nearest to the Hour or half hour then I do not want to go past the hour or half hour, So the
A001 11/06/2017 17:48:00 is the nearest to 18:00:00

Regards

PHIL Sawyer

ASKER

Hi Geert
Just to let you know I left a comment on the other question.
Regards

ASKER CERTIFIED SOLUTION

Geert G

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PHIL Sawyer

ASKER

Thanks - great work