If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > How to merge two tables and display time gap

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Jun 2012
Posts: 16
How to merge two tables and display time gap

** 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.
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 7,592
since we don't have your tables or data, we can't run, improve or test posted SQL
__________________
You can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
For most folks, they don't know, what they don't know.
Don't say, show. Don't promise, prove.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Jun 2003
Location: West Palm Beach, FL
Posts: 2,661
Quote:
Originally Posted by SQLNoob8 View Post
** Below query displays all Employees at Sites that hits the "ESC" key on the keyboard to clear out transaction

. . . E t c . . .

My problem is: How would I merge these two tables together? So that I can display ......
Create view of each query and join the views.
__________________
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Jun 2012
Posts: 16
Quote:
Originally Posted by LKBrwn_DBA View Post
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,
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Jun 2003
Location: West Palm Beach, FL
Posts: 2,661
Wink

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

2) Then try this:
Code:
CREATE OR REPLACE VIEW MyView1 
AS 
{ Employees at Sites query};

CREATE OR REPLACE VIEW MyView2 
AS 
{Ticket_Date a transaction query};

SELECT {whatever} 
FROM MyView1 v1, MyView2 v2
WHERE v1.some_col like V2.some_col;
__________________
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On