Also just a quick note...I noticed that you are applying a filter to a table that you are left joining to. That will effectively turn your left join into an...

How about something like this:

Select c.ConsultantID

,c.UserXID

...

Your issue is one of scoping.

You can't reference a field joined to in outer query in the inner query.

When working with derived tables, get your inner query working independent of...

Thanks for the feedback! I can sympathize with your situation. It is never easy getting dumped on, especially with SQL. I come from a procedural programming background, and the set...

alorenzini (3/21/2008)

---

That worked fantastic. One of these days I will get this TSQL figured out...

One more quick question. for the column Name (eg. JAN) I would like to format as...

OK. A few things here:

No need to sub query to pull out a description, and no need to repeat the same case logic over and over again.

See if this gets...

Go back and re-read some of the previous posts, because the pieces are there for you to put together and I have a job too...

You're not doing anything to "unpivot"...

If you're in the business of making IDs, then just make some IDs...

Select ConsultantID, 2 as ActivityID, PersonalSales, Date

From Table

union all

Select ConsultantID, 3 ,FirstLevelSales, Date

From Table

Union all

Select ConsultantID, 4,...

Oh, I see. In your original post, you show those values as columns in a table. What you are trying to do in one query is UNPIVOT the columns into...

So the question becomes...how do you know whether a sale is Personal, first, second or third? Once you know that, group by that column, and it should break it out...

I suppose this should be painfully obvious, but you need to relate the ActivitySort column to some other column in...

Right now, it updates those that are different, and it only adds one....

Well, your basic crosstab in SQL looks like:

use AdventureWorks

GO

Declare @MonthsOut int, @StartDate datetime, @EndDate datetime

Set @MonthsOut = 6

Set @StartDate = '2/1/2002'

Set @EndDate = dateadd(mm,@MonthsOut, @StartDate)

Select SalesPersonID,

Jan = sum(case when...

Exploring the trigger route...

CREATE TABLE [dbo].[TestTable1](

[n] [int] IDENTITY(1,1) NOT NULL,

[BadDate] [datetime] NOT NULL,

CONSTRAINT [PK_TestTable1] PRIMARY KEY CLUSTERED

(

[n] ASC

)

go

create TRIGGER [dbo].[trg_Test1]

ON [dbo].[TestTable1]

for...

You might have to go the dynamic route on this one:

Declare @sql nvarchar(1000)

Set @sql = 'Select EmpID as ' + @ID + ' ,EmpName as '...