Slightly tested:

SELECT aYr, bYr, aCol, bCol

FROM #A a

CROSS APPLY (

SELECT MAX_bYr = MAX(bYr) FROM #b) x

INNER JOIN #b b

ON b.bYr = CASE WHEN a.aYr <= MAX_bYr THEN a.aYr ELSE...

Usman Butt (12/1/2011)

---

ChrisM@Work (12/1/2011)

---

Use a crosstab, preaggregating the raw table with either a CTE or a derived table. Here's the derived table version:

SELECT

ItemCode,

Column1 = MAX(CASE WHEN YearShipped = 2010...

Use a crosstab, preaggregating the raw table with either a CTE or a derived table. Here's the derived table version:

SELECT

ItemCode,

Column1 = MAX(CASE WHEN YearShipped = 2010 AND MonthShipped =...

Denise McMillan (11/30/2011)

---

I got it to work by joining just on the left 4 charracters of the number in the subquery.

select number, avalue, (select x.nvalue from valuestbl as...

Hey Jon, thanks loads for posting up sample data - it took a moment to tweak the script but no worries. Try this:

;WITH CTE AS (

SELECT

ID,

ProjectID,

rowDescription,

[Year],...

Hi Jon, welcome to the forum.

Can you post up some sample data? There's a link in my sig which shows how to do this - well worth a read for...

Acquiring sufficient experience, knowledge and understanding of...

CROSS/OUTER APPLY can be surprisingly efficient - read the excellent articles written by Paul White,...

FROM

.

.

.

CROSS APPLY (

SELECT poi.POIDescription, rn = ROW_NUMBER() OVER(ORDER BY r.RequestCode desc)

FROM DMSRequestDocListTbl rdl

INNER JOIN DMSRequestTbl r

ON rdl.RequestCode = r.RequestCode

AND r.RequestTypeID in ( 8,9)

AND r.DocRevID = @DocRevID...

Try using Cross/Outer Apply.

You can easily convert the content into an inline TVF.

Using a temp table containing the PK's of the target table looks like the best option to me:

SELECT sec_no

INTO #sec_no_to_delete

FROM sec_returns m

WHERE NOT EXISTS (

SELECT 1

FROM sec_returns

WHERE...

Mark is innocent!

Jorge Lopes (11/28/2011)

---

There is a slight problem in the third Select statement you have to put 2 spaces between Balanço and ANO

SELECT 'Balanço[space][space]ANO'

CREATE TABLE #desc

( NAME VARCHAR(100))

INSERT INTO #desc

SELECT...

DROP TABLE #desc

CREATE TABLE #desc

( NAME VARCHAR(100))

INSERT INTO #desc

SELECT 'Balanço- Activo não corrente' UNION ALL -- note changed position of hyphen

SELECT 'Balanço - Autonomia Financeira' UNION ALL

SELECT 'Balanço ANO'

SELECT

NAME,...

Character position 9 of two of your strings is a hyphen. There are ways to get around this, for instance you could remove the hyphen and a white space using...