View HLPDTEST.STATEGPS.V_MONTHLY_PROJECT_STATISTICS | Generated by SchemaSpy |
| |||||||||||||||||||||||||||||||||||||||||||||||
Analyzed at Sun Jan 17 01:06 IST 2021 |
View Definition:
select
nvl(a.application_id,b.application_id) application_id,
nvl(a.month_year,b.month_year) MONTH_YEAR,
nvl(a.TICKET_COUNT,0) TICKET_COUNT,
nvl(b.CLOSED_TICKET_COUNT,0) CLOSED_TICKET_COUNT
from (
SELECT to_char(I.creation_date,'MON-YYYY') MONTH_YEAR,count(distinct I.INSTANCE_ID) ticket_count, I.application_id
FROM T_INSTANCES I, T_GPS_INSTANCES GI
where I.INSTANCE_ID = GI.INSTANCE_ID
AND GI.ACTIVE=1
group by to_char(I.creation_date,'MON-YYYY'), I.application_id
) a
FULL OUTER JOIN
(
SELECT to_char(GI.created_date, 'MON-YYYY') MONTH_YEAR, count(distinct I.INSTANCE_ID) closed_ticket_count,
I.Application_Id
FROM T_INSTANCES I, T_GPS_INSTANCES GI, T_GPS_WORKFLOW_STATES WS
where I.INSTANCE_ID = GI.INSTANCE_ID
AND GI.ACTIVE=1
AND GI.CURRENT_STATE_ID = WS.STATE_ID
AND WS.STATE_DESCRIPTOR = 'Workflow End'
group by to_char(GI.created_date, 'MON-YYYY'), I.application_id
) b
on
a.month_year = b.month_year
and a.application_id = b.application_id
ORDER BY TO_DATE(a.month_year, 'MON-YYYY')
Possibly Referenced Tables/Views:
![]() ![]() |