Join in SQL Server 2008

Question

I have two different select queries that return data as shown below,

Query 1

SELECT 
    COUNT(showtimeId) AS TotalShows,
    SUM(sc.Capacity) AS ScreenCapacity,
    ShowDate ------For all screens
FROM Shows s
JOIN Screens sc ON sc.ScreenID = s.ScreenID
WHERE 
    s.ShowDate < @dateNew -- someDate
    AND s.MovieID = 34
    AND s.IsDeleted = 0
GROUP BY 
    ShowDate

This outputs data as in image

,

also another select query as,

Query 2

SELECT 
   s.ShowDate,
   COUNT(ut.UserTicketID) AS TotalTicketsSold,
   SUM(ISNULL((Price+ConvinienceCharge-DiscountAmount)/(EntertainmentTax+BoxOfficeTax+1), 0)) AS Nett
FROM Shows s
LEFT OUTER JOIN UserTickets ut ON s.ShowID = ut.ShowID
WHERE 
   ut.ShowID IN (SELECT ShowID
                 FROM Shows
                 WHERE ShowDate < @dateNew -- someDate
                   AND MovieID = 34
                   AND IsDeleted = 0
                 GROUP BY 
                   ShowID)
GROUP BY 
   s.ShowDate

This output data as in image

I am not able to join these two tables so as to obtain a output like this,

I tried declaring table variables and insert into tables and then selecting using join query, but faii to insert into tables. My insert query is as,

DECLARE @tempTable TABLE (
    id INT identity(1, 1),
    TotalShows INT,
    ScreenCapacity INT,
    ShowDate DATETIME
    )

INSERT INTO @tempTable (
    TotalShows,
    ScreenCapacity,
    ShowDate
    )
VALUES (
    (
        SELECT COUNT(showtimeId) AS TotalShows,
            sum(sc.Capacity) AS ScreenCapacity,
            ShowDate ------For all screens
        FROM Shows s
        JOIN Screens sc ON sc.ScreenID = s.ScreenID
        WHERE s.ShowDate < @dateNew -- someDate
            AND s.MovieID = 34
            AND s.IsDeleted = 0
        GROUP BY ShowDate
        )
    )

And the error are as

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

Answer 1

Mybe this will work ?

SELECT 
    COUNT(showtimeId) AS TotalShows,
    SUM(sc.Capacity) AS ScreenCapacity,
    ShowDate ------For all screens,
    ,COUNT(ut.UserTicketID) AS TotalTicketsSold,
    SUM(ISNULL((Price+ConvinienceCharge-DiscountAmount)/
        (EntertainmentTax+BoxOfficeTax+1), 0)) AS Nett
FROM Shows s
JOIN Screens sc ON sc.ScreenID = s.ScreenID
LEFT OUTER JOIN UserTickets ut ON s.ShowID = ut.ShowID
WHERE s.ShowDate < @dateNew -- someDate
    AND s.MovieID = 34
    AND s.IsDeleted = 0
GROUP BY ShowDate

Answer 2

You can use your queries as derived tables and join on ShowDate.

SELECT T1.TotalShows,
       T1.ScreenCapacity,
       T1.ShowDate,
       T2.TotalTicketsSold,
       T2.Nett
FROM (
     SELECT COUNT(showtimeId) AS TotalShows,
         sum(sc.Capacity) AS ScreenCapacity,
         ShowDate ------For all screens
     FROM Shows s
     JOIN Screens sc ON sc.ScreenID = s.ScreenID
     WHERE s.ShowDate < @dateNew -- someDate
         AND s.MovieID = 34
         AND s.IsDeleted = 0
     GROUP BY ShowDate
     ) AS T1
  INNER JOIN 
     (
     SELECT s.ShowDate,
         COUNT(ut.UserTicketID) AS TotalTicketsSold,
         SUM(ISNULL((Price+ConvinienceCharge-DiscountAmount)/(EntertainmentTax+BoxOfficeTax+1), 0)) AS Nett
     FROM Shows s
     LEFT OUTER JOIN UserTickets ut ON s.ShowID = ut.ShowID
     WHERE ut.ShowID IN (
             SELECT ShowID
             FROM Shows
             WHERE ShowDate < @dateNew -- someDate
                 AND MovieID = 34
                 AND IsDeleted = 0
             GROUP BY ShowID
             )
     GROUP BY s.ShowDate
     ) AS T2
  ON T1.ShowDate = T2.ShowDate