I have the following Script which runs fine, but I am not receiving the expected output of all columns having data. Is the structure incorrect?
sabrTicketing:
Select
Event_Code as sabrEVENTCDE,
Event_Desc as sabrEVENTDESC,
Usage_Event_Date as sabrUSAGEEVENTDTE,
(Noncomp_Sale_Qty + Noncomp_Return_Qty) as sabrSOLDQTY,
(Comp_Sale_Qty + Comp_Return_Qty) as sabrCOMPQTY,
(Noncomp_Sale_Qty + Noncomp_Return_Qty + Comp_Sale_Qty + Comp_Return_Qty) as sabrTTLQTY,
(Sale_Amount + Return_Amount) as sabrREV
From
(Select
Event.Display_Order
, Event.Event_Code as Event_Code
, Event.Description as Event_Desc
, To_Char (Uevent.Event_Date, 'MM/DD/YYYY') As Usage_Event_Date
, Count (Case When Ticket.Price <> 0 And Oli.Transaction_Type_Code In ('SA','ES','CS')
Then Ticket.Ticket_Id End) As Noncomp_Sale_Qty
, Count (Case When Ticket.Price <> 0 And Oli.Transaction_Type_Code In ('RT','ER')
Then Ticket.Ticket_Id End) * -1 As Noncomp_Return_Qty
, Count (Case When Ticket.Price = 0 And Oli.Transaction_Type_Code In ('SA','ES','CS')
Then Ticket.Ticket_Id End) As Comp_Sale_Qty
, Count (Case When Ticket.Price = 0 And Oli.Transaction_Type_Code In ('RT','ER')
Then Ticket.Ticket_Id End) * -1 As Comp_Return_Qty
, Nvl (Sum (Case When Oli.Transaction_Type_Code In ('SA','ES','CS')
Then Ticket.Price End),0.00) As Sale_Amount
, Nvl (Sum (Case When Oli.Transaction_Type_Code In ('RT','ER')
Then Ticket.Price End),0.00) * -1 As Return_Amount
From hsbcr.Ticket Ticket
Inner Join hsbcr.Order_Line_Item Oli
On Ticket.Order_Line_Item_Id = Oli.Order_Line_Item_Id
Or Ticket.Remove_Order_Line_Item_Id = Oli.Order_Line_Item_Id
Inner Join HSBCR.Event Event
On Oli.Event_Id = Event.Event_Id
Inner Join hsbcr.Event Uevent --Different alias to distinguish the usage event.
On Oli.Usage_Event_Id = Uevent.Event_Id
Inner Join HSBCR.Order_Transaction Ot
On Oli.Order_Id = Ot.Order_Id And Oli.Transaction_Id = Ot.Transaction_Id
Where Oli.Market_Type_Code = 'P'
And Nvl(Ot.Order_Trxn_Assoc_Type_Code,'X') <> 'SI'
AND event.event_id >= '5039' and event.event_id <= '5079'
Group By Event.Display_Order
, Event.Event_Code
, Event.Description
, Uevent.Event_Date
, event.event_id
Order By event.event_id, Event.Display_Order, Uevent.Event_Date, Event.Event_Code);
LEFT JOIN (sabrTicketing)
sabrProjections:
LOAD
eventDATE AS SABRUSAGEEVENTDTE,
[Event Code] AS sabrEVENTCDE,
projectedSales AS sabrPROJSALES,
projREV AS sabrPROJREV,
projCOMP AS sabrPROJCOMP,
adjPROJSALES AS sabrADJSALES,
adjPROJCOMP AS sabrADJCOMP,
adjPROJREV AS sabrADJREV
FROM (ooxml, embedded labels, table is Sheet1);
LEFT JOIN (sabrTicketing)
SabresTicketingNew:
LOAD
SABREVENTCDE,
SABREVENTDESC,
SABRUSAGEEVENTDTE,
SABRSOLDQTY,
SABRCOMPQTY,
SABRTTLQTY,
SABRREV,
sabrPROJSALES,
sabrPROJREV,
sabrPROJCOMP,
sabrADJSALES,
sabrADJCOMP,
sabrADJREV,
'sabr' AS brandNME
RESIDENT sabrTicketing;
DROP TABLE sabrTicketing;
