Top 10 List - SQL Server Forums

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

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Top 10 List

Author

Topic

mattgee
Starting Member

6 Posts

Posted - 2009-01-24 : 16:27:23

Hi All,

This my first post and am hoping you can help me.

I support a help desk system that has a SQL 2005 back end.

I am wanting to create a script that will give me a Top 10 list of users who have logged tickets on the system.

I'm guessing the script will have to analyze the full table results and return a list of the top 10 submitters.

My SQL query skills are not great so any help would be brilliant.

Thanks again.

Matt

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-24 : 19:39:35

SELECT TOP 10 *
FROM YourTable
ORDER BY SomeColumn DESC

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-25 : 04:52:24

duplicate

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=118526

mattgee
Starting Member

6 Posts

Posted - 2009-01-25 : 15:19:07

Sorry for the duplicate. I wasnt sure where it would be picked up.

Thank you so much for your feedback.

That seems to work >:o)

May i push my look at ask how i could go about splitting these results into months?

In other words display the top 10 with a data range?

Thanks

Matt

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-26 : 08:46:23

[code]
select top 10 with ties
user_id,
count(*) as ticket_count
from
YourTable
where datefield >=@datestart
and datefield<dateadd(dd,1,@dateend)
group by
user_id
order by
ticket_count desc
[/code]
@datestart and @dateend represent date range values you pass

mattgee
Starting Member

6 Posts

Posted - 2009-01-26 : 13:18:27

visakh16, you are a fountain of knowledge! :o) thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-27 : 00:47:08

welcome

mattgee
Starting Member

6 Posts

Posted - 2009-01-31 : 16:10:06

All,

I haven't looked as yet, but i am guessing that when i extract the data, the date format isnt going to be the standard dd/mm/yyy.

The top 10 query i write is perfect now :o) thanks to this forum! now if i go one better and try and play about with date ranges externally i.e. with excel etc...how to i get the date to be in that format? Do i need to create a view to translate this from epoch?

What my end goal here is to be able to work out some trends of tickets logged at the help desk over the months of the year.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-01 : 01:09:33

what are possible formats of date you will get while extracting?

mattgee
Starting Member

6 Posts

Posted - 2009-02-01 : 07:18:05

The date/time is returned in sql as epoch seconds format i use the following website to usually translate! http://webnet77.com/cgi-bin/helpers/epoch.pl

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-01 : 12:59:55

ok. then make parameters as integer or longint and use logic as


select top 10 with ties
  user_id,
  count(*) as ticket_count
from
  YourTable
where datefield >=dateadd(ss,@datestart,dateadd(yy,70,0))
and datefield<=dateadd(ss,@dateend,dateadd(yy,70,0))
group by
  user_id
order by 
  ticket_count desc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources