title: SQL Is there a way to use variables in a 'WITH' statement?
body: <p>I have this 'WITH' statement that pulls out data for each day of the week. The code works but it becomes tedious to change when I modify a variable because I have to go through each and every code block. A lot of the code is repeated. I would like to know if there is a way to place the repeated code into a variable and just repeat the variable?</p>
  
  <p>Edit: Thank you so much for the answer! I did not expect to get one.</p>
  
  <pre><code>WITH 
    cteMonday (CTEdate, CTEtime, DayAverage) AS
    (
  select CONVERT(VARCHAR(10), [Datetime], 120) AS Date --extract only the time portion
              ,CONVERT(VARCHAR(5), [Datetime], 108) AS Time  --Extract only the date portion
              ,[High] - [Low] AS hilow 
           from [Trading].[dbo].MicroES
          where DATEPART(dw, Datetime) = 2
          AND CONVERT(VARCHAR(5), [Datetime], 108) = '14:00'
          --AND [Date] &gt;= '2016-07-27'
    ),
      cteTuesday (CTEdate, CTEtime,DayAverage) AS
    (
  select CONVERT(VARCHAR(10), [Datetime], 120) AS Date
              ,CONVERT(VARCHAR(5), [Datetime], 108) AS Time --extract only the time portion
              ,[High] - [Low] AS hilow --Extract only the date portion
           from [Trading].[dbo].MicroES
  where DATEPART(dw, Datetime) = 3
  --AND [Date] &gt;= '2018-07-27'
  --AND [Date] &gt;= '2016-07-27'
    ),
        cteWednesday (CTEdate, CTEtime, DayAverage) AS
    (
  select CONVERT(VARCHAR(10), [Datetime], 120) AS Date
              ,CONVERT(VARCHAR(5), [Datetime], 108) --extract only the time portion
              ,[High] - [Low] AS hilow --Extract only the date portion
           from [Trading].[dbo].MicroES
  where DATEPART(dw, Datetime) = 4
  --AND [Date] &gt;= '2018-07-27'
  --AND [Date] &gt;= '2016-07-27'
    ),
        cteThursday (CTEdate, CTEtime, DayAverage) AS
    (
  select CONVERT(VARCHAR(10), [Datetime], 120) AS Date
              ,CONVERT(VARCHAR(5), [Datetime], 108) AS Time --extract only the time portion
              ,[High] - [Low] AS hilow --Extract only the date portion
           from [Trading].[dbo].MicroES
  where DATEPART(dw, Datetime) = 5
  --AND [Date] &gt;= '2018-07-27'
  --AND [Date] &gt;= '2016-07-27'
    ),
        cteFriday (CTEdate, CTEtime, DayAverage) AS
    (
  select CONVERT(VARCHAR(10), [Datetime], 120) AS Date
              ,CONVERT(VARCHAR(5), [Datetime], 108) AS Time --extract only the time portion
              ,[High] - [Low] AS hilow --Extract only the date portion
           from [Trading].[dbo].MicroES
  where DATEPART(dw, Datetime) = 6
  AND CONVERT(VARCHAR(5), [Datetime], 108) = '14:00'
  --AND [Date] &gt;= '2016-07-27'
    )
  
    select 
    'Monday' AS 'Day',
  AVG(DayAverage) AS 'DayAverage',
      MIN(DayAverage) as min_day,
    MAX(DayAverage) as max_day,
    COUNT(DayAverage) as 'Days'  
     from cteMonday
  
     UNION ALL 
       select 
    'Tuesday' AS 'Day',
    AVG(DayAverage) AS DayAverage,
      MIN(DayAverage) as min_day,
    MAX(DayAverage) as max_day,
    COUNT(DayAverage) as 'Days'  
     from cteTuesday
  
     UNION ALL
       select 
    'Wednesday' AS 'Day',
    AVG(DayAverage) AS 'DayAverage',
      MIN(DayAverage) as min_day,
    MAX(DayAverage) as max_day,
    COUNT(DayAverage) as 'Days'  
     from cteWednesday
  
        UNION ALL
       select 
    'Thursday' AS 'Day',
    AVG(DayAverage) AS 'DayAverage',
      MIN(DayAverage) as min_day,
    MAX(DayAverage) as max_day,
    COUNT(DayAverage) as 'Days'  
     from cteThursday
  
        UNION ALL
       select 
    'Friday' AS 'Day',
    AVG(DayAverage) AS 'DayAverage',
      MIN(DayAverage) as min_day,
    MAX(DayAverage) as max_day,
    COUNT(DayAverage) as 'Days'  
     from cteFriday
  </code></pre>