Can you post up a sample data script please Pete? I can't see why you should have to aggregate twice. Cheers.

James.Rivers (12/3/2014)

---

Thanks for all your responses.

I wanted to avoid creating a temp table for this process so I have opted to use the

SELECT LocationId, MaterialId, Duration

FROM (

SELECT LocationId,...

Andrew Kernodle (12/3/2014)

---

Rough guess here, as there isn't too much information about your table; I'd guess you have some sort of patient ID number, or something like it, to identify...

Have you tried sending the whole query to the linked server, something like this?

SELECT *

FROM OPENQUERY([mhsvi-sql2012a\instance1],

'SELECT

CONCAT(CONVERT(varchar(5),LEFT(IMADD.Postcode, 5),101)

,

''/''

,

CASE

WHEN x.Age <= 24 THEN ''A''

WHEN x.Age BETWEEN 25 AND 34 THEN...

or

SELECT LocationId, MaterialId, Duration

FROM (

SELECT LocationId, MaterialId, Duration,

VAR(Duration) OVER (partition by MaterialID) AS VarDuration

FROM #t

) d

WHERE VarDuration > 0

😉

-- Sample data

IF OBJECT_ID ('tempdb..#Locations') IS NOT NULL DROP TABLE #Locations

CREATE TABLE #Locations (LOCATIONID VARCHAR(5), MATERIALID VARCHAR(5), DURATION SMALLINT)

INSERT INTO #Locations VALUES

('PlayM', 'Clip1', 626),

('PlayB', 'Clip1', 626),

('Ing', 'Clip1', 626),

('PlayM', 'Clip2',...

andrew_dale (12/2/2014)

---

1 year old still, the day has not rolled over to 1st March

select '20120228' , FLOOR((CONVERT(INT,'20140228') - CONVERT(INT,'20120228'))/10000) ,...

Try any date pair (DOB and reference date) where the month and the day are the...

andrew_dale (12/2/2014)

---

If you only need age in years, then the following is easier as it does not need you to create a function so can be used directly in a...

Yes it is. Why not code it up as an inline table-valued function instead of a UDF? You're likely to get far better performance. Get the query to work and...

Boby B Jacob (11/27/2014)

---

Hi Chris, scenario is made available by...

You have...

If you can provide some readily-consumable sample data, someone will be more likely to put together a query for you.

TomThomson (11/26/2014)

---

Hugo Kornelis (11/26/2014)

---

Sorry for the harsh words, but this is absolute rubbish.

The code in option 2 will always do two lookups. One for the EXISTS, and then another one...

skanker (11/26/2014)

---

I misunderstood what the question was asking so chose neither.

I can see now from the discussion that this was testing other things - however based on just the question...