Exact Time stamp rollup

Hi,
Our Customer needs a universe desined which contqains the time stamps.

Like :
Hours
dt_date Created
application_id
Processing Time in Minutes
Day shift
Evening Shift
Evening Shift

Hours column should display the exact time from 7:00am to 12:ooam

The rport should look like this.
HOURS <10 >10 count
7:00am 7 13 20
8:00 am 4 11 15
9:00 am 3 15 18
10:00 am 7 20 27
11:00 am 6 11 17
12:00 pm 9 23 32
1:00 pm 8 11 19
2:00 pm 9 14 23
3:00 pm 2 11 13

My question is ,there are many time stamps like 7:00 am ,7:10 am,7:11 am…but how can i display or roll back all the time stamps between 7:00am to 8:00 am into 7:00am.
I’l be grateful if anyone can help me.
Thanks.


randyj262004 (BOB member since 2004-04-09)

Depending on how your hours are stored and your DBMS you might be able to use SubStr or CASE statement, such as:

Pseudocode
CASE WHEN Hours between 7:00:00 and 7:59:59 then 7:00:00


Andreas :de: (BOB member since 2002-06-20)

Thanks,should I give the predefined condition in the designer ?


randyj262004 (BOB member since 2004-04-09)

I can give like that but the problem is ,there are n munber of time stamps, this is only for day shift.
we have it for evening shift and night shift also.
So, for each and every time i should give the condition in CASE
Thanks


randyj262004 (BOB member since 2004-04-09)

Sketch:
Or use the SubString function to parse out the Hour part, then concatenate 00:00 minutes and seconds.


Andreas :de: (BOB member since 2002-06-20)

Thanks ,i’l apply one among them.


randyj262004 (BOB member since 2004-04-09)

Posted: Mon Jun 28, 2004 4:07 pm
Post subject: How to give the exact time stamps in CASE


Hi, can anyone tell me as how to roll up the time stamps using the CASE statement.
Because i tried it but its giving missing keyword.
I’m sure that i might be giving it worng.
Hours:
7:15:10
7:20:05:
7:30:00
7:31:50

these all should be rolled under
hour :
7:00:00
Any input is appreciated.
Thanks


randyj262004 (BOB member since 2004-04-09)

Please, post the SQL code you tried…


Andreas :de: (BOB member since 2002-06-20)