First Time Fix Measure Discrepency

[will.good]

Hi,

I created a database direct query to count the number of first time fix rates (just as I wanted this with columns as source type, which I can't do in SM Reporting).

I'm a little confused as to why I am getting different numbers from this query compared to an existing report in SM.

I've uploaded the report definition file and included the query below... anyone have any ideas on what I'm missing and why they are different?

E.g. if I run it for March, I get the below:

From Database Direct 'Analyst':

Yes: 1997

No: 409

From Service Manager Reporting:

Yes: 1584

No: 999

SELECT 
  Replace(
    replace(h_firsttimefix, '1', 'Yes'), 
    '0', 
    'No'
  ) as "First Time Fix", 
  SUM(
    case when h_source_type = "Analyst" THEN 1 END
  ) AS "Analyst", 
  SUM(
    case when h_source_type = "Email" THEN 1 END
  ) AS "Email", 
  SUM(
    case when h_source_type = "Chat" THEN 1 END
  ) AS "Chat", 
  SUM(
    case when h_source_type = "Self Service" THEN 1 END
  ) AS "Self Service", 
  SUM(
    case when h_source_type = "Autoresponder" THEN 1 END
  ) AS "Autoresponder", 
  SUM(
    case when h_source_type = "Request" THEN 1 END
  ) AS "Request", 
  COUNT(h_source_type) AS "Total" 
FROM 
  h_itsm_requests 
where 
  h_firsttimefix is not null 
  and (
    h_datelogged >= '2023-03-01' 
    and h_datelogged <= '2023-03-31 23:59:59'
  ) 
group by 
  h_firsttimefix

 

first-time-fix.report.txt

[Met]

Hey @will.good,

Your SM report might be the issue here, at least when running it against our data. You need to select which column you are counting against - counting/grouping on either of the two produces the same result. I've attached a definition file which should hopefully match the DB direct. Having said that, the SM Report of yours I uploaded produced vastly different numbers to DBD (57 vs 2000 in total). Have a go and see if it works.

Met

first-time-fix.report (1).txt