Sub-SELECTs within the SELECT will perform horribly - you are forcing SQL server to run a sub-query for every main row retruned, which is essentially creating the equivalent of a...

>>I have more than 1 value per hour, and I need a random arbitrary value or the earliest.

You need a derived table which returns you the earliest timestamp per hour....

You're stuck with a completely flexible solution, as posted above, or alternatively you can trade off flexibility, remove the looping and place a hard cap on the number of levels...

Which version of SQL Server ?

This would be an ideal candidate for a CTE under SQL 2005.

>>It's a relational database

That's even worse then. Care to tell us who your dialler vendor is so we can avoid them ?

You...

OK, taking your test data, add another purchase of the same product:

insert @purchase

select '0001', '02-Dec-2005'

Resultset is only 1 record. Is this correct ? Only original poster knows...

Right, but what if you want other columns from B in the resultset ?

The original question was to join a product's purchase to its most recent "profile". Maybe I'm wrong...

Oops, I totally missed that.

Alas, you can't reference the outer Table A inside a derived table, so you need to build a different derived table that introduces the purchase date,...

>>I want a resul set with only one sample per hour, for example:

These are incomplete requirements that no-one can answer. If there is more than 1 value per hour, which...

>>Haven't looked at the DDLs yet, but in your where clause, you're not using a column name...

My assumption is that @intContactCategory needs to act as "all categories" if it...

Use a derived table to locatethe most recent update per product, and join to it.

Select A.Product_id, A.Purch_Date, B.*

From A

Inner Join B

  On A.product_id = B.product_id

-- Join to derived table of...

>>even though my tblUsers table is very small (600 records).

Right. But what about the size of the other 2 tables ? What size are tblAgencies and tblContactCategories *and*...

Is it just prefixes you need to consider ?

If so:

Declare @TestData varchar(20)

Set @TestData = 'CA 5000'

Select Substring(@TestData, patindex('%[0-9]%', @TestData), Len(@TestData))

Create a dummy table with an Identity. Save it. Now open the table in EM design mode and change the column to non-identity. See the 3rd toolbar icon from the left in EM ?...

Check BOL. xp_cmdshell probably isn't running under the user you think it is, and the user it is running as probably doesn't have access to a UNC or share or...