Hi Anthony,

many thanks for such a prompt reply, is greatly appreciated..:-)

I have tested your code and works as expected, however i didn't mention the fact that there could be possibly be upto 100 clients using this process and the expected values could change - i have read the best practices for posts as per advised and will ensure all information required is posted in future..

so, my client table has clientid and expected populated and will be pretty much static (future additions \ changes may or may not be made) and the jobhis table contains information about the job against each client

i first wanted some code to look through the client table and join with the jobhis table, then build on a calculation on the variance between the 2..

i have included some code to build the tables and test data - for example, client id 3,5 and 8 all should have variances, all of the other clients should have a count in jobhis that matches with their expected value..

-- create client table

CREATE TABLE [dbo].[Client](

[clientID] [int] NOT NULL,

[expected] [int] NOT NULL,

CONSTRAINT [PK_Client] PRIMARY KEY CLUSTERED

(

[clientID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

-- create jobhis table

CREATE TABLE [dbo].[JobHis](

[JobHistID] [int] IDENTITY(1,1) NOT NULL,

[clientid] [int] NOT NULL,

[jobinfo] [varchar](50) NOT NULL,

CONSTRAINT [PK_JobHis] PRIMARY KEY CLUSTERED

(

[JobHistID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

-- insert test client into client table

INSERT INTO [dbo].[Client]

([clientID]

,[expected])

values

(1,1),(2,2),(3,4),(4,2),(5,5),(6,2),(7,2),(8,5),(9,0),(10,3)

GO

--insert job results into jobhist table to compare against expected value

INSERT INTO [dbo].[JobHis]

([clientID]

,[jobinfo])

values

(1,'test job successful'),

(2,'test job successful'),

(2,'test job successful'),

(4,'test job successful'),

(4,'test job successful'),

(6,'test job successful'),

(6,'test job successful'),

(7,'test job successful'),

(7,'test job successful'),

(10,'test job successful'),

(10,'test job successful'),

(10,'test job successful')

GO