Please post your expected result set based with reference to your sample data (either the first sample, the second or the data from the link you mentioned.

Regarding the c.u.r.s.o.r. *cough*:...

The secod section of the UNION operation has additional columns ([wrk_ha_calldetail].[id_ContactOutcome],[wrk_ha_calldetail].[id_CallResult]).

Either remove those columns or expand the first SELECT statement:

SELECT [wrk_ha_calldetail].[id_calllist], max(CreationDate) as CallDetailCreationDate,0 as [id_ContactOutcome], '' [id_CallResult]

FROM [wrk_ha_calldetail]

A few...

It seems like DynamicCrossTab is the best solution for this task.

Please have a look at the related article referenced in my signature.

If you'd like to see a coded version based...

Lynn Pettis (7/23/2010)

---

Okay everyone, the hardest thing I have had to do since I started here (work) a little over 5 years ago is done, now the countdown to a...

You need to have the same collation per column for each union statement.

Example

SELECT col1 collate Latin1_General_CS_AS, col2 collate Greek_CI_AS

FROM MyTable

WHERE condition

UNION ALL

SELECT col1 collate Latin1_General_CS_AS, col2 collate Greek_CI_AS

FROM MyTable2

WHERE condition

will...

I'd probably use a CTE(common table expression)/subquery with ROW_NUMBER() and a self join to it. Then I would check for DATEDIFF(dd, cte1.enddate, cte2.EffDate)<30.

If you'd like to see a coded example...

Are you sure both columns are datetime?

I think both are char/varchar, since it's unlikely to store the data the way you menitoned in a datetime column....

To be sure the solution...

What part of your question remains unanswered based on my post on the other thread covering the very same issue??

Joe Celko (7/21/2010)

---

For your own education,look up the ANSI/ISO Standard syntax for

SUM(<expression>)

OVER (PARTITION BY ..

...

I'd query against a calendar table or an on-the-fly 12month subquery.

Based on that you'd get the AgtID expanded per month.

Something like

SELECT yr,mnth,ParentAgtID,AgtID

FROM

(

SELECT CAST('20100101' AS...

Please re-read my previous post and provide data (including expected result) in a ready to use format.

sharonmtowler (7/22/2010)

---

i have files that get sent to us i believe in a flat file(not sure yet). someone them puts them in excel. then creates a table from them.(not sure...

I think the samples provided should help you to resolve your issues.

To add another counter, look what ROW_NUMBER actually does and try to modify it to your needs.

Regarding the PIVOT:...

Since we don't have a table including sample data, your request to "go over the intire table" simply returns an error.

Like I stated before: please provide DDL and sample data...

Something like this?

SELECT DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())-2 ,0),GETDATE()