locked
Help with a query RRS feed

  • Question

  • Hi,

    I have the following data-source for Incident resolution performance. Whenever an incident is raised , there are 2 levels of Support , 

    It is analyzed by the team of First level of support and then it goes to Second level of Support which fixes them. Level 2 support comes into picture only when level 1 is done. for example if an incident is raised A, level 1 took 4 days level 2 took 2 days overall completion time is 6 days

    As part of a report, user wants to see whether some incidents are delayed to be Completed/Resolved because of more time taken by Level 1 or Level 2 support. 

    declare @T1 TABLE ( IncidentNo INT Identity ,GroupCode CHAR(1),CreationDT DATE, L1CompletionDT DATE, L2CompletionDT DATE)
    
    
    
    Insert into @T1 values ('A',getdate() - 20, getdate() - 10, getdate() -8)
    Insert into @T1 values ('A',getdate() - 30, getdate() - 25, getdate() -22)
    Insert into @T1 values ('B',getdate() - 60, getdate() - 40, getdate() -23)
    Insert into @T1 values ('B',getdate() - 10, getdate() - 3, getdate() -2)
    Insert into @T1 values ('B',getdate() - 5, getdate() - 3, getdate() -2)
    Insert into @T1 values ('C',getdate() - 20, getdate() - 16, getdate() -2)
    
    ;with cte as (Select *,DATEDIFF(DAY,CreationDT,L1CompletionDT) DaysForL1Processing ,
    DATEDIFF(DAY,L1CompletionDT,L2CompletionDT) DaysForL2Processing from @T1)
    
    select * from cte

    Here's the snaphot of the above code . I have created a derived column for

    DaysForL1Processing

    DaysforL2Processing - date diff between L1Completion and L2Completion

    L2 ProcessingRange


    Now for my reporting purpose , I need an output like below

    --- In continuation with previous code just added cte
    
    , CTE2 AS (select *,case when DaysForL2Processing between 0 and 1 then  '0-1 day'
                  when DaysForL2Processing between 2 and 7 then  '2-7 days'
    			  when DaysForL2Processing between 8 and 14 then  '8-14 days'
    			  when DaysForL2Processing > 14 then  'over 14 days'
    			  end As L2ProcessingRange
    			  from cte)
    
    			  SELECT GROUPCODE,L2ProcessingRange,COUNT(1) Count FROM CTE2 GROUP BY GROUPCODE,L2ProcessingRange ORDER BY GROUPCODE 

    I need a fourth column which should tell me the average DAYS taken by L1 for each row in the last snapshot

    example row 1 should be 

    1- A, 2-7 DAYS, 2 , 7.5 

    ( it means if L2 took 2-7 days  to process for these 2 incidents of Group A, the average days taken by L1 for Group A and these 2 records is 7.5 days ) 

    getting such fourth column is my requirement

    Any suggestions?

    -------------------

    Adding the pic again to show how 7.5 is calculated

    if u see pic1  below the average of days for L1Processing  for groupA and for L2  ( 2-7 days category )

    is (10 + 5 )/2 = 7.5


    

     


    • Edited by Peter8444 Monday, September 26, 2016 7:14 AM
    Monday, September 26, 2016 7:13 AM

All replies

  •  I got by answer by using partition by  but not getting float values . getting 7 , expected 7.5

    declare @T1 TABLE ( IncidentNo INT Identity ,GroupCode CHAR(1),CreationDT DATE, L1CompletionDT DATE, L2CompletionDT DATE)
    
    
    
    Insert into @T1 values ('A',getdate() - 20, getdate() - 10, getdate() -8)
    Insert into @T1 values ('A',getdate() - 30, getdate() - 25, getdate() -22)
    Insert into @T1 values ('B',getdate() - 60, getdate() - 40, getdate() -23)
    Insert into @T1 values ('B',getdate() - 10, getdate() - 3, getdate() -2)
    Insert into @T1 values ('B',getdate() - 5, getdate() - 3, getdate() -2)
    Insert into @T1 values ('C',getdate() - 20, getdate() - 16, getdate() -2)
    
    ;with cte as (Select *,DATEDIFF(DAY,CreationDT,L1CompletionDT) DaysForL1Processing ,
    DATEDIFF(DAY,L1CompletionDT,L2CompletionDT) DaysForL2Processing from @T1)
    --SELECT * FROM CTE
    ,CTE2 AS (select *,case when DaysForL2Processing between 0 and 1 then  '0-1 day'
                  when DaysForL2Processing between 2 and 7 then  '2-7 days'
    			  when DaysForL2Processing between 8 and 14 then  '8-14 days'
    			  when DaysForL2Processing > 14 then  'over 14 days'
    			  end As L2ProcessingRange
    			  from cte)
    
    			  SELECT  GROUPCODE,L2ProcessingRange,AVG(DaysForL1Processing) OVER ( PARTITION  BY GROUPCODE,L2ProcessingRange 
    			  ORDER BY GROUPCODE,L2ProcessingRange ) AverageL1Processing
    			  
    			   FROM CTE2 
    			  
    			

    Monday, September 26, 2016 7:25 AM
  • DATEDIFF returns an integer, so DaysForL1Processing is an integer.  AVG of an integer will return an integer so 7.5 gets truncated to 7.  Cast DaysForL1Processing to float or decimal before doing the AVG, for example

    declare @T1 TABLE ( IncidentNo INT Identity ,GroupCode CHAR(1),CreationDT DATE, L1CompletionDT DATE, L2CompletionDT DATE)
    
    
    
    Insert into @T1 values ('A',getdate() - 20, getdate() - 10, getdate() -8)
    Insert into @T1 values ('A',getdate() - 30, getdate() - 25, getdate() -22)
    Insert into @T1 values ('B',getdate() - 60, getdate() - 40, getdate() -23)
    Insert into @T1 values ('B',getdate() - 10, getdate() - 3, getdate() -2)
    Insert into @T1 values ('B',getdate() - 5, getdate() - 3, getdate() -2)
    Insert into @T1 values ('C',getdate() - 20, getdate() - 16, getdate() -2)
    
    ;with cte as (Select *,DATEDIFF(DAY,CreationDT,L1CompletionDT) DaysForL1Processing ,
    DATEDIFF(DAY,L1CompletionDT,L2CompletionDT) DaysForL2Processing from @T1)
    --SELECT * FROM CTE
    ,CTE2 AS (select *,case when DaysForL2Processing between 0 and 1 then  '0-1 day'
                  when DaysForL2Processing between 2 and 7 then  '2-7 days'
    			  when DaysForL2Processing between 8 and 14 then  '8-14 days'
    			  when DaysForL2Processing > 14 then  'over 14 days'
    			  end As L2ProcessingRange
    			  from cte)
    
    			  SELECT  GROUPCODE,L2ProcessingRange,AVG(Cast(DaysForL1Processing As float)) OVER ( PARTITION  BY GROUPCODE,L2ProcessingRange 
    			  ORDER BY GROUPCODE,L2ProcessingRange ) AverageL1Processing
    			  
    			   FROM CTE2 

    Tom

    • Proposed as answer by Riaon Wednesday, September 28, 2016 3:18 AM
    Monday, September 26, 2016 4:35 PM