I think you want to use SUM with a CASE statement. Maybe something along these lines:

SELECT SUM(CASE WHEN B.TRANS_TYPE = '2' THEN 1

...

I find it useful to loosely equate Excel Workbook files as databases and to think of a Workbook's Worksheets (aka Tabs) as tables. Just like with SQL Server you cannot...

mitch.fh (5/11/2011)

---

don't get me wrong. I am in favour of using the datetype for dates,

I just want to know if anyone made bigger test with those types before I move...

lindsaywang (5/8/2011)

---

Lutz,

I only want to create one query has 8000+ charaters, and prove the openquery doesn't work. and see a solution for it. Becasue I can't give you the...

Please post the table definition including constraints and indexes as well.

Surround...

IMAGE is a deprecated data type....

Ooops...wrong thread.

I have not tested them side-by-side however DATE has an edge in terms of its size...the storage size of an INT is 4 bytes while the storage size of the...

Here's a working demo with the GROUP BY that shows the escape issue:

SET NOCOUNT ON ;

GO

IF OBJECT_ID(N'tempdb..#Data') > 0

DROP TABLE #Data ;

GO

CREATE TABLE #Data

(

id...

PHXHoward (5/11/2011)

---

opc.three (5/11/2011)

---

Jason, your solution, while it may produce the correct result, will inevitably perform very poorly when run against a large set of data. I would offer one tweak...

If you're using SQL2005 you should not be using the IMAGE data type...use VARBINARY instead.

You can convert a hex string to VARBINARY using the CONVERT function...make sure you pay attention...

Very cool. Thanks for the additional link and for your notes.

It could be ANSI_WARNINGS or ARITHABORT.

ANSI_WARNINGS and ARITHABORT are both ON for new query windows in SSMS 2008 R2 by default which DOES allow Divide By Zero errors to bubble...

PS Be careful with using the XML RAW method as well...it escapes characters like ampersands and angle brackets to their XML-safe equivalents (namely &ampamp; , &amplt; and &ampgt;) so...