TSQL query help to return missing rows

You can do this in two steps: use cross join to generate all the rows and then left join to put in the values:

select m.moveid, s.showtime
from (select distinct movieid from movieshows) m cross join
     (select distinct showtime from movieshows) t left join
     movieshows ms
     on ms.movieid = m.movieid and ms.showtime = t.showtime;

The only think I don't understand is the id. How do you determine the id for a non-matching row?

Hmmm, here is one way to get the id:

select ms.id, m.moveid, s.showtime
from (select distinct movieid from movieshows) m cross join
     (select distinct showtime from movieshows) t outer apply
     (select top 1 ms.*
      from movieshows ms
      where ms.movieid = m.movieid and ms.showtime <= t.showtime
      order by ms.showtime desc
     ) ms

Here is another method using a Tally Table.

First, generate all possible ShowTimes,which should be 00:00 to 23:00. Then get the MIN(ShowTime) and MAX(Showtime) of each MovieId. Now do a JOIN on both result to generate all possible combinations of MovieIds and Showtimes such that the time is between the MIN and MAX Showtime.

To get the Id, use CROSS APPLY

WITH CteHr(hr) AS(
    SELECT
        CAST(DATEADD(HOUR, hr, 0) AS TIME)
    FROM (VALUES
        (0), (1), (2), (3), (4), (5), (6), (7),
        (8), (9), (10), (11), (12), (13), (14), 
        (15), (16), (17), (18), (19), (20), (21), (22), (23)
    ) AS t(hr)
),
CteMinMax(MovieId, minHr, maxHr) AS(
    SELECT
        MovieId, MIN(Showtime), MAX(Showtime)
    FROM #MovieShows
    GROUP BY MovieId
)
SELECT
    t.Id,
    mm.MovieId,
    Showtime = h.hr
FROM CteMinMax mm
CROSS JOIN CteHr h
CROSS APPLY(
    SELECT TOP 1 Id
    FROM #MovieShows
    WHERE
        Movieid = mm.MovieId
        AND Showtime <= h.hr
    ORDER BY Showtime DESC
) t
WHERE h.hr BETWEEN mm.minHr  AND mm.maxHr

ONLINE DEMO

If you have SQL Server 2012 or higher, you can use a recursive CTE combined with LEAD window function as per following example:

    DECLARE @MovieShows TABLE
    (
        Id INT, Movieid VARCHAR(20), Showtime TIME
        PRIMARY KEY (Movieid, Showtime)
    )

    INSERT INTO @MovieShows
    SELECT 11,'m1','13:00' UNION ALL
    SELECT 34,'m1','15:00' UNION ALL
    SELECT 64,'m1','16:00' UNION ALL
    SELECT 81,'m1','21:00' UNION ALL
    SELECT 23,'m2','14:00' UNION ALL
    SELECT 45,'m2','16:00' UNION ALL
    SELECT 55,'m2','20:00' UNION ALL
    SELECT 66,'m2','21:00'

    ;WITH CTE_Shows
    AS
    (
        SELECT   Id
                ,Movieid
                ,Showtime
                ,LEAD(Showtime, 1, NULL) OVER (PARTITION BY Movieid ORDER BY Showtime) AS NextShowTime
        FROM    @MovieShows MovesBase
        UNION   ALL     -- Fill in the gaps by performing a recursive union
        SELECT   Id
                ,Movieid
                ,DATEADD(HOUR, 1, Showtime) AS Showtime -- Add one hour to the current show time.
                ,Fill.NextShowTime
        FROM    CTE_Shows Fill
        WHERE   DATEADD(HOUR, 1, Fill.Showtime) < Fill.NextShowTime -- Only perform recursive union where the current show time + 1 hour is less than the next show time in the current dataset.
    ) 
    SELECT   Id
            ,Movieid
            ,Showtime
    FROM    CTE_Shows
    ORDER BY Movieid, Showtime

The main advantage of this method is that it eliminates the need for additional lookup tables.

Possible approaches to this problem include

1. Temporary Tables

2. Self / Cross Joins

3. Ammending the Schema so that all possible showtimes are stored in a separate table and then using an outer join to populate the results.