//Create a mapping table
EventMap:
Mapping
Load * Inline [
EventDate,EventCount
01/01/2017,1
02/14/2018,1
03/15/2017,1
11/06/2016,1
];
//The use a preceding load to lookup the events
Load *,
ApplyMap('EventMap',TransactionDate,0) as EventCounter;
SQL
select
TO_CHAR(po.created_date, 'MM-DD-YYYY'),
es.last_updated_date,
e.event_code AS EVENTCODE,
e.event_date,
a.agency_code,
bt.buyer_type_code,
bt.description AS buyer_type_desc,
c.channel_code,
c.description AS channel_desc,
ps.price_scale_code,
ps.description AS price_scale_desc,
es.price,
sec.section_code,
s.row_,
s.seat_number,
COUNT(1) AS QTY
from
agency a,
buyer_type bt,
channel c,
event e,
event_category ec,
event_seat es,
order_line_item oli,
patron_order po,
price_scale ps,
seat s,
section sec
where
oli.order_id = po.order_id
and oli.usage_event_id = e.event_id
and es.event_id = e.event_id
and es.order_id = po.order_id
and es.order_line_item_id = oli.order_line_item_id
and po.created_by_agency_id = a.agency_id
and a.channel_id = c.channel_id
and e.event_category_id = ec.event_category_id
and sec.section_id = es.section_id
and s.seat_id = es.seat_id
and es.buyer_type_id = bt.buyer_type_id
and ps.price_scale_id = es.price_scale_id
and trunc(po.created_date) >= ('01-JAN-2018')
and trunc(po.created_date) <= ('08-APR-2019')
and oli.market_type_code = 'P'
and oli.transaction_type_code in ( 'SA', 'ES', 'CS' ) -- sale, claimsale or exchangesale
and c.channel_code in ( 'BOXOFF', 'INTERNET', 'PHONE' )
and ec.event_category_code = 'TYPE'
and e.event_id >= '5039' and e.event_id <= '5079'
GROUP BY
po.created_date,
es.last_updated_date,
e.event_code,
e.event_date,
a.agency_code,
bt.buyer_type_code,
bt.description,
c.channel_code,
c.description,
ps.price_scale_code,
ps.description,
es.price,
sec.section_code,
s.row_,
s.seat_number;
