asked on

Cfquery sum of status field

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

_agx_

How many statuses do you have, and which database type are you using?

dudeatwork

ASKER

I have these:
New
Called
Inspected
Appointment
In Review
Status
Closed

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

_agx_

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.

dudeatwork

ASKER

The only query(s) I have is the one above. However, the items listed above are the only types of statuses.

_agx_

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

_agx_

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

dudeatwork

ASKER

_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

_agx_

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.

ASKER CERTIFIED SOLUTION

_agx_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dudeatwork

ASKER

Now were getting closer. WIll it not pivot?

_agx_

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.

dudeatwork

ASKER

Thanx _AGX_!!