Here's the output of the rCTE:

LevelParentItemIDChildItemIDList

1121>2

2231>2>3

2241>2>4

2251>2>5

3481>2>4>8

3361>2>3>6

3371>2>3>7

4791>2>3>7>9

47101>2>3>7>10

sku370870 (7/31/2012)

---

Hi and thanks very much for that.

It produces in the list column:

1>2

1>2>3

1>2>4

1>2>5

1>2>4>8

1>2>3>6

1>2>3>7>9

1>2>3>7>10

What I need it to produce is:

1>2

1>2>3

1>2>3>6

1>2>3>7

1>2>3>7>9

1>2>3>7>10

1>2>4

1>2>4>8

1>2>5

I've tried various sorting but I can't...

This link doesn't work.

Abu Dina (7/31/2012)

---

Hi Chris, I have some questions below. Any advice would be much appreciated. In your original post you said:

Try it out. There's always an element of tuning with...

Here's a rCTE to get you started:

;WITH rCTE AS (

SELECT

[Level] = 1,

tr.ParentItemID, tr.ChildItemID,

List = CAST(CAST(tr.ParentItemID AS VARCHAR(10)) + '>' + CAST(tr.ChildItemID AS VARCHAR(10)) AS VARCHAR(8000))

FROM #tblItemRelationship...

Here you go mate:

CREATE FUNCTION [dbo].[FuzzyMatch_iTVF2k5]

(

@Reference VARCHAR(100),

@Target VARCHAR(100)

)

RETURNS table

AS

-- See also http://research.microsoft.com/pubs/75996/bm_sigmod03.pdf

RETURN

SELECT d.Result, MatchRatio = CAST(CASE

WHEN d.Result = 1 THEN 100

WHEN d.Result = 3 THEN DATALENGTH(@Target)*100.00/DATALENGTH(@Reference)

WHEN d.Result...

Abu Dina (7/31/2012)

---

Thanks for this Chris but when I tried to create the function I get the following error message:

Msg 156, Level 15, State 1, Procedure FuzzyMatch_iTVF, Line 22

Incorrect syntax...

Gazareth (7/30/2012)

---

ChrisM@Work (7/30/2012)

---

The query you posted above doesn't generate more than one month. Where do the two extra months...

That's very kind Paul but I'm just a jobbing TSQL developer! Thanks anyway.

The query you posted above doesn't generate more than one month. Where do the two extra months come from...

So your report currently displays only one month's worth of data?

Hi Paul, thanks for the plan.

Points:

1. No clustered indexes on any of the 4 tables, a suitable clustered index on DeltaFAKT_DETAIL would probably help the expensive SORT in the...

Douglas P. (7/30/2012)

---

I should mention one thing. Months that are displayed in the report are determined by parameter @Month, which is determined by the user.

SQL query which provides data looks...

GilaMonster (7/30/2012)

---

The point is you do it once and check it. He checked and said that the row number does at the moment show the correct order. So you add...

SELECT January, February, March, Difference = March - February

FROM ...

Hi Paul, can you post the Actual plan please, rather than the estimated plan? There are often differences between the two - for instance, the estimated plan you've just posted...