Or this?

SELECT *

FROM document

WHERE (@dt = 1 AND doctype IN (1, 2))

OR (@dt = 0 AND doctype = 1)

Pimms

What results do you get from this query?

;WITH Deleter AS (

SELECT

ID,

rn = ROW_NUMBER() OVER(ORDER BY Postcode,BuildingName,housenumber,SubBuilding,County,Town,

LocalName,PostOutCode,PostInCode,Throughname,Throughdesc,poboxno,BusinessName,locality)

FROM PostCodesAndAddressesBt

)

SELECT *

FROM Deleter

WHERE rn > 1

Can you post a few rows please Mick, showing some dupes?

Easy.

I could have gone down...

Possibly. If the datetime you are constructing is the upper bound of a range, you will almost certainly find it easier to use the...

The CTE is nothing more than a macro, it will be substituted into the query and result in LEFT() in the join. There's no gain.

Your initialisation code fails with an error. Your expectations are incorrect.

I think this is what you are looking for:

CREATE TABLE #table1(c nvarchar(1));

CREATE TABLE #table2(c nvarchar(1));

INSERT INTO #table1 SELECT '1' UNION...

kapil_kk (9/11/2013)

---

Its not a good practice to use functions like LEFT,...

Dwain C has a nice article covering usage, I've put a link in an earlier post in this thread.

Blimey. I've learned so much from your website over the years. Nice...

Erland Sommarskog (9/11/2013)

---

Somewhat odd to use CROSS APPLY here. The normal is CROSS JOIN. Of course since there is no correlation on the right side, the...

Begin with the query below. Uncomment a single table source at a time, and run through all of...

You're welcome. With no sample data to...

Excellent!

You might get better performance from this query by preaggregating some of those tables feeding the three subqueries, something...

batgirl (9/11/2013)

---

LOL...here's my thought process.

Quick read - looks like it should return 2 rows.

Hmmm...are 2 rows = 2 results? Not really...it's 1 result.

Look again...looks like Steppenwolf will...