haha,
hi Rudy. I recall you helping me out back in 2003 or 2005 not sure, when I first joined this site.
Apologies for my earlier post, maybe some sample data might help. (I have blanked out some names as its live data)
In the diagram above, the username column comes 1st and the pivoted columns give you the average stats
What I am trying to achieve is a sub query like this. e.g. for the first username and first column
SELECT Count(distinct jobid) FROM [dbo].[Reports] WHERE USERNAME = ‘xxxxx.daley’ and ClinicID =‘column1’
for the 2nd row
SELECT Count(distinct jobid) FROM [dbo].[Reports] WHERE USERNAME = ‘xxxxx.taylor’ and ClinicID =‘xxxx123’
etc
and for the 2nd column
SELECT Count(distinct jobid) FROM [dbo].[Reports] WHERE USERNAME = ‘xxxxx.daley’ and ClinicID =‘xxxx123’
As each row show how many distinct JOBID a user (e.g. xxxx.daley) did for that clinic etc etc
Hope this is much clear, thanks