Outputting 2 queries into one table (as extra columns)

I have 2 separate queries that i'd like to combine so that the results for both queries are output to 1 table

From the 2 queries below I'd like one table with the following columns: StaffId, FullName, DayCount, MonthCount.

What is the best way of doing this?

SELECT TOP (10) COUNT(*) AS Mont开发者_Go百科hCount, Staff.FirstName + Staff.LastName AS FullName, Staff.StaffID
FROM Sales INNER JOIN
    Staff ON Sales.StaffID = Staff.StaffID LEFT OUTER JOIN
    SaleEndorsements ON Sales.SaleID = SaleEndorsements.SaleID
WHERE Sales.CreationDate BETWEEN DATEADD(mm, DATEDIFF(mm,'',getdate()), '') AND getdate() AND SaleEndorsements.EndorsementID = 31
GROUP BY Staff.FirstName, Staff.LastName, Staff.StaffID
ORDER BY MonthCount DESC

SELECT TOP (10) COUNT(*) AS DayCount, Staff.FirstName + Staff.LastName AS FullName, Staff.StaffID
FROM Sales INNER JOIN
    Staff ON Sales.StaffID = Staff.StaffID LEFT OUTER JOIN
    SaleEndorsements ON Sales.SaleID = SaleEndorsements.SaleID
WHERE Sales.CreationDate BETWEEN DATEADD(DD, DATEDIFF(DD,'',getdate()), '') AND getdate() AND SaleEndorsements.EndorsementID = 31
GROUP BY Staff.FirstName, Staff.LastName, Staff.StaffID
ORDER BY DayCount DESC

---

Untested and so may be riddled with syntax errors but I think you need to do something like

1. Push the repeated functionality into a base CTE
2. Create another CTE with the month count referencing (1)
3. Create another CTE with the day count referencing (1)
4. Full Outer Join (2) and (3)

Additionally your WHERE clause makes the LEFT JOIN pointless so I've changed this to an INNER JOIN.

WITH T
     AS (SELECT Staff.FirstName,
                Staff.LastName,
                Staff.StaffID,
                Sales.CreationDate
         FROM   Sales
                INNER JOIN Staff
                  ON Sales.StaffID = Staff.StaffID
                INNER JOIN SaleEndorsements
                  ON Sales.SaleID = SaleEndorsements.SaleID
         WHERE  SaleEndorsements.EndorsementID = 31),
     D
     AS (SELECT TOP (10) COUNT(*)             AS DayCount,
                         FirstName + LastName AS FullName,
                         StaffID
         FROM   T
         WHERE  CreationDate BETWEEN DATEADD(DD, DATEDIFF(DD, '', getdate()), '') AND
                                     getdate()
         GROUP  BY FirstName,
                   LastName,
                   StaffID
         ORDER  BY DayCount DESC),
     M
     AS (SELECT TOP (10) COUNT(*)             AS MonthCount,
                         FirstName + LastName AS FullName,
         FROM   T
         WHERE  CreationDate BETWEEN DATEADD(mm, DATEDIFF(mm, '', getdate()), '') AND
                                     getdate()
         GROUP  BY FirstName,
                   LastName,
                   StaffID
         ORDER  BY MonthCount DESC)
SELECT ISNULL(M.StaffId, D.StaffId)   AS StaffId,
       ISNULL(M.FullName, D.FullName) AS FullName,
       M.MonthCount,
       D.DayCount
FROM   M
       FULL OUTER JOIN D
         ON M.StaffID = D.StaffID  

---

Add a UNION ALL clause to this, like so:

SELECT TOP (10) COUNT(*) AS MonthCount, Staff.FirstName + Staff.LastName AS FullName, Staff.StaffID FROM Sales INNER JOIN     Staff ON Sales.StaffID = Staff.StaffID LEFT OUTER JOIN     SaleEndorsements ON Sales.SaleID = SaleEndorsements.SaleID WHERE Sales.CreationDate BETWEEN DATEADD(mm, DATEDIFF(mm,'',getdate()), '') AND getdate() AND SaleEndorsements.EndorsementID = 31 GROUP BY Staff.FirstName, Staff.LastName, Staff.StaffID ORDER BY MonthCount DESC  
union all
SELECT TOP (10) COUNT(*) AS DayCount, Staff.FirstName + Staff.LastName AS FullName, Staff.StaffID FROM Sales INNER JOIN     Staff ON Sales.StaffID = Staff.StaffID LEFT OUTER JOIN     SaleEndorsements ON Sales.SaleID = SaleEndorsements.SaleID WHERE Sales.CreationDate BETWEEN DATEADD(DD, DATEDIFF(DD,'',getdate()), '') AND getdate() AND SaleEndorsements.EndorsementID = 31 GROUP BY Staff.FirstName, Staff.LastName, Staff.StaffID ORDER BY DayCount DESC 

---

If you can use a stored procedure, this would be a way of doing it:

CREATE PROCEDURE [dbo].[GetStats]
AS

SELECT TOP (10) Staff.StaffID, Staff.FirstName + Staff.LastName AS FullName, COUNT(*) AS MonthCount, 0 As DayCount
INTO #TempMonthlyStats
FROM Sales INNER JOIN
    Staff ON Sales.StaffID = Staff.StaffID LEFT OUTER JOIN
    SaleEndorsements ON Sales.SaleID = SaleEndorsements.SaleID
WHERE Sales.CreationDate BETWEEN DATEADD(mm, DATEDIFF(mm,'',getdate()), '') AND getdate() AND SaleEndorsements.EndorsementID = 31
GROUP BY Staff.FirstName, Staff.LastName, Staff.StaffID
ORDER BY MonthCount DESC

SELECT TOP (10) Staff.StaffID, Staff.FirstName + Staff.LastName AS FullName, 0 AS MonthCount, COUNT(*) As DayCount
INTO #TempDailyStats
FROM Sales INNER JOIN
    Staff ON Sales.StaffID = Staff.StaffID LEFT OUTER JOIN
    SaleEndorsements ON Sales.SaleID = SaleEndorsements.SaleID
WHERE Sales.CreationDate BETWEEN DATEADD(DD, DATEDIFF(DD,'',getdate()), '') AND getdate() AND SaleEndorsements.EndorsementID = 31
GROUP BY Staff.FirstName, Staff.LastName, Staff.StaffID
ORDER BY MonthCount DESC


SELECT #TempMonthlyStats.StaffID, #TempMonthlyStats.FullName, #TempMonthlyStats.MonthCount, COALESCE(#TempDailyStats.DayCount,0) AS DayCount
FROM #TempMonthlyStats
LEFT OUTER JOIN #TempDailyStats
ON #TempMonthlyStats.StaffID = #TempDailyStats.StaffID
ORDER BY MonthCount DESC