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
)
SELECT To_char(Trunc(toll_date, 'HH'), 'DD-MON-RR HH24 "Hr"') date_hour,
Count(1)
FROM (
SELECT toll_id,
entry_plaza,
exit_plaza,
plate,
toll_date
FROM v1
WHERE plate NOT IN ( '2',
'3' )
UNION
SELECT violation_id toll_id,
entry_plaza,
exit_plaza,
plate,
toll_date
FROM v2
WHERE plate NOT IN ( '2',
'3' ) )
GROUP BY Trunc(toll_date, 'HH')
ORDER BY 1,
select to_char(trunc(toll_date, 'HH'), 'DD-MON-RR HH24 "Hr"') date_hour,
count(1)
FROM (
SELECT toll_id,
entry_plaza,
exit_plaza,
plate,
toll_date
FROM v_toll_full
WHERE (
entry_plaza = 101
AND exit_plaza = 101 )
UNION
SELECT violation_id toll_id,
entry_plaza,
exit_plaza,
plate,
toll_date
FROM v_violation_full
WHERE (
entry_plaza = 101
AND exit_plaza = 101 ) )
GROUP BY trunc(toll_date, 'HH')
ORDER BY 1