WITH v1 ( toll_id, entry_plaza, exit_plaza, plate, toll_date )
AS (SELECT 1,
101,
102,
'MARS01234',
To_timestamp('21-Mar-17', 'dd-Mon-rr')
FROM dual
UNION ALL
SELECT 2,
101,
103,
'MARS9876',
To_timestamp('21-Mar-17', 'dd-Mon-rr')
FROM dual
UNION ALL
SELECT 3,
101,
101,
'MARS7321',
To_timestamp('21-Mar-17', 'dd-Mon-rr')
FROM dual
UNION ALL
SELECT 4,
101,
104,
'2',
To_timestamp('21-Mar-17', 'dd-Mon-rr')
FROM dual
UNION ALL
SELECT 5,
101,
101,
'MARS2234',
To_timestamp('21-Mar-17', 'dd-Mon-rr')
FROM dual
UNION ALL
SELECT 6,
101,
105,
'MARS7667',
To_timestamp('21-Mar-17', 'dd-Mon-rr')
FROM dual),
v2 ( violation_id, entry_plaza, exit_plaza, plate, toll_date )
AS (SELECT 2,
101,
102,
'MARS01234',
To_timestamp('21-Mar-17', 'dd-Mon-rr')
FROM dual
UNION ALL
SELECT 7,
101,
103,
'MARS8747',
To_timestamp('21-Mar-17', 'dd-Mon-rr')
FROM dual
UNION ALL
SELECT 9,
101,
101,
'MARS9842',
To_timestamp('21-Mar-17', 'dd-Mon-rr')
FROM dual
UNION ALL
SELECT 10,
101,
104,
'MARS01244',
To_timestamp('21-Mar-17', 'dd-Mon-rr')
FROM dual
UNION ALL
SELECT 11,
101,
101,
'3',
To_timestamp('21-Mar-17', 'dd-Mon-rr')
FROM dual
UNION ALL
SELECT 12,
101,
105,
'MARS09234',
To_timestamp('21-Mar-17', 'dd-Mon-rr')
FROM dual
UNION ALL
SELECT 13,
101,
105,
'MARS09134',
To_timestamp('21-Mar-17', 'dd-Mon-rr')
FROM dual
UNION ALL
SELECT 14,
101,
105,
'MARS89234',
To_timestamp('21-Mar-17', 'dd-Mon-rr')
FROM dual),
v3 (count_v1)
AS (SELECT Count(1)
FROM v1),
v4 (count_v2)
AS (SELECT Count(1)
FROM v2),
v ( toll_id, entry_plaza, exit_plaza, plate, toll_date )
AS (SELECT toll_id,
entry_plaza,
exit_plaza,
plate,
toll_date
FROM v1
WHERE ( NOT( EXISTS(SELECT NULL
FROM v2
WHERE toll_id = v1.toll_id) ) )
UNION ALL
SELECT violation_id,
entry_plaza,
exit_plaza,
plate,
toll_date
FROM v2) -- select v3.count_v1 from v3 --6
-- select v4.count_v2 from v4 -- 8
SELECT To_char(Trunc(toll_date, 'HH'), 'DD-MON-RR HH24 "Hr"') AS date_hour,
Count(CASE
WHEN plate NOT IN ( '2', '3' ) THEN 1
END) AS
count_plate_not_2_or_3,
Count(CASE
WHEN entry_plaza = 101
AND exit_plaza = 101 THEN 1
END) AS
count_101_entry_exit,
Round(Count(v4.count_v2) / Count(v3.count_v1), 1) AS avg_trans_count
FROM v,
v3,
v4
GROUP BY Trunc(toll_date, 'HH')
ORDER BY 1;
DATE_HOUR COUNT_PLATE_NOT_2_OR_3
--------------- ---------------------------------------
COUNT_101_ENTRY_EXIT AVG_TRANS_COUNT
--------------------------------------- ---------------------------------------
21-MAR-17 00 Hr 7
2 1