Here is a connectwise SQL query that pulls the counts for all opened tickets that were also closed within the same day for yesterday. It then calculates the percentage of tickets closed in the same day. This makes a great counter for ConnectSmart or your own graphs and counters. There is 2 places in the SQL query that can be editied to allow a different Board Name. Find the ‘%Teir 1%’ and place in your board name to query for that board.
declare
@r1 float, @r2 float, @r3 float
set
@r1 =(SELECTCOUNT(1)FROM [dbo].[v_rpt_Service]
WHERE [dbo].[v_rpt_Service].[date_entered] >=dateadd(day,datediff(day,1,GETDATE()),0)
and [dbo].[v_rpt_Service].[date_entered] <dateadd(day,datediff(day,0,GETDATE()),0)
and [dbo].[v_rpt_Service].[Board_Name] LIKE‘%Tier 1%’)
set
@r2 =(SELECTCOUNT(1)FROM [dbo].[v_rpt_Service]
WHERE [dbo].[v_rpt_Service].[date_entered] >=dateadd(day,datediff(day,1,GETDATE()),0)
and [dbo].[v_rpt_Service].[date_entered] <dateadd(day,datediff(day,0,GETDATE()),0)
and [dbo].[v_rpt_Service].[Board_Name] LIKE‘%Tier 1%’
and [dbo].[v_rpt_Service].[date_closed] >=dateadd(day,datediff(day,1,GETDATE()),0)
and [dbo].[v_rpt_Service].[date_closed] <dateadd(day,datediff(day,0,GETDATE()),0)
)
set
@r3 = @r2 / @r1 * 100 Select‘Percent’= @r3,‘Closed’= @r2,‘Total’= @r1