[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Cfquery sum of status field

Posted on 2009-04-16
12
Medium Priority
?
317 Views
Last Modified: 2012-05-06
I have a logic and sql question.

I am trying to figure out the approach

I have a table (tblAssignments) that has field (status) that shows a status of where the assignment is. It could be new,Inspected, Closed and so on. This table has an Inspector_id (from field_inspector.f_id) showing who has each assigenment.

 

I am building this report and I created a query of the field_inspector table.

What I am trying to do is output the Inspector name with the count of how many assignments they have broken down to x in new, x in inspected and so on&

This is what im trying to do below.

 

Inspector          New                  Inspected                      Closed

Jeff Coolidge    4                      6                                  113

John Doe          45                     21                                 37

&

TOTALS            49                     27                                 483


<cfquery datasource="shs" name="getadj">
SELECT *
FROM field_inspector
WHERE (field_inspector.companyid=1) and (field_inspector.acctdisabled='NO');
</cfquery>
<cfoutput query="getInsp">
Problem is here....
</cfoutput>

Open in new window

0
Comment
Question by:dudeatwork
  • 7
  • 5
12 Comments
 
LVL 52

Expert Comment

by:_agx_
ID: 24159670
How many statuses do you have, and which database type are you using?
0
 

Author Comment

by:dudeatwork
ID: 24159718
I have these:
New
Called
Inspected
Appointment
In Review
Status
Closed

It's MS Crapcess. Dev DB before moving to SQL Server
0
 
LVL 52

Expert Comment

by:_agx_
ID: 24159842
Okay. So right now does your current query contain _all_ of the information you need and you are just trying to figure out how to display it correctly?  Because if not, I can give you an example that would produce that output.  Though It won't be as elegant as how you would do it in sql server.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:dudeatwork
ID: 24160078
The only query(s) I have is the one above. However, the items listed above are the only types of statuses.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 24160423
For Access, I would do an OUTER join between the two tables:

field_inspector
tblAssignments

So, you would get all inspectors, even those with 0 assignments. Then use a PIVOT to get the counts.  I am assuming the status values are in the "tblAssignments".  But you may have to adjust the query slightly


<cfquery name="getData" datasource="#dsn#">
	TRANSFORM COUNT(*) AS NumberOfAssignments
	SELECT field_inspector.InspectorName
	FROM    field_inspector LEFT JOIN tblAssignments 
			ON field_inspector.f_id = tblAssignments.f_id
	GROUP BY field_inspector.InspectorName
	PIVOT tblAssignments.Status
</cfquery>
 
<!--- get status column names (ie exclude InspectorName column) --->
<cfset statusColumnNames = getData.columnList>
<cfset pos = listFindNoCase(statusColumnNames, "InspectorName")>
<cfif pos>
	<cfset statusColumnNames = listDeleteAt(statusColumnNames, pos)>
</cfif>
 
<table border="1">
<tr>
	<td>Inspector Name</td>
	<!--- display status names --->
	<cfoutput>
	<cfloop list="#statusColumnNames#" index="col">
		<td>#col#</td>
	</cfloop>
	</cfoutput>
</tr>
<cfoutput query="getData">
<tr>
	<td>#InspectorName#</td>
	<cfloop list="#statusColumnNames#" index="col">
		<td>#getData[col][currentRow]#</td>
	</cfloop>
</tr>
</cfoutput>
</table>

Open in new window

0
 
LVL 52

Expert Comment

by:_agx_
ID: 24160462
Since I do not know the exact structure of your tables, here is the psuedo structure for the code above.  You may have to tweak it slightly to match your actual columns

[field_inspector]
Columns:
f_id - (numeric) record id
InspectorName - (text) inspector name

[tblAssignments]
f_id - (numeric) inspector id  
status - (text)   New, Called, etc...

0
 

Author Comment

by:dudeatwork
ID: 24161680
_AGX_, it is putting out some unwanted cols. I have attached a screenshot:

Also, I have been playing with this code but it keeps giving me and error
<cfquery name="getData" datasource="dhs">
    Select f_id,
    [Total] = count(*) ,
    [New] = sum(case when status = 'new' then 1 else 0 end),
    [Inspected] = sum(case when status = 'inspected' then 1 else 0 end),
    [Closed] = sum(case when status = 'closed' then 1 else 0 end)
    From  tblassignments
    Group by f_id
</cfquery>

Gives me this error..
Error Executing Database Query.  
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '[New] = sum(case when status = 'new' then 1 else 0 end)'.  

screenshot.gif
0
 
LVL 52

Expert Comment

by:_agx_
ID: 24161735
Can you post the exact query you used?  I am not seeing extra results on my end.  Also, could you dump the columnList variable and post the results.

Query columns are:  <cfoutput>#getData.columnList#</cfoutput>

Unfortunately, I am pretty sure Access does not support CASE.  That is why I didn't suggest it.  Though it would be fine for MS SQL.
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 2000 total points
ID: 24161790
> I am pretty sure Access does not support CASE

Try using  IIF instead:

Select f_id,
    count(*) AS Total,
    sum(IIF(status = 'new', 1, 0)) AS   [New],
    sum(IIF(status = 'Inspected', 1, 0)) AS [Inspected],
   sum(IIF(status = 'Closed', 1, 0)) AS  [Closed]

    From  tblassignments
    Group by f_id
0
 

Author Comment

by:dudeatwork
ID: 24162299
Now were getting closer. WIll it not pivot?
0
 
LVL 52

Expert Comment

by:_agx_
ID: 24162618
I am not sure what you mean.  Your query doesn't need pivot (ie it use a psuedo-CASE to achieve the same affect).  Though I think PIVOT is better because it is not harcoded.
0
 

Author Closing Comment

by:dudeatwork
ID: 31571050
Thanx _AGX_!!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi, I will be creating today a basic tutorial on how we can create a Mail Custom Function and use it where ever we want. The main advantage about creating a custom function is that we can accommodate a range of arguments to pass to the Function and …
Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Screencast - Getting to Know the Pipeline
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question