title: How to join two select queries in SQL Server 2012
body: <p>I have two queries. One query pulls the information based on the orders that have been scheduled to them in a day by employee. The other query pulls the number of orders that have been completed and paid in a day, along with the total amount of the revenue from the orders.  </p>
  
  <p>I have scraped the forums to get the code together to get these queries, but I need to have both queries joined together. I want to use this information in report builder once I get it done. It's probably simple for someone, but it's confusing me as I'm far from any sort of expert with SQL.  </p>
  
  <p>I only need one day at the moment, but found this code and modified for now and hope that I can use it in the future when needing week and month data.</p>
  
  <p>Any help would be appreciated.</p>
  
  <p>Query 1</p>
  
  <pre><code>declare @dt DATE = '20160823'
  set DATEFIRST 7;
  set @dt = dateadd(week, datediff(week, '19050101', @dt), '19050101');
  
  ;with dt as
  (
      select 
          Technician = (CASE emp_id
                           WHEN 'CW697' THEN 'Joe Biggs'
                           WHEN 'DZ663' THEN 'Mimi Cassidy'
                        END),
          dw = datepart(weekday, DATE)
      from 
          dbo.ordemps 
      where 
          date &gt;= @dt and date &lt;dateadd(day, 7, @dt)
  ),
  x AS
  (
      select 
          Technician, dw = coalesce(dw,8),
          c = convert(varchar(11), COUNT(*))
      from 
          dt 
      group by 
          grouping sets((Technician), (Technician,dw))
   )
  select 
      Technician, 
      [Sun] = coalesce([1], '-'),
      [Mon] = coalesce([2], '-'),
      [Tue] = coalesce([3], '-'),
      [Wed] = coalesce([4], '-'),
      [Thu] = coalesce([5], '-'),
      [Fri] = coalesce([6], '-'),
      [Sat] = coalesce([7], '-'),
      TOTAL =[8]
  from 
      x
  PIVOT 
      (MAX(c) FOR dw IN([1],[2],[3],[4],[5],[6],[7],[8])) as pvt;
  </code></pre>
  
  <p>Query 2    </p>
  
  <pre><code>select
      case
         when grouping(d.m)=15 then 'Year ' + cast(max(d.y) as varchar(10))
         when grouping(date)=15 then datename(m, max(DATE)) + ' ' + cast(max(d.y) as varchar(10))
         else cast(cast([date] as date) as varchar(255))
      end as DATE,
      TotalOrders = /*sum(Amount)*/convert(varchar(11), COUNT(*)),
      TotalSales = sum(Amount),
      Technician = (CASE recv_by
                       WHEN 'CW697' THEN 'Joe Biggs'
                       WHEN 'DZ663' THEN 'Mimi Cassidy'
                    END)
  from 
      ordpay
  cross apply 
      (select
           datepart(yy, [date]),
           datepart(m, [date])
      ) d(y, m) 
  where 
      [DATE] &gt;= dateadd(day, datediff(day, 1, getdate()), 0)
      and [date] &lt; dateadd(day, datediff(day, 0, getdate()), 0)
  group by 
      recv_by, d.y, rollup (d.m, date)
  order by 
      d.y desc, grouping(d.m), d.m, grouping(DATE), DATE
  </code></pre>