Jason-299789 (5/12/2011)

---

Thanks for the feed back opc, especially on the Distinct vs Group By modification and the tips on XML escaping characters, based on the opening post I would have...

The Dixie Flatline (5/11/2011)

---

One approach is to use SUM(CASE...) in place of COUNT().

The trick is to make the test for an earlier Trans_Type = 1 quick. ...

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.