separate results hourly from a query

Question

Post reply

separate results hourly from a query

phanatico-1156717

Ten Centuries

Points: 1015
More actions
August 18, 2010 at 5:33 pm

#387611

I have a query that display a result beetween dates (mm/dd/yyyy hh24:mi:ss) but i need to separate those results hourly, here is the query:
select pass_fail_indicator
from unit_status
where station = 'LG01-TIDASSGN-03'
and pass_fail_indicator = 'P'
and modified_date
between to_date('08/18/2010 08:00:00','mm/dd/yyyy hh24:mi:ss')
and to_date('08/18/2010 15:00:00','mm/dd/yyyy hh24:mi:ss');

and i get the result beetwen those dates, but i need to separate the result of this query by hour,
for example if i choose start date '08/18/2010 00:00:00' and end date '08/18/2010 03:00:00' i need the result from '08/18/2010 00:00:00' to '08/18/2010 01:00:00' and from '08/18/2010 01:00:00' to '08/18/2010 02:00:00' etc...
it can be possible????
any help you can provide me I'll be very thankful

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Henry Treftz SSCarpal Tunnel Points: 4207 More actions · Answer 1

So you would want 08/19/2010 13:15 to show up as 08/19/2010 13:00 ?

jerry-621596 SSCertifiable Points: 6096 More actions · Answer 2

Try this:

select pass_fail_indicator, CONVERT(nvarchar(10), modified_date, 101), DATEPART(hour, modified_date)

from unit_status

where station = 'LG01-TIDASSGN-03'

and pass_fail_indicator = 'P'

and modified_date

between to_date('08/18/2010 08:00:00','mm/dd/yyyy hh24:mi:ss')

and to_date('08/18/2010 15:00:00','mm/dd/yyyy hh24:mi:ss')

ORDER BY modified_date, DATEPART(hour, modified_date);

Without your schema and some test data, I can't test this. But it should give you a starting point.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

select pass_fail_indicator
from unit_status
where station = 'LG01-TIDASSGN-03'
and pass_fail_indicator = 'P'
and modified_date
between to_date('08/18/2010 08:00:00','mm/dd/yyyy hh24:mi:ss')
and to_date('08/18/2010 15:00:00','mm/dd/yyyy hh24:mi:ss');

TO_DATE is an ORACLE function. Use a Tally table for this instead.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jerry-621596 SSCertifiable Points: 6096 More actions · Answer 4

Thanks! Didn't even notice that. Was simply taking what he had and adding the ideas to it.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

jerry-621596 (8/19/2010)
Thanks! Didn't even notice that. Was simply taking what he had and adding the ideas to it.

Sorry Jerry... didn't mean to direct that at you. Op had it in his original code. I changed my post.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 6

I'd post a coded answer except I can't start a new message with code in it. The filters at work pick it up as a possible SQL injection attack and prevent the post.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 7

Info on the Tally table may be found at the following URL. http://www.sqlservercentral.com/articles/T-SQL/62867/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)