Instead of using the SUM() function on varchar values you'd need to use MAX().

Something like:

select

@Zip1 = max(Case When Rank = 1 Then Zip End),

@Zip2 = max(Case When Rank =...

It depends.

I asked before: what is the data type of functionDate?

You stated it would be datetime, but that doesn't...

There is still no sample data available that would make it possible to create your expected output: COMPLETE(TRAIN) CANCELLED(TRAIN) COMPLETE(BUS) CANCELLED(BUS)

Let's start from the very beginning:

Please show us the data...

Unfortunately, we don't have any sample data :unsure:

Please use the INSERT INTO @tbl and add some sample data rather thana join to tables we don't have access to.

Also, you...

No reason to apologize. That's what a forum is all about: to have more than just...

apat (3/5/2010)

---

Yes, this works... Thanks a lot everyone...

One more point,

I also would like to do the opposite for power function as well...

Instead of ---- ...

Are you really sure you want to calculate 1.8 ^975.81???

Not even sure, what that number will be....

What user did you use to try the insert?

Your revoke statement will remove any previously given permission:

Straight from BOL:

I guess what you're looking...

Would you please modify your sample data to include some sample data for your new columns?

Also please clarify whether the data you'd like to PIVOT will be static (e.g. there...

You could use the FOR XML PATH approach and a subquery (or CTE):

DECLARE @tbl TABLE

(

grp INT,ledger CHAR(3), amount INT

)

INSERT INTO @tbl

SELECT 1 ,'A01', 5 UNION ALL

SELECT 1 ,'A02', 3 UNION...

I just needed to show that your statement

is just wrong. Nothing...

BaldingLoopMan (3/4/2010)

---

also u have to ensure that the aliases of everything your selecting must match between the top select and bottom select. these aliases is what you will put in...

There are some confusing statements in your epost:

"fieldname of datetime datatype" vs. "daily date entries; just dates, no time"

That's simply impossible. Either you have a DATE datatype (assuming you're...

cowabunga (3/4/2010)

---

Not so simple !

They are allowed...

In addition to the data WC asked for, you should provide the column we need to order the values. Otherwise the TOP statement won't make that much sense...