Views, CTE's and Stored Procedures

Question

I'm trying to get the below code into a view. Can it be done? The aim is to use the output to join to another code, but this below needs to update every time the main code is run. (Hence I was trying to use a view)

NOTE: I can't use temp tables in the final code as the software it's loaded into does not like temp tables so I need this to run independently but also be able to join to another code and run every time the main code is executed.

DROP TABLE IF EXISTS #Cases
DROP TABLE IF EXISTS #Final_Data

Select c.[CaseID]
      ,b.dispositionCode
INTO #Cases
FROM [XXXX].[dbo].[XXXX] c
left join [XXXX].[dbo].[XXXX] b on c.FinalDx = b.dispositionCode
left join [XXXX].[dbo].[XXXX] LTLA with (nolock) on C.mappedLSOA = LTLA.LSOA11CD
where SiteTypeID = 5 and LTLA.IsLatest = 1
and CallDate between (Select min(calldate) from [XXXX].[dbo].[XXXX]) AND (Select max(calldate) from [XXXX].[dbo].[XXXX]) 
  

  Select caseId, Include
  into #Final_Data
  From
  (
  Select a.*
        ,case when (Questions < 3 and InOut <5 and dispositionCode is NULL) then 'N' 
              when (Questions is NULL and InOut <5 and dispositionCode is NULL) then 'N' else 'Y' end as [Include]
        ,Questions
        ,InOut
  from #Cases a
  left join (select g.CaseId
            ,count(distinct quid) as Questions
            ,DATEDIFF(SECOND,min(timein),max(timeout)) as InOut
            from
            (select caseID, quid, timein, timeout
            from [XXXX].[dbo].[XXXX] with (NOLOCK) 
            where TimeIn >= DATEADD(day,-5, (Select min(calldate) from [XXXX].[dbo].[XXXX]))
            and ActionId >1 and quId like 'Tx%'
            and AnswerNo is not null 
            and caseID IN (Select CaseID from #Cases)) g
            group by
            g.CaseId) e on a.caseId = e.CaseId ) f
    
Select * from #Final_Data

Answer 1

As you say in your title, CTEs are the solution for this problem.

You can convert those two temporary tables into two CTEs, so the resulting query can now be used within a view.

with Cases as 
(
  Select c.[CaseID]
        ,b.dispositionCode
  FROM [XXXX].[dbo].[XXXX] c
    left join [XXXX].[dbo].[XXXX] b on c.FinalDx = b.dispositionCode
    left join [XXXX].[dbo].[XXXX] LTLA with (nolock) on C.mappedLSOA = LTLA.LSOA11CD
  where SiteTypeID = 5 and LTLA.IsLatest = 1
        and CallDate between (Select min(calldate) from [XXXX].[dbo].[XXXX]) AND (Select max(calldate) from [XXXX].[dbo].[XXXX]) 
),
Final_Data as (
  Select caseId, Include
  From
  (
  Select a.*
        ,case when (Questions < 3 and InOut <5 and dispositionCode is NULL) then 'N' 
              when (Questions is NULL and InOut <5 and dispositionCode is NULL) then 'N' else 'Y' end as [Include]
        ,Questions
        ,InOut
  from Cases a
  left join (select g.CaseId
            ,count(distinct quid) as Questions
            ,DATEDIFF(SECOND,min(timein),max(timeout)) as InOut
            from
            (select caseID, quid, timein, timeout
            from [XXXX].[dbo].[XXXX] with (NOLOCK) 
            where TimeIn >= DATEADD(day,-5, (Select min(calldate) from [XXXX].[dbo].[XXXX]))
            and ActionId >1 and quId like 'Tx%'
            and AnswerNo is not null 
            and caseID IN (Select CaseID from #Cases)) g
            group by
            g.CaseId) e on a.caseId = e.CaseId ) f
)    
Select * from Final_Data