encapsulating DECLARATIONS, UNION ALL, and SELECTS into CTE

• DaveBriCam

  SSCarpal Tunnel

  Points: 4298

  More actions

  August 18, 2021 at 5:53 pm

  #3921501

  I ended up rolling all the code below into a stored procedure and it works this way:

  USE [A2WH]
  GO
  
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  CREATE PROCEDURE [dbo].[sp_GetDailyHoursByAgent]
  @keyword varchar(50)
  AS
  BEGIN
  WITH prDates_CTE 
      AS (
  
  SELECT dt.PRStartDate
        ,dt.PREndDate
    FROM a2hr.payroll.fnGetPrevPeriodYear(getdate()) py
  
  CROSS APPLY (SELECT TOP 1
                     PRStartDate = pp.startDate
                    ,PREndDate = pp.endDate
               FROM a2hr.payroll.PayPeriods pp
               WHERE pp.[Year] = py.[Year]
                 AND pp.Period = py.Period
              ) dt
         )
  
      ,combinedData_CTE
  
      AS (
  
  SELECT [Date] AS [Date]
        ,[DOMAIN] AS [Domain]
        ,[AGENT] AS [Agent]
        ,cast([AGENT EMAIL] as varchar) AS [AgentInfo]
        ,[LOGIN TIMESTAMP] AS [Login Timestamp]
        ,[LOGOUT TIMESTAMP] AS [Logout Timestamp]
        ,dbo.fnGetDecimalTime([LOGIN TIME]) AS [Time]
  FROM [a2wh].[dbo].[AgentHours] ah
  INNER JOIN prDates_CTE pd ON ah.[Date] BETWEEN pd.PRStartDate AND pd.PREndDate
  
  UNION ALL
  
  SELECT [DATE] AS [Date]
        ,[DOMAIN] AS [Domain]
        ,[Agent Name] AS [Agent]
        ,'' AS [AgentInfo]
        ,'' AS [Login Timestamp]
        ,'' AS [Logout Timestamp]
        ,dbo.fnGetDecimalTime([Total Time]) AS [Time]
  FROM [a2hr].[dbo].[stgOneConvoHrs] och
  INNER JOIN prDates_CTE pd1 ON och.[Date] BETWEEN pd1.PRStartDate AND pd1.PREndDate
  
  )
  
  SELECT date, Agent, AgentInfo, domain, sum(time) as 'total for day'
    FROM combinedData_CTE
   WHERE [Agent] LIKE '%' + @keyword + '%' 
   GROUP BY date, Domain, Agent, AgentInfo
  
  END

  Note this phrase with the single quotes: '%' + @keyword + '%'

  I can successfully run this with:

  EXEC a2wh..sp_GetDailyHoursByAgent @keyword = 'Fenner'