Case statement on Date

Hi,
I have a problem solving this condition in designer.I already did serach on the BOBs but upon that the problem is not solved,ai appreciate any help.

We have a view ORDER_STATUS_V ,which is in universe ORDERS.
The end user wants the report to be GROUP BY each hour in a day and COUNT(PROCESSING MINUTES).
Actaully date_time format is “MM/DD/YY HH:MM:SS”

Originally if i query on report it looks like this:
DATE_TIME PROCESSING_MINUTES
07/29/2004 7:11:00 AM 3.52
07/29/2004 7:12:25 AM 5.0
07/30/2004 7:20:33 AM 8.9
07/30/2004 7:50:00 AM 3.9
08/01/2004 7:00:00 AM 15

But the user needs all the above date_time into just 7:00:00AM like this:
DATE_TIME PROCESSING_MINUTES >10mins <10mins
7:00AM 5 1 4

No matter what date it is but the hour od the date should be taken.

What i did is :
In order to take only time from the DATE_TIME,i gave like this in designer object select condition
substr(to_char((ORDER_STATUS_V.date_time)),12,11)

and gave the CASE statement in where clause:
substr(to_char((ORDER_STATUS_V.date_time)),12,11)=
(SELECT
CASE WHEN (substr(to_char((ORDER_STATUS_V.date_time)),12,11) >= ‘7:00:00 AM’ and substr(to_char((ORDER_STATUS_V.date_time)),12,11) < ‘8:00:00 AM’)
THEN COUNT(ORDER_STATUS_V.“PROCESSING MINUTES” ) END CASE

Thanks in Advance


randyj262004 (BOB member since 2004-04-09)

Can some one help me,i tried a lot but still no use
Thans


randyj262004 (BOB member since 2004-04-09)

Randy,

Using the Where Clause of an object is not a good idea at all. I’d try to avoid it if possible.
What I understood from your question was you want to group by Hour only. I can give you a suggestion and you can try it.
I’d convert the time to a 24 hr format first to aviod AM/PM.

The following part looks the problem to me

substr(to_char((ORDER_STATUS_V.date_time)),12,11) >= '7:00:00 AM' and substr(to_char((ORDER_STATUS_V.date_time)),12,11) < '8:00:00 AM') 

Once you would convert the the time into a 24 hr format( I believe there should be a format available in designer), create an object for Hour.
Using Substr and other String functions grab the hour digit(s) only from the date column.

If it is a single digit, append a ‘0’ to make all hours 2 digits.
You dont need the above code at all. Either the hour would be 07 or 08.
Display the processing minutes and the hour object in the report and do the count there.

I hope this helps.

kashif


Kashif Saeed :pakistan: (BOB member since 2004-06-02)

Wouldn’t a dimension object defined as follows give you what you need?

to_char(table.datefield, 'HH24')

It will return 00 - 23 to indicate the hour.


Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

Thanks for the input .
I already changed the statement to
to_char(order_status.date_time, HH:MM:SS AM’),
The reason to to take this format is, the end user needs the report to
7:00:00 AM
8:00:00 AM
9:00:00 AM
10:00:00 AM
11:00:00 AM




6:00:00 AM.
Does it make any difference or problem if i take that format?
Thanks


randyj262004 (BOB member since 2004-04-09)

to_char(trunc(table.datefield,'HH'), 'HH:MI:SS AM')

The trunc will “round down” to the whole hour, then the to_char will format the way you wanted. I recommend a two character hour (leading 0 if needed) to be able to sort properly.


Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

Hi,
I gave the statement in select clause of HOUR PDT object:
to_char(trunc(order_status_v.date_time,‘HH’), ‘HH:MI:SS AM’)

and i ran th query with PROCESSING MINUTES & HOUR PDT
it displyed :
10:00:00 AM’ 0.56
10:00:00 AM 1.5
10:00:00 AM 3
10:00:00 PM 8
10:00:00 PM 17
10:00:00 PM 22
11:00:00 AM 28
11:00:00 AM 7
11:00:00 PM 8.9
11:00:00 PM 22




What i did is i created a variable ‘BELOW 10’
and gave the formula as
=If (=“10:00:00 AM” And <Processing Time (min)> <10 )Then Count(<Processing Time (min)>) Else 0000
But it is giving me ‘Computation error’ and HOUR PDT is displayed
correctly
10:00:00 AM
10:00:00 PM
11:00:00 AM
11:00:00 PM

‘’
Do have any idea as why it is giving that error?
Thanks


randyj262004 (BOB member since 2004-04-09)

It still din’t solve the problem.
If this shouldnt be posted here,i’m sorry but as the continuation of the topic ,i posted it here.

Now the output i’m getting is

HOUR PDT 'ProcessingMinutes
7:00:00 AM '8.0
7:00:00 AM '3.2
7:00:00 AM '4.3
7:00:00 AM '1.2
7:00:00 PM '.89
7:00:00 PM '22
7:00:00 PM '15
8:00:00 AM '1
8:00:00 AM '8.0
8:00:00 PM '8.0
8:00:00 PM '21
9:00:00 AM '1.1
9:00:00 AM '11
9:00:00 AM '17
9:00:00 PM '1.6
9:00:00 Pm '11


Now i want the HOUR PDT and ‘ABOVE 10’ is the variable i created to calculate the count of processing minutes for the hour 7:00:00 AM …and processing minutes which are above 10mins.to be displayed as

HOUR PDT 'Above 10 'Below 10
7:00:00 AM '0 '4
7:00:00 PM '2 '1
8:00:00 AM '0 '2
8:00:00 PM '1 '1
9:00:00 AM '2 '0
9:00:00 PM '1 '1

I created a variable ‘Below 10’ but its diplaying

HOUR PDT 'ProcessingMinutes
7:00:00 AM '1
7:00:00 AM '1
7:00:00 AM '1
7:00:00 AM '0
7:00:00 PM '1
7:00:00 PM '0
7:00:00 PM '0
8:00:00 AM '1
8:00:00 AM '1
8:00:00 PM '1
8:00:00 PM '0
9:00:00 AM '1
9:00:00 AM '1
9:00:00 AM '1
9:00:00 PM '0
9:00:00 Pm '0
Can anyone ,please help me


randyj262004 (BOB member since 2004-04-09)