It might be worth converting (a version of) the sproc into a multi-statement table-valued function. The performance won't change but it would give you the flexibility of "running" the code...

Continued here.

, x.FAC_ACTIVITY [FAC_ACTIVITY]

, x.NUMMAINTEMP [NUMMAINTEMP]

, LH.dtLeaseFrom [LHdtLeaseFrom]

, LH.dtLeaseTo [LHdtleaseto]

, crp.destimated [crpdestimated]

--, pps.HValue [ppsNSFFEE]

,pps.NSFFee

,pps.MTMFee

,pps.SecDepIntRate

, m.stext [memoStext]

FROM vw_DK_RMMISCDATA x

LEFT JOIN Property p

ON x.RM_ID...

Jeff Moden (2/26/2013)

---

dwain.c (2/26/2013)

---

I did have to make an assumption (the 0 in the second row of the set up data) as the OP only provided 4 data columns in...

Ah, I think you're looking at a pivot - does this help?

SELECT

p.HPROP,

p.STYPE,

p.SVALUE,

NSFFee = CASE WHEN p.STYPE = 'nsffee' THEN p.hvalue END,

MTMFee = CASE WHEN p.STYPE = 'MTMFee'...

Is this - [SecDepIntRate] a column name or a value?

-- APPLY can simplify calculations like this;

SELECT

p.HPROP,

p.STYPE,

p.SVALUE,

x.Newcolumn

FROM PROPOPTIONS P

CROSS APPLY (

SELECT Newcolumn = CASE

WHEN p.STYPE = 'nsffee' THEN 'NSFFee'

WHEN p.STYPE = 'MTMFee' THEN 'MTMFee'

WHEN p.STYPE...

It's an estimated plan - the actual plan for one of the queries would be much more informative. However, the following points spring to mind.

Statistics appear to be out of...

mister.magoo (2/26/2013)

---

ChrisM@Work (2/26/2013)

---

dwain.c (2/26/2013)

---

ChrisM@Work (2/26/2013)

---

peter-757102 (2/26/2013)

---

Using a binary comparisons is a good speedup for specific cases and good to be aware of and certainly worth mentioning in bold with...

Bought mine from GM too, about fifty quid IIRC. What you actually get is four...

dwain.c (2/26/2013)

---

ChrisM@Work (2/26/2013)

---

peter-757102 (2/26/2013)

---

Using a binary comparisons is a good speedup for specific cases and good to be aware of and certainly worth mentioning in bold with its benefits....

That's what I...never mind.

peter-757102 (2/26/2013)

---

Using a binary comparisons is a good speedup for specific cases and good to be aware of and certainly worth mentioning in bold with its benefits. What it...

Sean's solution is almost certainly quicker than Kingston's ROW_NUMBER method; however, Kingston's solution is a...

VASUforSQL (2/25/2013)

---

Hi ALL,

I have a table called Products which has many columns.

It is a temporary table used for reporting purpose. The data will be processed to this table concurrently by...