Ok, here's what I came up with:

Basically, I add a single row per database using UNION ALL and sort it as required.

The major downside (except for possible performance impacts due...

I had a look at the bmp file you posted. That's not what I expected.

Please provide table structure (CREATE TABLE) and some INSERT INTO ... SELECT ... UNION ALL statements...

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:...