It's not rocket science, converting a query into a stored procedure or vice versa. So why not let us assume that you are writing a stored procedure? Then if you...

The Dixie Flatline (8/11/2010)

---

I was just remembering one of my favorite cartoons from "The Far Side", Chris. 🙂

The comics world lost a great humorist when...

sc-w (8/11/2010)

---

Thanks for this.

Could you do me a favour? I'm not getting my head round how this works.

The part of my query below is the bit that works out...

The Dixie Flatline (8/11/2010)

---

GilaMonster (8/10/2010)

---

No, just beef to start with, wouldn't want to scare the poor Americans :-D. Kudu biltong...

CELKO (8/11/2010)

---

...Using table valued functions is a bad programming technique that people who just don't understand declarative programming use to make the code feel like OO. This will mess up...

sc-w (8/11/2010)

---

Well this does not matter in this instance i know they are unique based on their stu_id and i just want their contact deatils to populate the wce_contact tables....

donmakaveli41 (8/11/2010)

---

well I tried the little query you gave me, but I get the same error :

The column prefix 'a' does not match with a table name or alias...

The order by in the window function in the CTE in your original query will return a random row from the available dupes.

Is this intentional?

donmakaveli41 (8/11/2010)

---

Yes I've edited my post to correct this. It's not like that...

ROTFLOL! 😀

What about alias ata?

where ata.type_name = a.type_name AND at.simulator = 0 AND f.atd IS NOT NULL

If I were you I'd chuck out this query and start again, it looks like...

dennisv (8/11/2010)

---

...

I'm hoping someone can combine these (or at least the last two) for me which should speed it up a lot.

I know this will take some time and thank...

-- make some sample data

DROP TABLE #Archive

DROP TABLE #Current

CREATE TABLE #Current (objectId INT IDENTITY(1,1), Data1 VARCHAR(5), lastModified DATETIME)

INSERT INTO #Current (Data1, lastModified)

SELECT 'A', GETDATE()-5 UNION ALL

SELECT 'B', GETDATE()-4 UNION ALL

SELECT...

You could try preaggregating the source query:

;WITH ParameterList AS (

SELECT convert(varchar(max),decryptbypassphrase('SKey',PARAMETERNAME)) PARAMETERNAME

FROM MST_PARAMETER

),

Preaggregate AS (

SELECT PARAMETERNAME, -- extra columns required here

Col1 = SUM(Col1),

Col2 = SUM(Col2),

Col3 =...

LutzM (8/10/2010)

---

GilaMonster (8/10/2010)

---

...Love to, but it wouldn't be allowed through customs on this side. Same with honey, fruit and meat products etc. Otherwise I'd bring biltong when I visit...