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">
WHERE (field_inspector.companyid=1) and (field_inspector.acctdisabled='NO');
Problem is here....