Here's an alternative to Abu Dinas' suggestion;

SELECT

b.dbPatID,

b.dbPatFirstName,

b.dbPatLastName,

x.dbStaffLastName,

b.TotTot

FROM EPSAgedBalances b

CROSS APPLY (

SELECT TOP 1 dbStaffLastName

FROM EPSAllContracts c

WHERE c.dbPatID = b.dbPatID

) x

WHERE b.TotTot < 0

Not...

SELECT CounterDateTime = CAST(CAST(CounterDateTime AS CHAR(24)) AS DATETIME)

FROM (SELECT CounterDateTime = '2013-12-30 12:09:00.123' + CHAR(10)) d

-- Msg 241, Level 16, State 1, Line 1

-- Conversion failed when converting date and/or...

Try this - it's called a CROSSTAB query:

SELECT id,

LevelDesc1 = MAX(CASE WHEN levelid = 1 THEN leveldesc ELSE NULL END),

LevelDesc2 = MAX(CASE WHEN levelid = 2 THEN leveldesc ELSE NULL...

The return value of a stored procedure is traditionally used for this:

RETURN 0 if the procedure ran successfully

RETURN -n if the procedure failed.

ALTER PROCEDURE TestOutput @Outcome CHAR (4)

AS IF...

So long as you are aware that SUM()...

dwain.c (12/31/2013)

---

ChrisM@Work (12/31/2013)

---

Stefan Krzywicki (12/30/2013)

---

wolfkillj (12/30/2013)

---

Stefan Krzywicki (12/30/2013)

---

wolfkillj (12/30/2013)

---

GilaMonster (12/30/2013)

---

wolfkillj (12/30/2013)

---

is there really a performance problem or are you just "tuning" the query as a knee-jerk response to seeing a...

You're welcome, thanks for posting the final query. If it works for you and generates the results you want, then that's good and you're probably done - but I'd recommend...

Can you post your solution please? It's closure for your thread, offers closure for those who have helped you, and may help others who stumble upon this thread whilst seeking...

Is '2013-04-01' a date datatype or a string datatype?

You are...

The good ol' ROW_NUMBER() trick might be worth a try. A few tests on a similar-sized table here indicates relative costs of 20% for the table scan, 30% for the...

Split thread. Please post replies to the original thread here, where you will obtain background information and progress to date.

Split thread. Please post replies to the original thread here, where you will obtain background information and progress to date.

Here's the very first query from your very first post, reformatted and with table aliases applied for readability:

SELECT

SUM(Amount=ABS((

SELECT Top 1 Amount + VATAmount

FROM FAAccKitty k1 WITH(NOLOCK)

Where k1.PlaceID...

Can you post your solution please? It provides closure for this thread and for those...

Stefan Krzywicki (12/30/2013)

---

wolfkillj (12/30/2013)

---

Stefan Krzywicki (12/30/2013)

---

wolfkillj (12/30/2013)

---

GilaMonster (12/30/2013)

---

wolfkillj (12/30/2013)

---

is there really a performance problem or are you just "tuning" the query as a knee-jerk response to seeing a scan in...