for some reason the first time I used #temp table with query it didn't work. Forget the error.

Now either #temp or table variable works.

So, I'll post the entire script/solution below:

DECLARE @benches TABLE (Bench varchar(10));

INSERT @benches

values

('LabC1'),

('LabC2'),

('LabC3'),

('LabC4');

INSERT INTO RunsByBench

SELECT Bench AS BenchName,

COUNT(CreateDate) AS NumberOfRunsOnBench,

MAX(CreateDate) AS LastRun

FROM @benches b

OUTER APPLY (

SELECT r.createdate,

count(*) AS MachinesInRun

FROM Runs r

INNER JOIN runs_machines rm ON rm.runid = r.runid

INNER JOIN Machines m ON m.machineid = rm.machineid

WHERE m.NAME LIKE (b.Bench + '[0-9][0-9][0-9]')

AND r.createdate > dateadd(M, - 3, getdate())

GROUP BY r.runid,

r.creatorid,

r.createdate,

r.NAME,

r.enddate

) AS foo

GROUP BY Bench