Hey Guys,
Looking for help on adding a count field to the below script. I realize its missing a "Group By", but when I add it, it's still kicking out errors and I'm not sure why.....
The below script works on it own, but need help adding the Count(*)......below I commented out the section where the "Count(*)" would go, but not sure about the group by (where it goes, which fields I need, etc) and/or if I'm missing anything else.
--
WITH expired_data AS
( SELECT client_id, vio_type_id, VALUE
FROM category_list_details cld
WHERE category_list_id =
( SELECT id
FROM category_lists cl
WHERE cl.client_id = cld.client_id AND cl.code = 'INSTAENFC' )
UNION
SELECT cl.client_id, cll.vio_type_id, VALUE
FROM category_lists cl,
(SELECT DISTINCT client_id,
vio_type_id
FROM client_locations) cll
WHERE cl.code = 'INSTAENFC'
AND cl.client_id = cll.client_id
AND NOT EXISTS
( SELECT 1
FROM category_list_details cld
WHERE cld.client_id = cl.client_id
AND cld.category_list_id = cl.id
AND cld.vio_type_id = cll.vio_type_id )
)
SELECT * FROM (
-- Does not include DMV NOT FOUND records:
SELECT vio_month,
vio_month_num,
client_state,
client_name,
tier,
pay_type,
Vio_Id,
TRUNC (vio_create_date) VIO_CREATE_DATE,
TO_CHAR (vio_create_date, 'hh:mm:ss AM') VIO_CREATE_TIME,
TRUNC (vio_dtime) VIO_DATE,
TO_CHAR (vio_dtime, 'hh:mm:ss AM') VIO_TIME,
NULL DMV_FOUND_DATE, --<-------<------- added for GA CG
NULL IC_ACCEPT_DATE,
NULL IC_ACCEPT_TIME,
NVL (TRUNC (vn_accept_date),
TRUNC (rejected_date))
VN_ACCEPT_DATE,
TO_CHAR (vn_accept_date, 'hh:mm:ss AM') VN_ACCEPT_TIME,
TRUNC (rejected_date) REJECTED_DATE,
TO_CHAR (rejected_date, 'hh:mm:ss AM') REJECTED_TIME,
vio_expire_num REJECTED_MONTH,
TRUNC (rejected_date) - TRUNC (vn_accept_date) diff,
t.queue_rejected_from,
t.instate_enforceable_days,
VioType,
site_id,
site_name,
CASE
WHEN ClientId IN (SELECT client_id
FROM dw_client_supervisor
WHERE pd_run_yn = 'Y')
AND VioType = 'SPEEDVAN'
THEN
'Y'
ELSE
'N'
END
PD_RUN,
CASE
WHEN ( (TRUNC (Rejected_Date) - 2) >=
(TRUNC (vio_dtime) + t.instate_enforceable_days))
THEN
-- Check for VN accept Date
CASE WHEN
(TRUNC (Rejected_Date) - 2) >= t.vn_accept_date AND
t.vn_accept_date <= (TRUNC (vio_dtime) + t.instate_enforceable_days)
AND t.queue_rejected_from = 'WF POLICE REVIEW' THEN
'PD'
WHEN t.queue_rejected_from = 'WF POLICE REVIEW' AND
t.instate_enforceable_days <= 16 AND
TRUNC (Rejected_Date) -3 >= t.vn_accept_date THEN
'VERRA'
WHEN t.queue_rejected_from = 'WF POLICE REVIEW' AND
t.instate_enforceable_days > 16 AND t.instate_enforceable_days < 31 AND
TRUNC (Rejected_Date) -6 >= t.vn_accept_date THEN
'VERRA'
WHEN t.queue_rejected_from = 'WF POLICE REVIEW' AND
t.instate_enforceable_days > 31 AND
TRUNC (Rejected_Date) -9 >= t.vn_accept_date THEN
'VERRA'
ELSE
'SYSTEM REJECT PAST ENFORCEMENT'
END
ELSE
CASE
WHEN (t.instate\_enforceable\_days \<= 16)
THEN
CASE
WHEN ( (t.instate\_enforceable\_days - 3) >
( TRUNC (vn\_accept\_date)
- TRUNC (vio\_dtime))
AND queue\_name NOT IN
('BG DEMO',
'BG PRINT ERROR',
'WF DMV NOT FOUND',
'WF TECH REVIEW',
'BG PRINT HOLD'))
THEN
'PD'
ELSE
CASE
WHEN ( queue\_name IN
('BG DEMO',
'BG PRINT ERROR',
'WF DMV NOT FOUND',
'WF TECH REVIEW',
'BG PRINT HOLD'))
THEN
'SYSTEM'
ELSE
'VERRA'
END
END
ELSE
CASE
WHEN (t.instate\_enforceable\_days \<= 31)
THEN
CASE
WHEN ( (t.instate\_enforceable\_days - 6) >
( TRUNC (vn\_accept\_date)
- TRUNC (vio\_dtime))
AND queue\_name NOT IN
('BG DEMO',
'BG PRINT ERROR',
'WF DMV NOT FOUND',
'WF TECH REVIEW',
'BG PRINT HOLD'))
THEN
'PD'
ELSE
CASE
WHEN ( queue\_name IN
('BG DEMO',
'BG PRINT ERROR',
'WF DMV NOT FOUND',
'WF TECH REVIEW',
'BG PRINT HOLD'))
THEN
'SYSTEM'
ELSE
'VERRA'
END
END
ELSE
CASE
WHEN (t.instate\_enforceable\_days > 31)
THEN
CASE
WHEN ( (t.instate\_enforceable\_days - 9) >
( TRUNC (vn\_accept\_date)
- TRUNC (vio\_dtime))
AND queue\_name NOT IN
('BG DEMO',
'BG PRINT ERROR',
'WF DMV NOT FOUND',
'WF TECH REVIEW',
'BG PRINT HOLD'))
THEN
'PD'
ELSE
CASE
WHEN ( queue\_name IN
('BG DEMO',
'BG PRINT ERROR',
'WF DMV NOT FOUND',
'WF TECH REVIEW',
'BG PRINT HOLD'))
THEN
'SYSTEM'
ELSE
'VERRA'
END
END
ELSE
'NO FAULT'
END
END
END
END
LIABILITY,
last\_action\_user,
to\_number(to\_char(rejected\_date,'YYYYMMDD')) REJ\_KEY\_DATE
-- Count (*)
FROM (SELECT c.id ClientId,
TO_CHAR (v.vio_dtime, 'MM') vio_month_num,
TO_CHAR (v.vio_dtime, 'MONTH') vio_month,
CASE WHEN SUBSTR(c.description,-4,1) = ',' THEN
TRIM (SUBSTR (c.description,
1,
INSTR (c.description,
',',
1,
1)
- 1))
ELSE c.description END
client_name,
c.client_state,
v.id vio_id,
vio_dtime,
v.create_dtime vio_create_date,
last_action_dtime rejected_date,
TO_CHAR (v.last_action_dtime, 'MM') vio_expire_num,
(SELECT MAX (last_action_dtime)
FROM violation_histories
WHERE client_id = v.client_id
AND vio_id = v.id
AND queue_id IN
(SELECT id
FROM queues
WHERE client_id = v.client_id
AND queue_name IN ('WF VERIFY NOTICE' , 'BG VERIFY NOTICE'))
AND last_action_id IN
(SELECT id
FROM category_lists
WHERE client_id = v.client_id
AND code IN ('SRREVIEW', 'ACCEPT')))
vn_accept_date,
c.tier,
c.pay_type,
v.queue_id,
q.queue_name,
CASE
WHEN q.queue_name = 'WF SPRV REVIEW'
THEN
CASE
WHEN (SELECT queue_name
FROM queues
WHERE client_id = client_id
AND id = prev_queue_id) IN
('WF TECH REVIEW', 'WF POLICE REVIEW')
THEN
'POLICE SUPER REVIEW QUEUE'
ELSE
'OPERATION SUPER REVIEW QUEUE'
END
ELSE
queue_name
END
queue_rejected_from,
v.prev_queue_id,
v.client_id,
vt.code Viotype,
TO_NUMBER (cl2.VALUE) instate_enforceable_days,
v.last_action_user last_action_user,
cLocation.code site_id,
cLocation.description site_name
FROM violations v,
clients c,
category_lists cl,
expired_data cl2,
queues q,
violation_types vt,
category_lists cLocation
WHERE v.client_id = c.id
AND v.client_id = cl.client_id
AND v.last_action_id = cl.id
AND v.client_id = q.client_id
AND v.queue_id = q.id
AND v.vio_type_id = vt.id
AND v.vio_type_id = vt.id
AND cl.code = 'TOOOLD'
AND in_use_flag = 'R'
AND NOT EXISTS ---<--- added to filter on GA CG only
( SELECT 1
FROM violations vl
WHERE v.client_id = vl.client_id
AND v.id = vl.id
AND vl.vio_type_id = 43
AND vl.client_id in
( SELECT id
FROM clients
WHERE client_state = 'GA') )
AND cl2.client_id = v.client_id
AND cl2.vio_type_id = vt.id
AND last_action_dtime >= '01/JAN/2019'
AND v.client_id = cLocation.client_id
AND v.location_id = cLocation.id
AND NOT EXISTS
(SELECT 1
FROM queues
WHERE v.client_id = q.client_id
AND v.queue_id = q.id
AND q.queue_name = 'WF SPRV REVIEW'
AND v.prev_queue_id IN
(SELECT id
FROM queues
WHERE queue_name IN
('WF TECH REVIEW',
'WF POLICE REVIEW')))
AND EXISTS
(SELECT 1
FROM client_locations cloc
WHERE v.client_id = cloc.client_id
AND v.location_id = cloc.location_id
AND v.vio_dtime BETWEEN cloc.start_date
AND cloc.end_date
AND v.vio_dtime >=
cloc.start_date + cloc.warning_days)
AND c.id IN (SELECT client_id
FROM dw_client_supervisor
WHERE exp_vio_bypass_yn = 'N')
AND c.id IN (SELECT client_id
FROM dw_client_supervisor
WHERE one_touch_yn = 'N')
AND q.queue_name <> 'WF DMV NOT FOUND'
AND ( (v.create_dtime - v.vio_dtime) <=
(TO_NUMBER (cl2.VALUE) - 5))) t )