Please see sample below, there is event (LE-0000374) that assigned to different business entities. By default report framework, value will be double counted. However business does request this case, and want this event to associate to different business entities, however count once when aggregating into high events. Report has to get the list of loss events involved and then total.
- Single event
select LOSSEVENT.NAME00 Loss_Event_Name, sum(LOSSEVENT.NET_LOSS_BA) Loss_Event_Net_Loss_BA
from (
select *
from RV_LOSSEVENT
) LOSSEVENT
where LOSSEVENT.NAME00='LE-0000374' and (LOSSEVENT.OP_FLAG_READ_ACCESS='Y' or LOSSEVENT.OP_FLAG_READ_ACCESS is null)
group by LOSSEVENT.NAME00
- Associate with two business entity
select SOXBUSENTITY_CHILD.FULL_PATH Child_Business_Entity_Location, LOSSEVENT.NAME00 Loss_Event_Name, sum(LOSSEVENT.NET_LOSS_BA) Loss_Event_Net_Loss_BA
from ((((
select *
from RV_ENTITY_GPC
) RV_ENTITY_GPC LEFT OUTER JOIN (
select *
from RV_ENTITY
) SOXBUSENTITY_CHILD on RV_ENTITY_GPC.C_ENTITY_ID=SOXBUSENTITY_CHILD.ENTITY_ID and RV_ENTITY_GPC.C_RPT_ID=SOXBUSENTITY_CHILD.REPORTING_PERIOD_ID) LEFT OUTER JOIN (
select *
from RT__ENTITY_LOSSEVENT
) SOXBUSENTITY_CHILD_LOSSEVENT on SOXBUSENTITY_CHILD.ENTITY_ID=SOXBUSENTITY_CHILD_LOSSEVENT.ENTITY_ID and SOXBUSENTITY_CHILD.REPORTING_PERIOD_ID=SOXBUSENTITY_CHILD_LOSSEVENT.REPORTING_PERIOD_ID) LEFT OUTER JOIN (
select *
from RV_LOSSEVENT
) LOSSEVENT on SOXBUSENTITY_CHILD_LOSSEVENT.LOSSEVENT_ID=LOSSEVENT.LOSSEVENT_ID and SOXBUSENTITY_CHILD_LOSSEVENT.REPORTING_PERIOD_ID=LOSSEVENT.REPORTING_PERIOD_ID) LEFT OUTER JOIN (
select *
from RV_ENTITY
) SOXBUSENTITY_GRANDPARENT on RV_ENTITY_GPC.G_ENTITY_ID=SOXBUSENTITY_GRANDPARENT.ENTITY_ID and RV_ENTITY_GPC.G_RPT_ID=SOXBUSENTITY_GRANDPARENT.REPORTING_PERIOD_ID
where SOXBUSENTITY_GRANDPARENT.NAME00='GOR Scotiabank' and LOSSEVENT.NAME00='LE-0000374' and (LOSSEVENT.OP_FLAG_READ_ACCESS='Y' or LOSSEVENT.OP_FLAG_READ_ACCESS is null) and (SOXBUSENTITY_CHILD.OP_FLAG_READ_ACCESS='Y' or SOXBUSENTITY_CHILD.OP_FLAG_READ_ACCESS is null) and (SOXBUSENTITY_GRANDPARENT.OP_FLAG_READ_ACCESS='Y' or SOXBUSENTITY_GRANDPARENT.OP_FLAG_READ_ACCESS is null)
group by SOXBUSENTITY_CHILD.FULL_PATH, LOSSEVENT.NAME00
- Wrong total value
select LOSSEVENT.NAME00 Loss_Event_Name, sum(LOSSEVENT.NET_LOSS_BA) Loss_Event_Net_Loss_BA
from ((((
select *
from RV_ENTITY_GPC
) RV_ENTITY_GPC LEFT OUTER JOIN (
select *
from RV_ENTITY
) SOXBUSENTITY_GRANDPARENT on RV_ENTITY_GPC.G_ENTITY_ID=SOXBUSENTITY_GRANDPARENT.ENTITY_ID and RV_ENTITY_GPC.G_RPT_ID=SOXBUSENTITY_GRANDPARENT.REPORTING_PERIOD_ID) LEFT OUTER JOIN (
select *
from RV_ENTITY
) SOXBUSENTITY_CHILD on RV_ENTITY_GPC.C_ENTITY_ID=SOXBUSENTITY_CHILD.ENTITY_ID and RV_ENTITY_GPC.C_RPT_ID=SOXBUSENTITY_CHILD.REPORTING_PERIOD_ID) LEFT OUTER JOIN (
select *
from RT__ENTITY_LOSSEVENT
) SOXBUSENTITY_CHILD_LOSSEVENT on SOXBUSENTITY_CHILD.ENTITY_ID=SOXBUSENTITY_CHILD_LOSSEVENT.ENTITY_ID and SOXBUSENTITY_CHILD.REPORTING_PERIOD_ID=SOXBUSENTITY_CHILD_LOSSEVENT.REPORTING_PERIOD_ID) LEFT OUTER JOIN (
select *
from RV_LOSSEVENT
) LOSSEVENT on SOXBUSENTITY_CHILD_LOSSEVENT.LOSSEVENT_ID=LOSSEVENT.LOSSEVENT_ID and SOXBUSENTITY_CHILD_LOSSEVENT.REPORTING_PERIOD_ID=LOSSEVENT.REPORTING_PERIOD_ID
where SOXBUSENTITY_GRANDPARENT.NAME00='GOR Scotiabank' and LOSSEVENT.NAME00='LE-0000374' and (LOSSEVENT.OP_FLAG_READ_ACCESS='Y' or LOSSEVENT.OP_FLAG_READ_ACCESS is null) and (SOXBUSENTITY_GRANDPARENT.OP_FLAG_READ_ACCESS='Y' or SOXBUSENTITY_GRANDPARENT.OP_FLAG_READ_ACCESS is null)
group by LOSSEVENT.NAME00
- Correct total value
select Event.Loss_Event_Name Loss_Event_Name, sum(Event.Loss_Event_Net_Loss_BA) Loss_Event_Net_Loss_BA
from (
select LOSSEVENT.LOSSEVENT_ID Loss_Event_Resource_ID, LOSSEVENT.REPORTING_PERIOD_ID Loss_Event_Reporting_Period_ID, LOSSEVENT.NAME00 Loss_Event_Name, sum(LOSSEVENT.NET_LOSS_BA) Loss_Event_Net_Loss_BA
from (
select *
from RV_LOSSEVENT
) LOSSEVENT
where LOSSEVENT.OP_FLAG_READ_ACCESS='Y' or LOSSEVENT.OP_FLAG_READ_ACCESS is null
group by LOSSEVENT.LOSSEVENT_ID, LOSSEVENT.REPORTING_PERIOD_ID, LOSSEVENT.NAME00) Event, (
select distinct LOSSEVENT.LOSSEVENT_ID Loss_Event_Resource_ID, LOSSEVENT.REPORTING_PERIOD_ID Loss_Event_Reporting_Period_ID
from ((((
select *
from RV_ENTITY_GPC
) RV_ENTITY_GPC LEFT OUTER JOIN (
select *
from RV_ENTITY
) SOXBUSENTITY_GRANDPARENT on RV_ENTITY_GPC.G_ENTITY_ID=SOXBUSENTITY_GRANDPARENT.ENTITY_ID and RV_ENTITY_GPC.G_RPT_ID=SOXBUSENTITY_GRANDPARENT.REPORTING_PERIOD_ID) LEFT OUTER JOIN (
select *
from RV_ENTITY
) SOXBUSENTITY_CHILD on RV_ENTITY_GPC.C_ENTITY_ID=SOXBUSENTITY_CHILD.ENTITY_ID and RV_ENTITY_GPC.C_RPT_ID=SOXBUSENTITY_CHILD.REPORTING_PERIOD_ID) LEFT OUTER JOIN (
select *
from RT__ENTITY_LOSSEVENT
) SOXBUSENTITY_CHILD_LOSSEVENT on SOXBUSENTITY_CHILD.ENTITY_ID=SOXBUSENTITY_CHILD_LOSSEVENT.ENTITY_ID and SOXBUSENTITY_CHILD.REPORTING_PERIOD_ID=SOXBUSENTITY_CHILD_LOSSEVENT.REPORTING_PERIOD_ID) LEFT OUTER JOIN (
select *
from RV_LOSSEVENT
) LOSSEVENT on SOXBUSENTITY_CHILD_LOSSEVENT.LOSSEVENT_ID=LOSSEVENT.LOSSEVENT_ID and SOXBUSENTITY_CHILD_LOSSEVENT.REPORTING_PERIOD_ID=LOSSEVENT.REPORTING_PERIOD_ID
where LOSSEVENT.REPORTING_PERIOD_ID=-1 and SOXBUSENTITY_GRANDPARENT.NAME00='GOR Scotiabank' and LOSSEVENT.NAME00='LE-0000374' and (LOSSEVENT.OP_FLAG_READ_ACCESS='Y' or LOSSEVENT.OP_FLAG_READ_ACCESS is null) and (SOXBUSENTITY_GRANDPARENT.OP_FLAG_READ_ACCESS='Y' or SOXBUSENTITY_GRANDPARENT.OP_FLAG_READ_ACCESS is null)) ListOfEvents
where Event.Loss_Event_Reporting_Period_ID=ListOfEvents.Loss_Event_Reporting_Period_ID and Event.Loss_Event_Resource_ID=ListOfEvents.Loss_Event_Resource_ID
group by Event.Loss_Event_Name
This join has archived the best performance as both loss event ID and report ID are primary index.
No comments:
Post a Comment