Sanky Sanky - 2 months ago 5
SQL Question

I want to write a recursive query which can check for parentid, childid and return all the values with their grandchildren

That's the ids with me:

PARENT_ID PROBLEM_TYPE_ID PROBLEM_TYPE_NAME
12 1 Server/Hardware
NULL 2 IT General/Other
NULL 3 Phone/Voicemail
NULL 4 HR
10 5 CMS Applications
NULL 12 Incident (Technical Issues)
12 13 CMS Applications


thats the query i am using to calculate SLA and need to add one query to grab all the parent_id and childids so that I dont miss out on any tickets

SELECT
--[PROBLEM_TYPE].[PRIORITY_TYPE_ID]
PROBLEM_TYPE.PARENT_ID
,[PROBLEM_TYPE].[PROBLEM_TYPE_ID]
,[PROBLEM_TYPE_NAME]
,[JOB_TICKET].[JOB_TICKET_ID]
,[JOB_TICKET].[REPORT_DATE]
,[JOB_TICKET].[FIRST_RESPONSE_DATE]
,[JOB_TICKET].CLOSE_DATE
,[JOB_TICKET].LAST_UPDATED
,[PRIORITY_TYPE].[PRIORITY_TYPE_NAME] AS 'Ticket_Priority',DATEDIFF(MINute, report_date,FIRST_RESPONSE_DATE) as 'time_to_accept', DATEDIFF(MINUTE,[JOB_TICKET].[FIRST_RESPONSE_DATE],[JOB_TICKET].[CLOSE_DATE]) as 'time_to_resolve',case WHEN DATEDIFF(MINute, report_date,FIRST_RESPONSE_DATE) <= 10 and DATEDIFF(MINUTE,[JOB_TICKET].[FIRST_RESPONSE_DATE],[JOB_TICKET].[CLOSE_DATE]) <= 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 AS [SLA Compliant] FROM [SWHD01].[dbo].[PROBLEM_TYPE] INNER JOIN [SWHD01].[dbo].[Job_ticket] ON [problem_type].[PROBLEM_TYPE_ID] = [job_ticket].[PROBLEM_TYPE_ID] INNER JOIN [SWHD01].[dbo].[PRIORITY_TYPE] ON [PROBLEM_TYPE].[PRIORITY_TYPE_ID] = [PRIORITY_TYPE].[PRIORITY_TYPE_ID]where datediff(month, CLOSE_DATE, getdate()) <= 6 order by REPORT_DATE desc


thats the code I have and I need to incorporate your code with this one to get the correct job_ticket_ids and the output that i am expecting is given below @John Cappelletti

Thats the expected output

Answer

I am assuming your SLA_Compliant fields contains your business logic

-- Create some Base Ticket Data -- I trust your process will produce 
Declare @Ticket table (Job_ticketid int,JOB_PROBLEM_TYPE int, Report_Date Datetime,Close_Date DateTime,time_to_accept int,time_to_resolve int,SLA_Compliant bit)
Insert Into @Ticket values
(1002,527,'2016-09-15 5:42 PM','2016-09-15 7:15 PM',4,89,1),
(1003,528,'2016-09-15 4:12 PM','2016-09-15 4:16 PM',3, 1,1),
(1004,530,'2016-09-15 5:42 PM','2016-09-15 7:15 PM',4,89,1),
(1005,529,'2016-09-15 5:42 PM','2016-09-15 4:16 PM',3, 1,1),
(1006,241,'2016-09-15 5:42 PM','2016-09-15 4:16 PM',3, 1,0)


Declare @Table table (PARENT_ID int,PROBLEM_TYPE_ID int,PROBLEM_TYPE_NAME varchar(50))
Insert into @Table values 
(12  ,1 ,'Server/Hardware'),
(NULL,2 ,'IT General/Other'),
(NULL,3 ,'Phone/Voicemail'),
(NULL,4 ,'HR'),
(10  ,5 ,'CMS Applications'),
(NULL,12,'Incident (Technical Issues)'),
(12  ,13,'CMS Applications'),
(12  ,527,'Alerting'),
(527 ,528,'Interpreter Alerts Not Working'),
(527 ,530,'Nutanix Prism Alters Not Working'),
(527 ,529,'Orion Alerts Not Working'),
(12  ,241,'Call Processing Center')

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   @Table 
     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   @Table 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.R1
      ,A.R2
      ,A.Lvl
      ,A.PROBLEM_TYPE_NAME
      ,Tickets           = Count(*)
      ,Ttl_Accept        = sum(time_to_accept)
      ,Ttl_Resolve       = sum(time_to_Resolve)
      ,Avg_Accept        = avg(time_to_accept)
      ,Avg_Resolve       = avg(time_to_Resolve)
      ,SLA_Compliant     = sum(case when SLA_Compliant=1 then 1 else 0 end)
      ,SLA_Non_Compliant = sum(case when SLA_Compliant=0 then 1 else 0 end)
      ,Percent_Compliant = cast((sum(case when SLA_Compliant=1 then 1 else 0 end)*100.0)/count(*) as money)
 From  cteFinalHier A
 Join  (Select _R1=B.R1,* from @Ticket A Join cteFinalHier B on (A.JOB_PROBLEM_TYPE=B.PROBLEM_TYPE_ID)) B on B._R1 between A.R1 and A.R2
 Group By 
       A.R1
      ,A.R2
      ,A.Lvl
      ,A.PROBLEM_TYPE_NAME
 Order By R1

Returns

enter image description here