title: How to do union of multiple CTE
body: <p>I have two queries as below</p>
  
  <p><strong>Query 1</strong></p>
  
  <pre><code>set @sql = cast(N'
  ;with cteSales as
  (
    select
      datepart(weekday, s.enddate) as SalesWeekDay,
      datediff(hour, cast(s.enddate as date), s.enddate) DayTime,
      s.enddate SalesDate,
      CONVERT(DECIMAL(10,2),OrigionalSubTotal/100.0) as OrigionalSubTotal,
      o.ordertype ordertype
    from sale s,DinePointOrderType o
    where s.enddate &gt;= @DateBegin and s.enddate &lt; @DateEnd
   and o.ordertypeindex = s.ordertype
   and s.ordertype = 1
    and s.IsSuspend = 0  and s.IsTrainMode = 0
    and s.IsCancelled = 0 and s.wasrefunded=0
  ),
  cteSalesPerWeekDays as
  (
    select
      p.hrs,
      p.period,
      isnull(p.ordertype,''DINE IN'') ordertype,' as nvarchar(max)) + @col_per_day_list + N'
    from #Timing t
    cross join #WeekDays w
    left join cteSales s on datediff(hour, w.wd_date, s.SalesDate) = t.hrs
    pivot
    (
      Sum(s.OrigionalSubTotal)
      for w.wd_name in (' + @pivot_val_per_day_list + N')
    ) p
  )
  
  select
    r.hrs,
    r.period as [From-To],
    r.ordertype,' + @sum_cols_per_day_list + N'
  from cteSalesPerWeekDays r
  group by r.hrs, r.period,r.ordertype
  order by r.hrs
  
  '
  exec sp_executesql @sql, N'@DateBegin datetime, @DateEnd datetime',
    @DateBegin = @DateBegin,
    @DateEnd = @TradeDateEnd
  
  GO
  </code></pre>
  
  <p><strong>Query 2</strong></p>
  
  <pre><code>set @sql = cast(N'
  ;with cteSales as
  (
    select
      datepart(weekday, s.enddate) as SalesWeekDay,
      datediff(hour, cast(s.enddate as date), s.enddate) DayTime,
      s.enddate SalesDate,
      CONVERT(DECIMAL(10,2),OrigionalSubTotal/100.0) as OrigionalSubTotal,
      o.ordertype ordertype
    from sale s,DinePointOrderType o
    where s.enddate &gt;= @DateBegin and s.enddate &lt; @DateEnd
   and o.ordertypeindex = s.ordertype
   and s.ordertype = 2
    and s.IsSuspend = 0  and s.IsTrainMode = 0
    and s.IsCancelled = 0 and s.wasrefunded=0
  ),
  cteSalesPerWeekDays as
  (
    select
      p.hrs,
      p.period,
      isnull(p.ordertype,''TAKE OUT'') ordertype,' as nvarchar(max)) + @col_per_day_list + N'
    from #Timing t
    cross join #WeekDays w
    left join cteSales s on datediff(hour, w.wd_date, s.SalesDate) = t.hrs
    pivot
    (
      Sum(s.OrigionalSubTotal)
      for w.wd_name in (' + @pivot_val_per_day_list + N')
    ) p
  )
  
  select
    r.hrs,
    r.period as [From-To],
    r.ordertype,' + @sum_cols_per_day_list + N'
  from cteSalesPerWeekDays r
  group by r.hrs, r.period,r.ordertype
  order by r.hrs
  
  '
  
  
  exec sp_executesql @sql, N'@DateBegin datetime, @DateEnd datetime',
    @DateBegin = @DateBegin,
    @DateEnd = @TradeDateEnd
  </code></pre>
  
  <p>Is it possible to do a union of this two queries?</p>