Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2012
    Posts
    16

    Unanswered: First SQL assignment for work .. need help!

    I was told to come up with 5 query, and I managed to get 3 done in a week, but I'm stuck on the last 2. Seeing if you guys can help me out.

    I need a query to show:

    1.The number of times the employees reprints a credit ticket that was already reprinted on a given day.
    2.The number of days the employees reprints a credit ticket that was already reprinted on a given day.
    For 2. I only have this so far but it gives me the TOTAL number of days an employee Reprints a ticket, not only days already reprinted (more than one for day).

    Select login_username AS employee, site_id AS SiteID
    Count (distinct extract (day from date_time)) as NumbofDays_Reprinted
    From Tables_Names
    Where (date_time >= '1-march-2012'
    )
    AND (date_time <= '31-march-2012'
    )
    AND (payment_meth = 'credit')
    )
    Group by login_username, fac_id
    Order by count (distinct extract (day from date_time)) desc


    ** date_time is the time a reprint was done and is in this format "02-12-2012 12:34:32" so I had to extract the "day" out and count it.

    Number of time an employees reprint a credit ticket that has already been reprinted.

    Example:
    CreditTicket #12 reprinted 3/12/12 9:00am by Bob at SITE01
    CreditTicket #15 reprinted 3/12/12 10am by Bob at SITE01
    CreditTicket #12 reprinted 3/12/12 10:30am by Bob at SITE01
    CreditTicket #12 reprinted 3/12/12 12pm by Bob at SITE01
    CreditTicket #15 reprinted 3/12/12 2pm by Bob at SITE01
    CreditTicket #23 reprinted 3/12/12 1pm by Joe at SITE02

    I want to know the number of time an employee reprinted a Credit Ticket that has already been reprinted. We want to look for multiple reprints by employees on same tickets at Sites.

    So here, it would show "BOB" at "SITE01" on "3/12/12" reprinted "2" times after a Credit Ticket has already been reprinted. Then we can look into that site, since more than 1 reprint was done on the "same" ticket.



    Would I use the > 1 statement on TicketNo? Since if it has more than 1 means it was reprinted again?


    THANKS!
    Last edited by SQLNoob8; 06-14-12 at 11:41.

  2. #2
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    We don't know what your input table looks like or what updates/inserts the reprint package/procedure does, anyway, this will show you a count by user, site, ticket#, day for rows with more than 1 entry:
    Code:
    select login_username as employee, site_id, ticket_no,
           to_char( date_time, 'yyyy-mm-dd' ) as date,
           count(*) as numbofdays_reprinted
     from  schema.tables_names
    where  date_time between to_date( '03-01-2012 00:00:00', 'mm-dd-yyyy hh24:mi:ss' )
     and                     to_date( '03-31-2012 23:59:59', 'mm-dd-yyyy hh24:mi:ss' )
     and   payment_meth  =  'credit'
    group by login_username, site_id, ticket_no, to_char( date_time, 'yyyy-mm-dd' )
    having count(*)  >  1

Posting Permissions

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