yatish.patil (7/12/2011)

---

HI ChrisM@Work,

Thank you for your query Solution, but when I tested the output for the data as below.

...

You can see the difference between both. Thank you for...

;WITH Calculator1 AS (

SELECT Historyid, ID, LetterNo, letter,

rn1 = ROW_NUMBER() OVER(ORDER BY Historyid),

rn2 = ROW_NUMBER() OVER(PARTITION BY Letter ORDER BY Historyid)

FROM #Test),

Calculator2 AS (

SELECT Historyid, ID, LetterNo,...

Facebook is awesome for retaining a link with distant relatives. I have cousins in Canada and Hong Kong (and family scattered all around the UK) and FB lets me keep...

Here's another APPLY solution:

DROP TABLE #Strings

CREATE TABLE #Strings (Longstring VARCHAR(150))

INSERT INTO #Strings (Longstring) values ('SABRE a CIRCUIT BREAKER MOTORISATION SCHEMATIC DIAGRAM, FOR USE WITH MICOM & A MULTI VOLTAGE SHUNT...

It's all going South sooner or later.

From BOL UPDATE section: "...This is because...

SQLkiwi (7/7/2011)

---

... By the way, a windowing function in the recursive part of a CTE has a weird semantic (quite counter-intuitive, in fact) but works the way it does because...

SQLkiwi (7/7/2011)

---

Hi Chris,

There's no need for a join at all - a point I realised after editing the TOP trick into your code. I have updated the code and...

The new version runs faster but not by much.

So the optimizer converts what looks like a triangular join (the entire CROSS APPLY part) into a TOP query?

Interestingly, you can...

Nice one, Paul. The triangular join put me off writing and testing it, but in practice the performance is stunningly good - half a million rows return in 12s on...

Craig Farrell (7/6/2011)

---

There's a way using Cross Apply...

Easiest is the recursive CTE:

DROP TABLE #SourceData

CREATE TABLE #SourceData (id INT...

-- simplest

SELECT *

FROM t

WHERE YEAR(CASE

WHEN t.[type] = 'import' THEN t.arrdate

WHEN t.[type] = 'export' THEN t.depdate

ELSE NULL END) = YEAR(@deparr)

-- possibly faster

SELECT *

FROM t...

Today's Random World: "Dragon's Egg".

SQLkiwi (6/30/2011)

---

Hi Chris,

You might be interested in a slight tweak to that code. Take a look at the query plans for these:

...

Notice the special distinct sort in the...