CODEWITHSUNDEEP
sqlsql-servert-sql
Rajiv
Rajiv

Reputation: 685

Use variable after common table expression

I tried to capture a value derived from CTE (Common Table Expression) Table into a variable. For some reason it doesn't seem to work. Below is the code:

WITH CTE
as
(
    select 
    Case 
        when target_title like '%P1%' then 'P1'
        when target_title like '%P2%' then 'P2'
        when target_title like '%P3%' then 'P3'
        when target_title like '%P4%' then 'P4'
    End as Priority,
    flag,
        case when flag='Response' then Business_Hours_MMTR end as Response,
        case when flag = 'Resolution' then Business_Hours_MMTR end as Resolution
    from Keurig..Response_Resolution
    where [Group] like '%Network%'
and datepart(month, Measurement_Stop) =  datepart(month, Getdate())-1
and (Target_Title like '%P1%' OR Target_Title like '%P2%' OR Target_Title like '%P3%' OR Target_Title like '%P4%')
)
Declare @K4Resp numeric(5,2);
Select @K4Resp = CAST(AVG(Response) as numeric(10,2)) as K4Response from CTE where flag = 'Response' and Priority = 'P4'
group by Priority, flag

Upvotes: 0

Views: 4128

Answers (1)

SqlZim
SqlZim

Reputation: 38043

Move your declaration before the common table expression. Only the statement after the cte can reference the cte. In this case, that was your declare.

declare @K4Resp numeric(5, 2);
with CTE as (
  select 
      case 
        when target_title like '%P1%' then 'P1'
        when target_title like '%P2%' then 'P2'
        when target_title like '%P3%' then 'P3'
        when target_title like '%P4%' then 'P4'
      end as Priority
    , flag
    , case when flag = 'Response' then Business_Hours_MMTR end as Response
    , case when flag = 'Resolution' then Business_Hours_MMTR end as Resolution
  from Keurig..Response_Resolution
  where [Group] like '%Network%'
  and datepart(month, Measurement_Stop) = datepart(month, Getdate()) - 1
  and ( Target_Title like '%P1%'
     or Target_Title like '%P2%'
     or Target_Title like '%P3%'
     or Target_Title like '%P4%'
     )
)
select @K4Resp = CAST(AVG(Response) as numeric(5, 2))
from CTE
where flag = 'Response'
  and Priority = 'P4'
group by 
    Priority
  , flag

Upvotes: 2

Related Questions