Or use the number table function to populate the Numbers table (I that's what you meant, KH, I apologize...)

1. I think so, yes. 2. I guess this depends on your requirements and the amount of data.

You do have a point there - you might have to store a lot of data in a Numbers table. I ran the following (simple and stupid) test, and my...

I didn't know you needed that many columns

Maybe the following query is better:

select

s.entity,

s.organisation,

d.source,

d.labid,

month(d.testdate),

year(d.testdate),

count(*)

from tblSource s left join tblData d

on s.pathcode = d.source

group by...

Why use a Number Table Function instead of a Number table? Using a Number table is simpler (in my opinion). I also think it is faster.

Nice solution, KH, I have only a minor improvement. Instead of joining on

(

select 1 as num union all select 2 union all select 3 union all

select 4 union all select...

I am still not sure what you want. The problem is the non-uniqueness of the columns. I have two examples below. Please state what the result should be in each...

Ok - does this give you what you want?

select

s.entity,

s.organisation,

d.source,

d.labid,

sum(case when month(d.testdate) = 1 then 1 else 0 end),

sum(case when month(d.testdate) = 2 then 1 else 0 end)

from tblSource s left...

Don't say that my judgement is better - that's nonsense

Which query is the most readable is probably subjective... Which one is fastest can...

A first attempt, I haven't joined with tblSource yet. Am I on the right track?

select

source,

labid,

sum(case when month(testdate) = 1 then 1 else 0 end),

sum(case when month(testdate) = 2 then 1 else...

Are you worried about readability or performance? I hope SQL Server will realise that two of the patindexes are the same and not calculate it more than once...

David's solution looks faster to me, although it is probably not the most readable...

You could also use (inspired by David's suggestion):

select substring(string, PATINDEX('% [.0-9]%PT%',...

I still don't see how these numbers come up. Consider Entity x, Site1, Lab B. This should be 2 in Jan and 6 in Feb. Why? Also, in your desired...

Could you give some testdata for tables tblData and tblSource along with the expected output of the query?

If you have data on the following form:

c_id   o_date

1       1-1-2006

1       2-2-2006

Then which o_date should be displayed along with c_id = 1? (The largest? The smallest? Or something else?)