** Below query displays all Employees at Sites that hits the "ESC" key on the keyboard to clear out transaction

SELECT Employees, SiteID,SiteName, COUNT (*) as Numdays_ESC,
sum(ESC) as NumTimes_ESC, round((sum(ESC) / Count (*)),2) as Average_ESC_Per_Day
FROM
(
Select POS_ABNDN.APP_UID AS Employees, RDN_FAC_FULL.FAC_IDU AS SiteID,RDN_FAC_FULL.FAC_NM AS SiteName, to_char(POS_ABNDN.ABNDN_DT,'YYYY-MM-DD'),
COUNT(POS_ABNDN.ABNDN_ID) as ESC, (COUNT(POS_ABNDN.ABNDN_ID) / Count (*)) as Average_ESC_Per_Day
FROM
POS_ADMIN_R2.POS_ABNDN
INNER JOIN POS_ADMIN_R2.POS_PAYMENT_METHOD_LKP
ON (POS_ABNDN.PAYMENT_METHOD_ID = POS_PAYMENT_METHOD_LKP.PAYMENT_METHOD_ID)
INNER JOIN WMAPPS.RDN_FAC_FULL
ON POS_ABNDN.FAC_IDU = RDN_FAC_FULL.FAC_IDU
WHERE POS_ABNDN.ABNDN_EVENT_TYPE = ANY ('Cancel','Discard')
AND POS_PAYMENT_METHOD_LKP.PAYMENT_METHOD_DESC = 'Cash'
--AND POS_ABNDN.FAC_IDU = 'S04108'--;SiteID)
AND POS_ABNDN.ABNDN_DT >= to_date('1/01/2013 00:00:00', 'mm/dd/yyyy hh24:mi:ss')
AND POS_ABNDN.ABNDN_DT <= to_date('02/7/2013 23:59:59', 'mm/dd/yyyy hh24:mi:ss')
GROUP by POS_ABNDN.APP_UID , RDN_FAC_FULL.FAC_IDU,RDN_FAC_FULL.FAC_NM, to_CHAR(POS_ABNDN.ABNDN_DT,'YYYY-MM-DD')
)
GROUP BY Employees, SiteID, SiteName
ORDER by Numdays_ESC desc, NumTimes_ESC desc;



-----------------------------------------------
* Below query shows the Ticket_Date a transaction was recorded by which Employee at which location (SiteID)

select pos_ticket_header.FAC_IDU as SiteID,
TICKETDETAIL.OPERATOR_IN_ID AS Employees,
decode(pos_ticket_header.PAYMENT_METHOD_ID, '1', 'Cash', '2', 'Credit Card', '3',
'Credit Account', '4', 'Debit Account',
'5', 'Check', '6', 'Open Check', '7', 'Pre-Pay') payment_method, ticket_date, TICKET_AMOUNT,
decode(pos_ticket_header.Status_ID, '1', 'Active', '2', 'Inactive', '3', 'Expired', '4', 'Sent',
'5', 'Staged', '6', 'Completed', '7', 'In Process') ticket_status
from pos_ticket_header, POS_TICKET_DETAIL TICKETDETAIL, POS_CUSTOMER_ORG
where
TICKETDETAIL.TICKET_HEADER_ID_MASTER = POS_TICKET_HEADER.TICKET_HEADER_ID_MASTER
AND POS_TICKET_HEADER.CUSTOMER_ORG_ID = POS_CUSTOMER_ORG.CUSTOMER_ORG_ID
AND TICKETDETAIL.TICKET_LINE_NO = 1
and pos_ticket_header.fac_idu = 'S04083'
AND POS_TICKET_HEADER.audt_create_dt >= to_date('2/2/2013 00:00:00', 'mm/dd/yyyy hh24:mi:ss')
AND POS_TICKET_HEADER.audt_create_dt <= to_date('2/2/2013 23:59:59', 'mm/dd/yyyy hh24:mi:ss')
order by SiteID, ticket_no;




My problem is: How would I merge these two tables together? So that I can display similar to the TOP query the result of Employees at Sites that hits the ESC function but before 1minute of the Time_Date in the Bottom query?


I dont want to display all and every time an Employees hit the ESC key (is quite large results), but only the employees that hit the ESC key before 1 minute from the next transaction (Time_Date).

*** Scenario - An employee sets up a ticket at 9:00am, collects the cash, but HIT the ESC key to clear the transaction. Pockets the CASH for example. Then the next transaction arrives 3 minutes later and is recorded correctly.

I noticed when is within a minute ... is usually a correction to a legitimate current ticket. Clears it and correct ticket within 30 second.

since we don't have your tables or data, we can't run, improve or test posted SQL

Create view of each query and join the views.

LK thanks for the help, but how do you create a view of each query?

What statement would I start of with?

Thanks,

1) Review the fine Oracle® Database SQL Language Reference on the subject.

2) Then try this: