Sql query to display invoice table where no products delivered

-- drop the temp tables if they already exist
IF OBJECT_ID('tempdb..#InvHeader') IS NOT NULL
    DROP TABLE #InvHeader
IF OBJECT_ID('tempdb..#InvItems') IS NOT NULL
    DROP TABLE #InvItems

-- create the temp tables
CREATE TABLE #InvHeader
(
    InvNumber INT,
    [Name] NVARCHAR(50)
)

CREATE TABLE #InvItems
(
    InvNumber INT,
    Item NVARCHAR(50),
    Quantity INT,
    Delivered INT
)

-- populate the temp tables with your sample data
INSERT INTO #InvHeader ( InvNumber, [Name])
VALUES (1, 'Michael'), (2, 'Josh')
INSERT INTO #InvItems (InvNumber,Item,Quantity,Delivered)
VALUES (1, 'Pen', 10, 0), (1, 'Pencil', 10, 1), (2, 'Pen', 10, 0)

-- everything  above this comment will not be needed in your query. It is 
-- simply included here so you could test what I did

-- here's the code you're interested in. If you're happy with it, substitute 
-- your table and column names where appropriate.

-- create a common table expression to group the items so that each invoice 
-- number has exactly one delivered value that represents the total items 
-- delivered
;WITH cte AS
(
    SELECT invnumber, SUM(Delivered) AS totalDelivered
    FROM #InvItems
    GROUP BY invnumber
)
-- select the invoice number and name from the header table 
SELECT h.InvNumber,
       h.[Name]
FROM #InvHeader AS h 
-- and join tpo the common table expression
JOIN cte AS i 
-- but only in the invoice number matches and the total delivered value is 0
ON i.invnumber = h.InvNumber AND i.TotalDelivered = 0