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
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?
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:
select login_username as employee, site_id, ticket_no,
to_char( date_time, 'yyyy-mm-dd' ) as date,
count(*) as numbofdays_reprinted
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