How to optimize SELECT statement with multiple sub-queries

Question

I have a query that I'm trying to optimize but haven't had much success. There are two tables, one with the main data and one with timestamps of when specific events occurred. The tables are relational using a common key of adID. I am trying to perform a query that pulls in all of the timestamps and other data from the main table. I have it working but I am trying to optimize so it runs faster.

SELECT a.ID,a.repID,a.artistID,
(
    SELECT TOP 1 c.timestamp
    FROM Tracking AS c
    WHERE statusID = 4
    AND c.ID = a.ID
    ORDER BY c.timestamp ASC

) 
AS created,
(
    SELECT TOP 1 d.timestamp
    FROM Tracking AS d
    WHERE statusID = 5
    AND d.ID = a.ID
    ORDER BY d.timestamp ASC
)
AS claimed,
(
    SELECT TOP 1 p.timestamp
    FROM Tracking AS p
    WHERE statusID = 6
    AND p.ID = a.ID
    ORDER BY p.timestamp ASC
)
AS proof,
(
    SELECT TOP 1 v.timestamp
    FROM Tracking AS v
    WHERE statusID = 8
    AND v.ID = a.ID
    ORDER BY v.timestamp ASC
)
AS approved,
(
    SELECT count(ID)
    FROM Tracking AS t
    WHERE statusID = 6
    AND t.ID = a.ID
)
AS proofcount
FROM Advertising AS a
WHERE a.statusID = 8

Any help on this is appreciated. I'm not too familiar with SQL Server so I am not too well versed in optimizing queries such as these.

Answer 1

You should be able to use the following:

SELECT a.ID,
  a.repID,
  a.artistID,
  min(case when t.statusID = 4 then t.timestamp end) created,
  min(case when t.statusID = 5 then t.timestamp end) claimed,
  min(case when t.statusID = 6 then t.timestamp end) proof,
  min(case when t.statusID = 8 then t.timestamp end) approved,
  count(case when t.statusID = 6 then id end) proofcount
FROM Advertising AS a
LEFT JOIN Tracking t
  on a.id = t.id
WHERE a.statusID = 8
GROUP BY a.ID, a.repID, a.artistID;