How to use count statement in this query?

Question

I have to display the daily crane working hours and total box in a ship.
For an example, a crane named FG1 may work from 09/25/2019 at 8 pm until 09/26/2019 at 3 am. the query need to display two information, which are the total box from 09/25/2019 8pm to 11.59 pm and total box from 09/26/2019 12 am to 3 am. The problem is that it does not count the total box between the two date properly.

SELECT SUBSTR (b.user_voy, 1, 12) JPVC
  , NVL (SUBSTR (TO_CHAR  (MIN (g.actual_time), 'YYYY-MM-DD HH24:MI:SS'), 1, 20),'-') FIRSTLIFT
  , NVL (SUBSTR (TO_CHAR  (MAX (g.actual_time), 'YYYY-MM-DD HH24:MI:SS'), 1, 20),'-') LASTLIFT
  , (
    select Count(actual_time)
    from tb_gc_odr
    where actual_time IS NOT NULL
      and equ_no = g.equ_no
      and vsl_cd = t.vsl_cd
      AND call_year = t.call_year
      AND call_seq = t.call_seq
    ) as COM_TOTAL
  , SUBSTR((ROUND((((FLOOR(((MAX(g.actual_time)) - (MIN(g.actual_time))) * 24)) * 60) + (MOD(FLOOR(((MAX(g.actual_time)) - (MIN(g.actual_time))) * 24 * 60), 60))) / 60, 2) || ' '), 1, 18) WH
FROM tb_gc_odr g
  , tb_master t
  , tb_berthplan b
WHERE 1 = 1
  AND g.vsl_cd = t.vsl_cd
  AND g.call_year = t.call_year
  AND g.call_seq = t.call_seq
  AND g.cntr_no = t.cntr_no
  AND g.cntr_seq = t.cntr_seq
  AND b.call_year = t.call_year
  AND b.call_seq = t.call_seq
  AND b.vsl_cd = t.vsl_cd
  AND g.cntr_seq = t.cntr_seq
  AND t.cntr_seq = '0'
  AND t.cargo_type <> 'BB'
  AND g.equ_no = 'FG1'
  AND TO_CHAR(g.actual_time, 'MM/DD/YYYY') between '09/23/2019' and '09/26/2019'
GROUP BY t.vsl_cd
  , t.call_year
  , t.call_seq
  , g.equ_no
  , b.user_voy
  , TO_CHAR(g.actual_time, 'MM/DD/YYYY')
order by g.equ_no
  , FIRSTLIFT

I noticed that the count statement is wrong

(
  select Count(actual_time) 
  from tb_gc_odr
  where actual_time IS NOT NULL
    and equ_no = g.equ_no 
    and vsl_cd = t.vsl_cd
    AND call_year = t.call_year 
    AND call_seq = t.call_seq
) as COM_TOTAL

Expectation:

JPVC | FIRSTLIFT          | LASTLIFT           | COM_TOTAL | WH
-----+--------------------+--------------------+-----------+--------
A01  | 09/25/2019 8:00PM  | 09/25/2019 11:59PM | 97        | 4 HOURS
A01  | 09/26/2019 12:00AM | 09/26/2019 03:00AM | 100       | 3 HOURS

Actual Result:

JPVC | FIRSTLIFT          | LASTLIFT           | COM_TOTAL | WH
-----+--------------------+--------------------+-----------+--------
A01  | 09/25/2019 8:00PM  | 09/25/2019 11:59PM | 197       | 4 HOURS
A01  | 09/26/2019 12:00AM | 09/26/2019 03:00AM | 197       | 3 HOURS

Is there any way to display total box between the FIRSTLIFT and LASTLIFT?

edited: this is the sample data that I got

FIRSTLIFT : LASTLIFT : JPVC : WORKING HOUR : TOTALBOX

2019-09-23 00:02:07 : 2019-09-23 04:10:15 : 19KITN-0518S : 4.13 : 84

2019-09-24 07:37:47 : 2019-09-24 17:42:03 : 19KITN-0519S : 10.07 : 210

2019-09-24 21:36:23 : 2019-09-24 23:59:07 : 19HBRM-HM134 : 2.37 : 177

2019-09-25 00:01:25 : 2019-09-25 05:34:07 : 19HBRM-HM134 : 5.53 : 177

2019-09-25 09:21:18 : 2019-09-25 12:28:51 : 19D112-7262 : 3.12 : 110

2019-09-25 15:57:29 : 2019-09-25 23:57:35 : 19BDGM-BM193 : 8 : 195

