Simon,

I'm not sure I understood your specs correctly, but a CASE statement is what you need to use.

SELECT

OwnContact

,

RevenueTotal

,

CASE

Steve,

You could use a RS matrix instead of a table.  You'd need to modify your query to a series of unions that include the column names as well as their...

Mubarek,

Try using a matrix vs. a table.  Although I'm not very familiar with pivot tables using Excel, I've achieved good results using a matrix.

Mike

PK

 I exercised your example, and although I had to force the JOIN version to use the index with a WITH ( INDEX ... ) it clearly is more efficient than my...

Nice to learn that I'm not the only one who's made Classic mistakes... 

PW - I'm curious why you included the test in the JOIN instead of modifying the WHERE clause...

Chris,

Jeff is right on.  The LEFT JOIN is needed to include Clients w/o Tickets.  COUNTing ( t.TicketID ) as opposed to (*) is the other trick.

Unless your using RS 2005 your limited to a single entry per text box.  You could parse an entry ( 1000,2000 etc) into a @table variable prior to using it as...

Herb,

I ran into the same limitation that you are.  UDF's that return a table are only executed once. 

From Books On Line

You can invoke a user-defined function that returns a...

I think you have at least two ways to go.

a) Create a view that UNIONs the contents of your various mailing lists, appending the table name as a column.  Downside...

You may want to try using a matrix instead of a table.  I'm new to matrixs, but they will pivot the data based on any column(s)? in the data set.

Mike

I think this is what your looking for.

SELECT

    o1.Machine,

    o1.OrderNum,

    (   SELECT TOP 1

            o2.OrderNum

        FROM

            [Order] o2

        WHERE

            O2.Machine = o1.Machine AND o2.ProductionDate < o1.ProductionDate

        ORDER BY

            o2.ProductionDate DESC

   ...

DATETIME is a better choice than SMALLDATETIME, but I would hesitate to use either as a primary key.  Consider the effect of needing to adjust your timeclock backwards, or having data introduced...

Robb,

I posed a similar question a short while back.  The answer I got makes sense, even though it wasn't what I was hoping for.  Consider the following:

CREATE PROCEDURE test

 @p1...

TransData  td

LEFT OUTER JOIN Code c  ON td.GLAcct = c.Code

LEFT OUTER JOIN @ExcludedGLAccounts e ON e.GLAcct= td.GLAcct

WHERE e.GLAcct IS NULL

I'd benchmark one method against the other...

select

 u.username,

 (select top 1 gm.groupname

  from tbl_groupmemberships gm

  where u.id=gm.userid

  order by gm.id ) as 'groupname'

from

 tbl_users u

where exists

 (select gm.groupname

 from tbl_groupmemberships gm

 where u.id=gm.userid )