Help with combining two queries into one
Assuming your first query has error_flag and date in one of the tables then something like this would probably work:

SELECT ROW_NUMBER() OVER ( ORDER BY s.Date DESC ) AS Rownum ,
       s.cname ,
       c.HName
FROM   c
       INNER JOIN s ON (   c.pid = s.pid
                           AND c.cid = s.cid )
WHERE s.error_Flag = 'Y' AND Rownum = 1;