query that returns the top 5 records (most frequently used)kindly read 1

cfdeveloper · Mar 22, 2005

Hello everybody, I really need someone to help me here. I'm developing a report and stats front-end interface
for a job logging system in ColdFusion and SQL Server 2000. I need your help in writing a sql query. I want to get
the top 5 issues used when logging a job for a customer from the database. All issues are stored in a table called
job_issues and all jobs logged by customers are stored in the job table. The job_issues table has a primary key called
issueUnique which is referenced in the jobs table. I'll give you an example and hopefully it will help you understand what I'm asking here.

Ex data stored in the job_issues table:

CODE
Issue1 Issue2 IssueUnique
Administration Change 26
Administration Move 104
Administration Copy 115
Administration New 53
Administration Delete 54
Advice General 159
Advice Hardware 30
Advice Software 31
Fault Hardware 16
Fault Software 17
Fault Virus 182
Maintenance Equipment 158
Maintenance Scheduled Event 28
Project IT 34
Project Engineering 35

I want to get all issueUnique values where Issue1 is not null and Issue2 is not null. This query should do the job

CODE
SELECT IssueUnique
FROM job_issues
WHERE (Issue1 <> '') AND (Issue1 IS NOT NULL)
AND (Issue2 <> '') AND (Issue2 IS NOT NULL)
ORDER BY IssueUnique

Sample Results:

CallSubjectUnique
6
16
17
19
20
21
22
24
26
28
29
30
31
32

I want to now query the jobs table and return a recordset containing the top 5 (5 most frequent issues used when logging jobs) issues in a date range.

The date value is stored in the logdatetime column in the jobs table. The jobs table has a column to store the issueUnique with the same name. I hope this is making sense.

I would really apprecaite your help on this

Best regards
cfcoder

tlhawkins · Mar 22, 2005

hey cfcoder,

That's not an easy one.

I would recommend creating a stored procedure that would return what you want.
if your job table was called tbl_job and had a link to your job_issues table called issue_key then the T-SQL would look something like:

Code:

declare @issue_key int
declare @issue_weight int

create table tbl_issue_weight  (issue_key int,issue_weight int)

declare issueList  cursor for
select issueUnique from  job_issues where  (Issue1 <> '') AND (Issue1 IS NOT NULL) AND (Issue2 <> '') AND (Issue2 IS NOT NULL) ORDER BY IssueUnique

OPEN issueList
FETCH NEXT FROM issueList
INTO @issueID
set @RowNum = 0
WHILE @@FETCH_STATUS = 0
BEGIN
  set @issue_key = @issueID
  declare issueCount cursor for
  select count(issue_key) as ISSUE_WEIGHT from tbl_jobs where issue_key = @issueID
  open issueCount
  FETCH NEXT FROM  issueCount INTO @ISSUE_WEIGHT
  Close issueCount
  set @issue_weight = @ISSUE_WEIGHT
  insert into tbl_issue_weight values (@issue_key, @issue_weight)
  FETCH NEXT FROM issueList
    INTO @issueID
End
Close issueList
DEALLOCATE issueList
select top 5 issue_key, issue_weight  from tbl_issue_weight  order by issue_weight DESC
drop table tbl_issue_weight

otherwise you can get the logic from that.
Let me know if you need help converting that the CF.

I didn't test the above code... sorry no simple way to setup a SQL2000 server from here.

I hope that helps.

Travis Hawkins
BeachBum Software
travis@cfm2asp.com

http://www.beachbumsoftware.com

r937 · Mar 22, 2005

could you please explain just a wee bit more about the "top 5" you want from the jobs table

i don't understand how this relates to your long explanation of unique issues

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

cfdeveloper · Mar 23, 2005

Hello Travid and Rudy, and thanks for replying to my thread. Sorry for not getting back yesterday. Just got in to work.

Rudy, hopefully this will make it clearer. I want the query to return the 5 most frequently used issues in a date range that customes have selected when logging jobs.

I'm working on a job/call logging system for a support desk. The support desk log jobs for customers. The job_issues table contains information more specific to the job. For example if the customer calls in to say that there is a hardware problem, the support desk user would log the job under Fault (Issue1) Hardware (Issue2). The job_issues
table has 3 columns, Issue1, Issue2 and IssueUnique. IssueUnique being a primary key column. Issue1 and Issue2 are populated in a drop-list (select box) in the job logging screen. All information relating to the customer and the job are stored in the jobs table which too has a column called IssueUnique which I guess is a foreign key.

I want the query to return the 5 most frequently used issues in a date range that customes have selected when logging jobs. Hope this is making sense

Best regards
cfcoder

r937 · Mar 23, 2005

i still don't see how the two tables are related insofar as the request for "top 5" is concerned

i know they're related by Issueunique, but i'm not clear on which one is the primary and which one is the foreign

which table has the date range?

if it's the jobs table, do you want the top 5 from the jobs table?

and if so, what does the jobissues table have to do with it?

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

cfdeveloper · Mar 23, 2005

>>i know they're related by Issueunique, but i'm not clear on which one is the primary and which one is the foreign

The IssueUnique column is the primary key in job_issues table. The jobs table too has a column called IssueUnique which is a foreign key. When logging a new job for the customer, the IssueUnique value from the job_issues table is inserted in the IssueUnique column in the jobs table.

>>which table has the date range?

The jobs table has a column that stores the log date time (field - logdatetime).

>>if it's the jobs table, do you want the top 5 from the jobs table?

I want the top 5 (most frequently used issues (Issue1 and Issue2 from the job_issues table) in a date range that customes have selected when logging jobs.

Summary: I want the query to join the two tables (jobs.IssuesUnique = job_issues.IssueUnique) and return the 5 most frequently used issues (from the issues table) for logging jobs for customers between a date range Ex: 2005/02/01 - 2005/02/28

Hope this is making sense
regards,
cfcoder

r937 · Mar 23, 2005

Hope this is making sense

yes, albeit slowly

okay, suppose you had a query with a join so that we could pull out all the rows of the job_issues table for the selected date range in the jobs table

which "top 5" do you want -- the top 5 pairs of Issue1/Issue2, or do you want Issue1 and Issue2 combined to choose the top 5 issues from both?

and "top 5" here means COUNT(), right?

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

cfdeveloper · Mar 23, 2005

Rudy, the explaination given in this thread is right

Regards
cfcoder

r937 · Mar 23, 2005

wow, the other thread just disappeared, eh

okay, which top 5 do you want -- pairs, or individual issues?

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

tlhawkins · Mar 23, 2005

Hey,

I think the concept is clear enough. cfcoder needs to know what users have been complaining about the most.

So first we collect the complete issues from the issues table then search the jobs table with a count aggregate to find out how many times a job has complained about that issue. Then we sort the results and grab the top 5

we're basicaly making a top 5 worst issues list.

no problem... just takes a little programming.

Travis Hawkins
BeachBum Software
travis@cfm2asp.com

http://www.beacbumsoftware.com

r937 · Mar 23, 2005

yeah, but why do any programming at all?

unless, of course, you're a programmer and enjoy writing code for things that sql can do for you ;-)

so what's your interpretation -- top 5 issue1/issue2 pairs, or top 5 unique issues where an issue is either issue1 or issue2?

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

cfusiondeveloper · Mar 23, 2005

I don't know what happenned. I've had to create a new login because I'm having probelms with my other login. I don't know why the other thread was deleted or why my reply to your question Rudy has dissappeared.

To answer your question

>>which "top 5" do you want -- the top 5 pairs of Issue1/Issue2, or do you want Issue1 and Issue2 combined to choose the top 5 issues from both?

I want the query to return atleast 4 columns ie
Issue1, Issue2, IssueUnique and IssueCount, something like this:

Issue1 Issue2 IssueUnique IssueCount
Fault Hardware 112 200 (this being the count no of times the IssueUnique value has been used in the a date range (ex: 2005/02/01 - 2005/02/28)

I want the top 5 (most frequently used) IssueUnique values in in a date range

Ex:

Issue1 Issue2 IssueUnique IssueCount
Fault Hardware 112 200
Advice General 159 170
Administration Move 104 130
Maintenance Equipment 158 100
Project IT 34 40

>>??and "top 5" here means COUNT(), right?
yes the issueCount

Regards,
cfcoder

tlhawkins · Mar 23, 2005

hey Rudy,

I think if you look at cfcoders original query to grab his issues it shows that he wants issues as pairs

SELECT IssueUnique
FROM job_issues
WHERE (Issue1 <> '') AND (Issue1 IS NOT NULL)
AND (Issue2 <> '') AND (Issue2 IS NOT NULL)
ORDER BY IssueUnique

this requires that both issues be filled in at which point we are only returning the "IssueUnique" so at that point they are paired and we've never been asked to seperate them. If you look at his sample values "Issue1" is like a Category and "issue2" is a Subcategory but they are taken together. The database setup could probably have been more ideal, but this is what we're working with.

Now... if anyone can get that done with one SQL query I'll give them a star myself.

Otherwise the above T-SQL will probably do the trick, or we can do it in CFM.

Either way, lets get cfcoder's problem solved.

Travis Hawkins
BeachBum Software
travis@cfm2asp.com

http://www.beacbumsoftware.com

cfusiondeveloper · Mar 23, 2005

I just want to mention that each pair (Issue1, Issue2) has a UNIQUE IssueUnique value.

r937 · Mar 23, 2005

Code:

select top 5
       IssueUnique
  from (
       select JI.IssueUnique
         from jobs as J
       inner
         join job_issues as JI
           on J.IssueUnique 
            = JI.IssueUnique 
        where J.Issue1 > ''
          AND J.Issue2 > ''
          and JI.logdatetime
              between '2005-02-01'
                  and '2005-02-28'
       ) as DT
group 
    by IssueUnique
order
    by count(*) desc

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

tlhawkins · Mar 23, 2005

rudy rocks.

not to steal any thunder but if you adjust...

select JI.IssueUnique, count(*) as Weight

you will have a field with the actual count of times the issue has been used.

I would give you 2 stars if I could.

Travis Hawkins
BeachBum Software
travis@cfm2asp.com

http://www.beacbumsoftware.com

cfusiondeveloper · Mar 23, 2005

that does rock but I'm afraid I also want to return Issue1, Issue2 and Count
At the minute it returns just the IssueUnique column

Code:

IssueUnique
17
16
53
26
44

If you could also return the issue 1 and 2 names for the returned IssueUnique value that would be fantastic

Regards,
cfcoder

r937 · Mar 23, 2005

i just realized that if "logdatetime" actually contains datetime values (with a non-zero time component) then you should replace

Code:

and JI.logdatetime
    between '2005-02-01'
        and '2005-02-28'

with

Code:

and JI.logdatetime >= '2005-02-01'
and JI.logdatetime  < '2005-03-01'

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

r937 · Mar 23, 2005

Code:

select * 
  from job_issues
 where IssueUnique
    in (
select top 5
       IssueUnique
  from (
       select JI.IssueUnique
         from jobs as J
       inner
         join job_issues as JI
           on J.IssueUnique 
            = JI.IssueUnique 
        where J.Issue1 > ''
          AND J.Issue2 > ''
          and JI.logdatetime
              between '2005-02-01'
                  and '2005-02-28'
       ) as DT
group 
    by IssueUnique
order
    by count(*) desc  
) as dt2

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

cfusiondeveloper · Mar 23, 2005

I get an SQL Server error message

Incorrect syntax near the keyword 'AS'

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

query that returns the top 5 records (most frequently used)kindly read 1

Programmer

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Programmer

Programmer

Technical User

Programmer

Programmer

Technical User

Technical User

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor