how to use group by case in powerbuilder 10.5

Question

date_entry  time_start  time_finished idle_code    qty_good
8/8/2013    13:00       13:30            6            10     
8/8/2013    13:30       15:20            0            20
8/8/2013    15:20       15:30            6            5 
8/8/2013    15:30       16:25            0            10
8/8/2013    16:25       16:40            7            0
8/8/2013    16:40       17:25            0            40
8/8/2013    17:25       17:40            3            10
8/8/2013    17:40       24:00            1
8/8/2013    24:00       00:00            1
8/8/2013    00:00       00:30            1

Idle Time Legend:

0   Production  
1   Adjustment/Mold 
2   Machine 
3   Quality Matter  
4   Supply Matter   
5   Mold Change 
6   Replacer    
7   Others

----------Result-------------------------------------- total mins

idle_code total mins 
1 -       410:00 mins
2 -        00:00
3 -        15:00
4 -        00:00
5 -        00:00
6 -        40:00
7 -        15:00 
0 -       210:00

First question how to group by idle_code and add the total mins.?

---------other report---------------------------------- production efficientcy report

idle_code         total mins
    1             410:00 mins
    2             00:00 mins
    3             15:00 mins
    4             00:00 mins
    5             00:00 mins
    7             15:00 mins

total idle time = 440:00 mins (formula: sum(total mins of idle 1,2,3,4,5,7))

idle rate = 63.77% (formula: (total idle time / total actual production time)* 100 )

total operation time = 250:00 mins (formula sum(idl_code '0' and idle_code '6'))

machine efficienct = 36.23% (formula (total operation time / total actual production time * 100))

total actual production time = 690:00 mins (formula sum(total_idle_time + total operation time))

this is easy to compute in the powerbuilder using computed field but my problem is how to group them by idle_code and there total mins.

score 0 · Answer 1 · edited Aug 05 '14 at 14:10

You could do this as a single SQL statement, summing the difference between the start and finish times, and grouping on idle_code. (Don't forget to make this a Left Outer Join from the Idle_Code table to the Production data table). This would save you from retrieving all the detail data to the client, and doing the grouping and summing there.

If you need to do this as a computed column, and you've retrieved all the detail data, then create a group on idle_code, and create a computed column that sums (time_finished - time_start for group 1). The SecondsAfter() function can do this, if those columns are datetimes and not just time values.

How are you storing your time_start and time_finished columns? Are those datetime datatypes? Because that makes the calculations much easier. If they're just times, you'll have problems calculating the duration when those times cross midnight into the next day.

im using datetime in time_finished and time_started, how to do i group the idle_code in computed field? sorry if have to much question, im just a newbie. — cavin, Aug 15 '13 at 02:26
sums (time_finished - time_start for group 1)<--- i have an error on this one error message say EXPECTING NUMBER EXPRESSION.. — cavin, Aug 16 '13 at 02:20

how to use group by case in powerbuilder 10.5

First question how to group by idle_code and add the total mins.?

this is easy to compute in the powerbuilder using computed field but my problem is how to group them by idle_code and there total mins.

1 Answers1