Hi

Something like this?

DECLARE @Client TABLE (ClientID INT, Expected INT)

DECLARE @JobHis TABLE (ClientID INT, JobInfo INT)

INSERT INTO @Client VALUES (1,1),(2,2),(3,3),(4,4),(5,5)

INSERT INTO @JobHis VALUES (1,100),(2,100),(2,200),(3,100),(3,200),(4,100),(4,200),(4,300),(4,400),(5,100),(5,200),(5,300),(5,400),(5,500)

SELECT

t1.ClientID,

t1.Expected,

COUNT(t2.ClientID) AS Actual,

CASE WHEN t1.Expected > COUNT(t2.ClientID) THEN 'Error, Actual less than Expected' ELSE 'OK, Expected and Actual match' END AS Status

FROM

@Client t1

INNER JOIN

@JobHis t2

ON

t1.ClientID = t2.ClientID

GROUP BY

t1.ClientID,

t1.Expected

If not, could you please follow the second link in my signature on posting code and data for the best help.

From that and with the expected outcome you provide we will be able to hopefully get a solution which solves your problem.