2019-09-26 00:00:58 : 2019-09-26 03:37:30 : 19BDGM-BM193 : 3.6 : 195

New Update:

Its okay. already figured it out. need to add AND TO_CHAR(actual_time,'MM/DD/YYYY') = TO_CHAR( g.actual_time,'MM/DD/YYYY')

Answer 1

Try to add time grouping from table tb_gc_odr like this :

select g.*,case 
            when to_char(g.actual_time,'hh24mi') <= 300 or to_char(g.actual_time,'hh24mi')  >= 2000 then 'group 1' 
            when to_char(g.actual_time,'hh24mi') > 300 or to_char(g.actual_time,'hh24mi')  < 2000 then 'group 2'
            end as group_time from tb_gc_odr g

So, your Query for full case being something like this :

SELECT SUBSTR (b.user_voy, 1, 12) JPVC
  , NVL (SUBSTR (TO_CHAR  (MIN (g.actual_time), 'YYYY-MM-DD HH24:MI:SS'), 1, 20),'-') FIRSTLIFT
  , NVL (SUBSTR (TO_CHAR  (MAX (g.actual_time), 'YYYY-MM-DD HH24:MI:SS'), 1, 20),'-') LASTLIFT
  , (
    select Count(actual_time)
    from tb_gc_odr
    where actual_time IS NOT NULL
      and equ_no = g.equ_no
      and vsl_cd = t.vsl_cd
      AND call_year = t.call_year
      AND call_seq = t.call_seq
    ) as COM_TOTAL
  , SUBSTR((ROUND((((FLOOR(((MAX(g.actual_time)) - (MIN(g.actual_time))) * 24)) * 60) + (MOD(FLOOR(((MAX(g.actual_time)) - (MIN(g.actual_time))) * 24 * 60), 60))) / 60, 2) || ' '), 1, 18) WH
FROM (select g.*,case 
            when to_char(g.actual_time,'hh24mi') <= 300 or to_char(g.actual_time,'hh24mi')  >= 2000 then 'group 1' 
            when to_char(g.actual_time,'hh24mi') > 300 or to_char(g.actual_time,'hh24mi')  < 2000 then 'group 2'
            end as group_time from tb_gc_odr g
            ) g
  , tb_master t
  , tb_berthplan b
WHERE 1 = 1
  AND g.vsl_cd = t.vsl_cd
  AND g.call_year = t.call_year
  AND g.call_seq = t.call_seq
  AND g.cntr_no = t.cntr_no
  AND g.cntr_seq = t.cntr_seq
  AND b.call_year = t.call_year
  AND b.call_seq = t.call_seq
  AND b.vsl_cd = t.vsl_cd
  AND g.cntr_seq = t.cntr_seq
  AND t.cntr_seq = '0'
  AND t.cargo_type <> 'BB'
  AND g.equ_no = 'FG1'
  AND TO_CHAR(g.actual_time, 'MM/DD/YYYY') between '09/23/2019' and '09/26/2019'
GROUP BY t.vsl_cd
  , t.call_year
  , t.call_seq
  , g.equ_no
  , b.user_voy
  , g.group_time
order by g.equ_no
  , FIRSTLIFT

Answer 2

If you can show some sample data, how the data looks then it will little bit easier to solve the problems. but I want to add in the SUB Query u used you have to add more parameters for the DATES i think they are taking all data.

(
  select Count(actual_time) 
  from tb_gc_odr
  where actual_time IS NOT NULL
    and equ_no = g.equ_no 
    and vsl_cd = t.vsl_cd
    AND call_year = t.call_year 
    AND call_seq = t.call_seq
    AND actual_time between '*datefrom*' and '*dateto*'
) as COM_TOTAL

you have to filter data. i tried to solve your problems

Answer 3

It's hard to answer this without sample data, but your calculation does not limit the results to the times you specify.

You need to include something like:

(select Count(actual_time) 
from tb_gc_odr 
where actual_time IS NOT NULL 
and equ_no= g.equ_no 
and vsl_cd = t.vsl_cd 
AND call_year = t.call_year 
AND call_seq = t.call_seq 
and actual_time > NVL (SUBSTR (TO_CHAR  (MIN (g.actual_time), 'YYYY-MM-DD HH24:MI:SS'), 1, 20),'-')
and actual_time < NVL (SUBSTR (TO_CHAR  (MAX (g.actual_time), 'YYYY-MM-DD HH24:MI:SS'), 1, 20),'-')
) as COM_TOTAL,