Sanky Sanky - 2 months ago 9
SQL Question

How to compare temporary columns created from select in order to calculate SLA?

This is the whole query that I have now, the first part seems to be fine but starting from "Y as" it shows all the columns underlined with red. I have marked the statements in bold which shows a red underline. I need to calculate the percentage of SLA which I was not able to figure out with this query. The business hours are SAturday-sunday from 4:30 am to 5:30pm and Mon-Friday from 4:30am to 10:30pm.

SELECT
[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' ,
sum (CASE
WHEN (DATEDIFF(MINute,report_date,FIRST_RESPONSE_DATE) <= '10' )
THEN 1
ELSE 0
END) AS "SLA time_to_accept Status",
DATEDIFF(MINUTE,[JOB_TICKET].[FIRST_RESPONSE_DATE],[JOB_TICKET].[CLOSE_DATE]) as 'time_to_resolve' ,
(CASE
when PRIORITY_TYPE_NAME='low'
then sum(case
when DATEDIFF(MINUTE,[JOB_TICKET].[FIRST_RESPONSE_DATE],[JOB_TICKET].[CLOSE_DATE]) <= '960'
then 1
else 0
end )
when PRIORITY_TYPE_NAME='medium'
then sum(case
when DATEDIFF(MINUTE[JOB_TICKET].[FIRST_RESPONSE_DATE],[JOB_TICKET].[CLOSE_DATE]) <= '480'
then 1
else 0
end )
when PRIORITY_TYPE_NAME='high'
then sum(case
when DATEDIFF(MINUTE,[JOB_TICKET].[FIRST_RESPONSE_DATE],[JOB_TICKET]. [CLOSE_DATE]) <= '120'
then 1
else 0
end )
when PRIORITY_TYPE_NAME='Urgent'
then sum(case
when DATEDIFF(MINUTE,[JOB_TICKET].[FIRST_RESPONSE_DATE],[JOB_TICKET]. [CLOSE_DATE]) <= '60'
then 1
else 0
end)
end) as "Closed within SLA" FROM [SWHD01].[dbo].[JOB_TICKET] INNER JOIN [SWHD01].[dbo].[STATUS_TYPE] ON [JOB_TICKET].[STATUS_TYPE_ID] =[STATUS_TYPE].[STATUS_TYPE_ID] INNER JOIN [SWHD01].[dbo].[PROBLEM_TYPE] ON [JOB_TICKET].[PROBLEM_TYPE_ID] = [PROBLEM_TYPE].[PROBLEM_TYPE_ID] INNER JOIN [SWHD01].[dbo].[PRIORITY_TYPE] ON [JOB_TICKET].[PRIORITY_TYPE_ID] = [PRIORITY_TYPE].[PRIORITY_TYPE_ID] where REPORT_DATE is not null and DATEDIFF(MINUTE,[JOB_TICKET].[FIRST_RESPONSE_DATE],[JOB_TICKET].[CLOSE_DATE]) is not null and DATEDIFF(MINUTE,[JOB_TICKET].[FIRST_RESPONSE_DATE],[JOB_TICKET].[CLOSE_DATE]) > 0 group by 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]

Answer
  • Since you need to reuse time_to_accept and time_to_resolve, use a CTE so you don't have to repeat those long DATEDIFF expressions.
  • Use another CTE so you don't have to repeat the long CASE expressions for time_to_accept andtime_to_resolve.
  • Finally, you completely left out the FROM statement from your primary SELECT; I've added FROM SomeTable as a placeholder.

.

WITH X AS (
SELECT
    [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'
FROM SomeTable
), 
Y AS (
SELECT
    X.JOB_TICKET_ID,
    X.REPORT_DATE,
    X.FIRST_RESPONSE_DATE,
    X.CLOSE_DATE,
    X.LAST_UPDATED,
    X.Ticket_Priority,
    X.time_to_accept,
    X.time_to_resolve,
    case when time_to_accept <= 10 then 1 else 0 end 
        AS "SLA time_to_accept Status",
    case when time_to_resolve <= 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 "Closed within SLA"
)

SELECT 
    Y.JOB_TICKET_ID,
    Y.REPORT_DATE,
    Y.FIRST_RESPONSE_DATE,
    Y.CLOSE_DATE,
    Y.LAST_UPDATED,
    Y.Ticket_Priority,
    Y.time_to_accept,
    Y.time_to_resolve,
    Y.[SLA time_to_accept Status],
    Y.[Closed within SLA],
    case WHEN [SLA time_to_accept Status]=1 and [Closed within SLA]=1
      then 1
      else 0
      end
        AS [SLA Compliant]
FROM Y