Results 1 to 5 of 5
  1. #1
    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.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,622
    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.
    There are no stupid questions, but there are a LOT of Inquisitive Idiots.

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,670
    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

  4. #4
    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,

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,670

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •