will.good Posted April 26 Share Posted April 26 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 Link to comment Share on other sites More sharing options...
Met Posted May 2 Share Posted May 2 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 1 Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now