Running Individuals Reports

Question

I am trying to make a query to run reports. I am trying to have it search a range of dates and check to see how much work a user has done. Closed_by is the field that would show the users number meaning they completed the work and op_id means they submitted the work. Is there a way I can get a breakdown to report what each individual has done?

<cftry> 

<cfquery name="received" datasource="dns">
        Select * 
        FROM dbo.Dealer_Track_Work 
        WHERE Date_Received between <cfqueryparam value="10/13/2015" /> 
        AND <cfqueryparam value="10/26/2015" />
        WHERE Closed_by <cfqueryparam value="192" /> 
        AND op_id <cfqueryparam value="192" />
</cfquery> 

<cfdump var="#received#" label="received">

<!---<cfquery name="users" dbtype="query"> 
      Select *  
      FROM received     
      WHERE Closed_by <cfqueryparam value="192" />
        AND op_id <cfqueryparam value="192" />
</cfquery> 

<cfdump var="#users#" label="users">--->

<cfcatch type="any"> 
        <cfset result.error = CFCATCH.message > 
        <cfset result.detail = CFCATCH.detail > 
    </cfcatch> 
</cftry>

rodmunera · Accepted Answer · 2015-11-05T21:59:20.440

3

You can ditch your where clause and replace it with a group by clause. You should also list your columns instead of using select *

  Select op_id
    ,count(op_id) as itemsOpened
    ,count(closed_by) as itemsClosed
  FROM dbo.Dealer_Track_Work 
  WHERE Date_Received between <cfqueryparam value="10/13/2015" /> 
    AND <cfqueryparam value="10/26/2015" />
    AND op_id IN (<cfqueryparam value="192,229,123" list="true">)
  GROUP BY op_id
  ORDER BY op_id ASC

Your query might also need some sort of aggregation so you know what your definition of work.

Edit: modify to get open and closed items, added filtering

edited Nov 05 '15 at 21:59

answered Nov 05 '15 at 21:30

rodmunera

542
1
5
13

Just updated it adding columns for items opened, items closed and an AND clause to let you provide a list of op_id to filter by – rodmunera Nov 05 '15 at 21:45
Yes, it's not really much of a coldFusion question (except for the cfqueryparams), more of a sql questions. I also made a large assumption that the database you were using is sql server, but I think the syntax also works for mySQL – rodmunera Nov 05 '15 at 21:54
You can sort by any column, just add a SORT BY clause after the GROUP BY. I've updated the answer – rodmunera Nov 05 '15 at 22:00
While it is technically optional, you should always [specify a cfsqltype when using cfqueryparam](http://stackoverflow.com/questions/9778723/is-using-cfsqltype-good-practice). – Leigh Nov 07 '15 at 06:40

Running Individuals Reports

1 Answers1