First SQL assignment for work .. need help!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

First SQL assignment for work .. need help!

Author

Topic

SQLNoob8
Starting Member

5 Posts

Posted - 2012-06-14 : 00:01:27

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, fac_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.

^^ That gives me everyday that employee reprinted, but I need days that employee reprints that's was already reprinted on a given day.
(We want to track why an employees is reprinted multiple times during a day ... one is acceptable)

Employee SITEID NumbofDays_Reprinted
John 1122 21
Bob 3454 17
ect...

Would I use > 1 ? Being that if is greater than 1 then it was reprinted again?

Any help would be great for the top 2 query I'm trying to write.

How would you count the number of times a reprint is already reprinted on a given day?

I originally used the (date_time) - 1 ... if I was running it for 1 day. If an employee had 5 reprint for that day total, minus 1 would show how many times employee reprinted after the first reprint.

Is that the one way to go about it?

and how would you show Number of days the employee reprints a tickets that was already reprinted on a given day?

THANKS!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-06-14 : 00:27:53

looks like you are using Oracle and not MS SQL Server. SQLTeam.com is on Microsoft SQL Server. For Oracle, try posting at http://www.dbforums.com/oracle/

KH
[spoiler]Time is always against us[/spoiler]

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2012-06-14 : 00:32:01

Please, show us table structure with sample data and expected output.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-06-14 : 02:22:01

quote:
Originally posted by namman

Please, show us table structure with sample data and expected output.

No - don't show us, do it in an ORACLE forum

No, you're never too old to Yak'n'Roll if you're too young to die.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-14 : 04:43:54

Not really sure what yoou want but maybe this (think it will be close to working in oracle)

select employee, fac_id, numdays = count(*), num = sum(num)
from
(
Select login_username AS employee, fac_id AS SiteID, num = count(*)
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, extract (day from date_time)
having count(*) > 1
) as a
group by employee, fac_id

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SQLNoob8
Starting Member

5 Posts

Posted - 2012-06-14 : 11:29:01

Sorry guys but nobody is replying over there at DB. You guys reply so much quicker.

Anyway, just a question then:

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
CreditTicket #35 reprinted 3/12/12 3pm by Joe at Site02
CreditTicket #35 reprinted 3/12/12 4pm 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" more times after a Credit Ticket has already been reprinted. and "Joe" at "Site02" reprinted ticket "#35" "1" more time from original reprint.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?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-14 : 11:31:31

Did you look at my previous post?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SQLNoob8
Starting Member

5 Posts

Posted - 2012-06-14 : 11:33:46

^^yes, Nigel

I tried that statement but received an error. numdays = count(*), num = sum(num)

From keyword is not found where expected

Is blinking infront of the = and before count

Should I be counting the ticket_no instead looking for > 1 ?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-14 : 11:58:55

maybe

select employee, fac_id, count(*) as numdays, sum(num) as num
from
(
Select login_username AS employee, fac_id AS SiteID, count(*) as num
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, extract (day from date_time)
having count(*) > 1
) as a
group by employee, fac_id

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources