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

[cfdeveloper]

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

 

[r937]

near which AS? there's 4 of them

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

 

[cfusiondeveloper]

as far as I can see there are only 2 AS keywords. I think its the second AS statement

) as dt2

 

[cfusiondeveloper]

I'm sorry you are right there are 4 'AS' keywords and I don't know which one is throwing the error. I'm running the sql in SQL Analyser

Server: Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'AS'.

 

[r937]

could you please show your exact query

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

 

[cfusiondeveloper]

Here is the query:

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 JI.Issue1 > ''
		  AND JI.Issue2 > ''
		  and J.logdatetime
			  between '2005-02-01'
				  and '2005-02-28'
	   ) as DT
group 
	by IssueUnique
order
	by count(*) desc  
) as dt2

Its the job_issues table that has the columns Issue1 and Issue2 and the jobs table that has the logdatetime column

Regards,
cfcoder

 

[cfusiondeveloper]

I've cracked it. This works:

SELECT     *
FROM         job_issues
WHERE     (CallSubjectUnique IN
   (SELECT     TOP 5 CallSubjectUnique
   FROM          (SELECT     JI.CallSubjectUnique
     FROM          jobs AS J INNER JOIN
     job_issues AS JI ON J.CallSubjectUnique = JI.CallSubjectUnique
                                                    WHERE      JI.Issue1 > '' AND JI.Issue2 > '' AND J.logdatetime >= '2005-02-01' AND J.logdattim < '2005-03-01') DT
GROUP BY CallSubjectUnique
ORDER BY COUNT(*) DESC))

This returns all the columns in the job_issues table. It doesn't however return the COUNT no of issues found for each record. Can you kindly modify it to return this as well.
Regards
cfcoder

 

[cfusiondeveloper]

I have got it to return the fields I wanted it to. here is the code. Thanks ever so much for your help.

SELECT     TOP 5 IssueUnique, Issue1, Issue2, COUNT(*) AS IssueCount, 
Issue1 + '-' + Issue2 + '-' + CAST(IssueUnique AS CHAR) AS Issue
FROM         (SELECT     JI.IssueUnique, JI.Issue1, JI.Issue2
	FROM          jobs AS J INNER JOIN
	jobs_issue AS JI ON J.IssueUnique = JI.IssueUnique
	WHERE      JI.Issue1 > '' AND JI.Issue2 > '' AND 
	J.logdatetime >= '2005/02/01' AND J.logdatetime <= '2005/02/28') DT
GROUP BY IssueUnique, Issue1, Issue2
ORDER BY COUNT(*) DESC

 

[cfusiondeveloper]

Hi guys, I don't know how to put it to you, I got the spec wrong. I showed the results returned by the query to my team and it was not quite the results my manger was expecting.

I'll try and explain.

At the moment the query returns the following

IssueUnique	Issue1			Issue2			IssueCount	Issue
17			Fault			Software		288			Fault-Software-17                            
16			Fault			Hardware		212			Fault-Hardware-16                            
53			Administration	New				183			Administration-New-53                            
26			Advice			General			123			Advice-General-26                            
44			Maintenance		Equipment		123			Maintenance-Equipment-44

What I didn't explain in my earlier posts is that the job_issues table has a column called Issue3. Here is some sample data from the table where Issue1 is Fault and Issue2 is Hardware

IssueUnique	Issue1	Issue2		Issue3	
16			Fault	Hardware	<NULL>			
43			Fault	Hardware	IT equipment
44			Fault	Hardware	Non-IT equipment

If you notice, the results returned from your query has a row where issue1 is Fault and Issue2 is Hardware and IssueCount is 212. I want the query to return a count of all the jobs logged under Fault Hardware (in this case).

If we take the example above, and say the logged job under "Fault - Hardware - Null" were 20 and the jobs logged under "Fault - Hardware - It Equipment" were 20 and the jobs logged under "Fault - Hardware - Non-IT Equipment" were 20

I would like the query to return the following

Issue1			Issue2			IssueCount	Issue
Fault			  Hardware		60  Fault-Hardware

NOTICE, I have removed the IssueUnique column from the ex above. I don't know if this can be in done in one single query or querires of queries. I hope I am making sense. If not then please let me know
I would apprecaite your help greatly.

Best regards
cfcoder

 

[cfusiondeveloper]

can somebody please help!!

 

[TruthInSatire]

take a deep breath man, you only posted an hour ago... if all else fails you could ask a DB forum since, after all, it is a SQL question.

If you don't ask the right questions, you don't get the right answers. A question asked in the right way often points to its own answer. Asking questions is the ABC of diagnosis. Only the inquiring mind solves problems.

-Quote by Edward Hodnett