I want to use a function similar to COUNTIFS function in SQL
This query is supposed to give me a count of tickets that met SLA(tickets that have been accepted in less than 10 minutes and have been resolved under a particular time depending on the priorities)
The SLA section in my query gives me an error
----------------------------SLA----------------------------
,CAST ((sum(case WHEN datediff(minute,(select max(ENTRY_DATE) from history_entry where a.job_Ticket_ID = HISTORY_ENTRY.job_Ticket_ID and HISTORY_ENTRY.ENTRY_TEXT like ('%Escalated to %%NOC%Level 2%%') and HISTORY_ENTRY.ENTRY_TEXT not like ('%Escalated to %%NOC%Level 1%')),
(select max(ENTRY_DATE) from history_entry where a.job_Ticket_ID = HISTORY_ENTRY.job_Ticket_ID and HISTORY_ENTRY.ENTRY_TEXT like ('%Assigned to %%NOC%Level 2%') and HISTORY_ENTRY.ENTRY_TEXT not like ('%Assigned to %%NOC%Level 1%'))) <= 10 and
datediff(MINUTE,(select max(ENTRY_DATE) from history_entry where a.job_Ticket_ID = HISTORY_ENTRY.job_Ticket_ID and HISTORY_ENTRY.ENTRY_TEXT like ('%Escalated to %%NOC%Level 2%%') and HISTORY_ENTRY.ENTRY_TEXT not like ('%Escalated to %%NOC%Level 1%')),
(select max(ENTRY_DATE) from history_entry where a.job_Ticket_ID = HISTORY_ENTRY.job_Ticket_ID and HISTORY_ENTRY.entry_text like '%Status changed from % to closed%%')) <= case PRIORITY_TYPE_NAME
WHEN 'low' then 960
WHEN 'medium' then 480
WHEN 'high' then 120
WHEN 'Urgent' then 60
end
then 1
else 0
end)*100.0)/COUNT(*) as money) as Percent_Compliant
I tried the above query but got an error
(Msg 130, Level 15, State 1, Line 94
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.)
I am expecting the code to return 100% for tickets that have met SLA and below given is my full code:
Declare @Top int = null --<< Sets top of Hier Try 12
Declare @Nest varchar(25) ='|-----' --<< Optional: Added for readability
;with cteHB (Seq,Path,PROBLEM_TYPE_ID,PARENT_ID,Lvl,PROBLEM_TYPE_NAME) as (
Select Seq = cast(1000+Row_Number() over (Order by PROBLEM_TYPE_NAME) as varchar(500))
,Path = cast(PROBLEM_TYPE_ID as varchar(500))
,PROBLEM_TYPE_ID
,PARENT_ID
,Lvl=1
,PROBLEM_TYPE_NAME
From problem_type
Where IsNull(@Top,-1) = case when @Top is null then isnull(PARENT_ID,-1) else PROBLEM_TYPE_ID end
Union All
Select Seq = cast(concat(cteHB.Seq,'.',1000+Row_Number() over (Order by cteCD.PROBLEM_TYPE_NAME)) as varchar(500))
,Path = cast(concat(cteHB.Path,'.',cteCD.PROBLEM_TYPE_ID) as varchar(500))
,cteCD.PROBLEM_TYPE_ID
,cteCD.PARENT_ID,cteHB.Lvl+1
,cteCD.PROBLEM_TYPE_NAME
From problem_type cteCD
Join cteHB on cteCD.PARENT_ID = cteHB.PROBLEM_TYPE_ID)
,cteR1 as (Select Seq,PROBLEM_TYPE_ID,R1=Row_Number() over (Order By Seq) From cteHB)
,cteR2 as (Select A.Seq,A.PROBLEM_TYPE_ID,R2=Max(B.R1) From cteR1 A Join cteR1 B on (B.Seq like A.Seq+'%') Group By A.Seq,A.PROBLEM_TYPE_ID )
,cteFinalHier as (
Select B.R1
,C.R2
,A.PROBLEM_TYPE_ID
,A.PARENT_ID
,A.Lvl
,PROBLEM_TYPE_NAME = Replicate(@Nest,A.Lvl-1) + A.PROBLEM_TYPE_NAME
,A.Seq -- < Included for Illustration
,A.Path -- < Included for Illustration
From cteHB A
Join cteR1 B on A.PROBLEM_TYPE_ID=B.PROBLEM_TYPE_ID
Join cteR2 C on A.PROBLEM_TYPE_ID=C.PROBLEM_TYPE_ID
)
Select A.Job_ticket_id
,[Problem_Type_Name(Parent)]=C.PROBLEM_TYPE_NAME
,[Problem_Type_Name(Child)] =B.PROBLEM_TYPE_NAME
,HISTORY_ENTRY.ENTRY_DATE
,case when HISTORY_ENTRY.ENTRY_TEXT like max('%Assigned to %%NOC%Level 2%') and HISTORY_ENTRY.ENTRY_TEXT not like max('%Assigned to %%NOC%Level 1%')
or HISTORY_ENTRY.ENTRY_TEXT like max('%Escalated to %%NOC%Level 2%%') and HISTORY_ENTRY.ENTRY_TEXT not like max('%Escalated to %%NOC%Level 1%')
or HISTORY_ENTRY.ENTRY_TEXT like max('%Status changed from % to hold%%')
or HISTORY_ENTRY.ENTRY_TEXT like max('%Status changed from %Hold to %%')
or HISTORY_ENTRY.ENTRY_TEXT like max('%Status changed from % to Resolved%%')
or HISTORY_ENTRY.ENTRY_TEXT like max('%Status changed from %Resolved to %%')
or HISTORY_ENTRY.ENTRY_TEXT like max('%Status changed from % to Closed%%')
or HISTORY_ENTRY.ENTRY_TEXT like max('%Ticket Type changed%')
or HISTORY_ENTRY.ENTRY_TEXT like max('%Reopen%')
then HISTORY_ENTRY.ENTRY_TEXT end as 'History Entry'
,DATEDIFF(MINUTE, (select max(ENTRY_DATE) from history_entry where a.job_Ticket_ID = HISTORY_ENTRY.job_Ticket_ID and HISTORY_ENTRY.entry_text like '%Status changed from % to hold%%'),
(select max(ENTRY_DATE) from history_entry where a.job_Ticket_ID = HISTORY_ENTRY.job_Ticket_ID and HISTORY_ENTRY.entry_text like '%Status changed from %Hold to %%')) as 'hold time'
,datediff(minute,(select max(ENTRY_DATE) from history_entry where a.job_Ticket_ID = HISTORY_ENTRY.job_Ticket_ID and HISTORY_ENTRY.ENTRY_TEXT like ('%Escalated to %%NOC%Level 2%%') and HISTORY_ENTRY.ENTRY_TEXT not like ('%Escalated to %%NOC%Level 1%')),
(select max(ENTRY_DATE) from history_entry where a.job_Ticket_ID = HISTORY_ENTRY.job_Ticket_ID and HISTORY_ENTRY.ENTRY_TEXT like ('%Assigned to %%NOC%Level 2%') and HISTORY_ENTRY.ENTRY_TEXT not like ('%Assigned to %%NOC%Level 1%'))) as 'Time to Accept SLA' ----> (this is time difference captured from the history details of ticket when it was first Escalated to level 2 till the time it was accepted by technician in Level 2)
,datediff(minute,(select max(ENTRY_DATE) from history_entry where a.job_Ticket_ID = HISTORY_ENTRY.job_Ticket_ID and HISTORY_ENTRY.ENTRY_TEXT like ('%Escalated to %%NOC%Level 2%%') and HISTORY_ENTRY.ENTRY_TEXT not like ('%Escalated to %%NOC%Level 1%')),
(select max(ENTRY_DATE) from history_entry where a.job_Ticket_ID = HISTORY_ENTRY.job_Ticket_ID and HISTORY_ENTRY.entry_text like '%Status changed from % to resolved%%')) as 'Escalated to Resolved time' ----> (this is time difference captured from the history details of ticket when it was first Escalated to level 2 till the time its status changed to resolved)
,datediff(MINUTE,(select max(ENTRY_DATE) from history_entry where a.job_Ticket_ID = HISTORY_ENTRY.job_Ticket_ID and HISTORY_ENTRY.ENTRY_TEXT like ('%Escalated to %%NOC%Level 2%%') and HISTORY_ENTRY.ENTRY_TEXT not like ('%Escalated to %%NOC%Level 1%')),
(select max(ENTRY_DATE) from history_entry where a.job_Ticket_ID = HISTORY_ENTRY.job_Ticket_ID and HISTORY_ENTRY.entry_text like '%Status changed from % to closed%%')) as 'Escalated to Closed time' ----> (this is time difference captured from the history details of ticket when it was first Escalated to level 2 till the time its status changed to closed)
,A.Report_Date
,A.LAST_UPDATED
,STATUS_TYPE.STATUS_TYPE_NAME as 'Ticket Status'
,a.STATUS_TYPE_ID AS 'status Id'
,A.Close_Date
,A.TECH_GROUP_ID
,isnull(tech.lAST_NAME,'') + ' ' +isnull(Tech.FIRST_NAME,'') [Assigned Tech]
,TECH_GROUP_LEVEL.LEVEL_NUMBER
,DATEDIFF(MINUTE,a.LAST_UPDATED,getdate()) as 'greater than 72 hours'
,TECH_GROUP.NAME
,PRIORITY_TYPE_NAME
----------------------------SLA----------------------------
,CAST ((sum(case WHEN datediff(minute,(select max(ENTRY_DATE) from history_entry where a.job_Ticket_ID = HISTORY_ENTRY.job_Ticket_ID and HISTORY_ENTRY.ENTRY_TEXT like ('%Escalated to %%NOC%Level 2%%') and HISTORY_ENTRY.ENTRY_TEXT not like ('%Escalated to %%NOC%Level 1%')),
(select max(ENTRY_DATE) from history_entry where a.job_Ticket_ID = HISTORY_ENTRY.job_Ticket_ID and HISTORY_ENTRY.ENTRY_TEXT like ('%Assigned to %%NOC%Level 2%') and HISTORY_ENTRY.ENTRY_TEXT not like ('%Assigned to %%NOC%Level 1%'))) <= 10 and
datediff(MINUTE,(select max(ENTRY_DATE) from history_entry where a.job_Ticket_ID = HISTORY_ENTRY.job_Ticket_ID and HISTORY_ENTRY.ENTRY_TEXT like ('%Escalated to %%NOC%Level 2%%') and HISTORY_ENTRY.ENTRY_TEXT not like ('%Escalated to %%NOC%Level 1%')),
(select max(ENTRY_DATE) from history_entry where a.job_Ticket_ID = HISTORY_ENTRY.job_Ticket_ID and HISTORY_ENTRY.entry_text like '%Status changed from % to closed%%')) <= case PRIORITY_TYPE_NAME
WHEN 'low' then 960
WHEN 'medium' then 480
WHEN 'high' then 120
WHEN 'Urgent' then 60
end
then 1
else 0
end)*100.0)/COUNT(*) as money) as Percent_Compliant
------------------------------JOINS--------------------------------------------------
From JOB_TICKET A
Join cteFinalHier B on A.PROBLEM_TYPE_ID=B.PROBLEM_TYPE_ID
INNER JOIN [SWHD01].[dbo].[PRIORITY_TYPE] ON A.[PRIORITY_TYPE_ID] = [PRIORITY_TYPE].[PRIORITY_TYPE_ID]
INNER JOIN [SWHD01].[dbo].[STATUS_TYPE] ON A.[STATUS_TYPE_ID] = [STATUS_TYPE].[STATUS_TYPE_ID]
inner join [SWHD01].[dbo].TECH_GROUP_LEVEL on A.TECH_GROUP_LEVEL_ID=TECH_GROUP_LEVEL.ID
join TECH_GROUP on TECH_GROUP.ID= TECH_GROUP_LEVEL.tech_group_id
LEFT JOIN TECH on Tech.CLIENT_ID = A.ASSIGNED_TECH_ID
join HISTORY_ENTRY on a.JOB_TICKET_ID=HISTORY_ENTRY.JOB_TICKET_ID
Cross Apply (Select Top 1 * from cteFinalHier Where B.R1 between R1 and R2 and Lvl=1) C
-------------Tickets for the Last 6 months---------------------------------------------------
where datediff(day, A.REPORT_DATE, getdate()) <= 30
and TECH_GROUP.NAME like '%NOC%'
and LEVEL_NUMBER ='2'
Group By C.PROBLEM_TYPE_NAME,a.JOB_TICKET_ID,B.PROBLEM_TYPE_NAME,B.PROBLEM_TYPE_ID, a.REPORT_DATE,a.CLOSE_DATE,SWHD01.dbo.PRIORITY_TYPE.PRIORITY_TYPE_NAME,a.FIRST_RESPONSE_DATE,B.R1,a.LAST_UPDATED,a.STATUS_TYPE_ID,a.TECH_GROUP_ID,TECH_GROUP_LEVEL.LEVEL_NUMBER,TECH_GROUP.NAME,a.ASSIGNED_TECH_ID,HISTORY_ENTRY.ENTRY_DATE,HISTORY_ENTRY.ENTRY_TEXT,HISTORY_ENTRY.TECH_ID,TECH.LAST_NAME,TECH.FIRST_NAME,STATUS_TYPE.STATUS_TYPE_NAME
Order By job_ticket_id desc
