[/quote]

Note that If you use Jeff Moden's code,if there are more than one value then the first/last(not sure which one) returned value would be assigned[/quote]

Thanks for the reminder. ...

For the sake of discussion...

I have in the past done this:

select first_name

,last_name

,government_id

from people

where len(government_id ) < 9

Would this be faster, slower?

Some progress.

This gives me a list of totals. Unfortunately, they don't match the above values. Possible cause?

--get Branches having MNCs according to length

select distinct BRN_CD

into...

Here's a snippet of the end result. Thank you for the lesson!

SELECT pr.Code,

--jan

SUM(case when pr.RevDate = '1/1/07' then pr.CommissionableRevenue end) as JanCommis,--

SUM(case when pr.RevDate = '1/1/07' then s.[Jan TBR]...

That's almost it. I made some minor changes and am getting closer still:

select #GovRev.Code_t,

(case when #GovRev.RevDate = '1/1/07' then #GovRev.CommissionableRevenue end) as JanCommis,--

(case when #GovRev.RevDate = '1/1/07' then smcsarchive..CEM_SepYTD_TBRbyBranch.[Jan...

Both are SQL Server 8.0.

These are what worked. Thanks!

SELECT IsNull(q1.ProdGroup, q2.ProdGroup) as NameofProdGroup,

ProdCodePROD,

ProdCodeANL

FROM (

select ProdGroup,

count(ProdCode) as ProdCodePROD

from smcscalc..ERVProdGrp_ProdCode

where ProcessDate <'12/20/07'

group by ProdGroup

) As q1

FULL OUTER JOIN (

select ProdGroup,

count(ProdCode) as ProdCodeANL

from smcscalcanalysis..ERVProdGrp_ProdCode

where ProcessDate <'12/20/07'

group...