Or if you want the results in a table format, you could try this:

select asset_year,

sum(case when asset = 'A' then 1 else 0 end) A,

sum(case when asset = 'B' then...

--create test data

create table test_count (asset_year int, asset varchar(10));

go

insert into test_count values (2009, 'A')

go 12

insert into test_count values (2010, 'A');

go 14

insert into test_count values (2009, 'B');

go 5

insert into test_count values...

Seems if he was that specific, he'd know how to fix the problem...

LutzM (7/8/2011)

---

Since you're using SS2K5 (as indicated by the forum you posted in) I'd recommend to look into the OUTPUT clause of the INSERT statement. It's much more reliable than...

True, but the OP said earlier, "Only one user is inserting records at anytime." So if this holds true, that this is the only user inserting into this table, IDENT_CURRENT...

Since you know the table, replace @@IDENTITY with SELECT IDENT_CURRENT('MailLog')

kramaswamy is right about this. if anyone else is working elsewhere in the database, @@IDENTITY could pick up an unrelated ID...

...

I have a program that inserts a record, returns the record id of the newly inserted record, then inserts another record in another table using the returned row id as...

No reason to use error-handling for a DDL script. Just check if the object exists and create it if not, like so:

IF NOT EXISTS (SELECT 1 from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME...

sqlquest2575 (7/7/2011)

---

Thank you for your reply. I am still trying to figure out the issue here. After Modifying the queries now the error says that table does not exist... I...

Does that mean you found the problem? What was it?

So, if this returned nothing

select * from information_schema.tables where table_name = 'qry_Reports'

then there...

Very cool. Here is how we do it in the hood: fill in the @CartID and @ShopperID in the script below pulled from your proc and run it. It'll show...

The only way I see @LeafCount not getting the right total is if @SingleDoorCount or @DoubleDoorCount is null.

Congrats. Maybe I can use this as motivation to start studying for mine :pinch:

GilaMonster (7/6/2011)

---

toddasd (7/6/2011)

---

Exactly. There has to be an INSTEAD OF INSERT trigger on the archive table. Lowell and...