Hi,

sorry no, if the data was

Desc Amt

bob 10

tim 20

jim 30

then I'm after a result set of

Desc Amt

bob 60

so first row...

Thanks Sean, that did the trick...give yourself a 1000 points!

Hi, yes!

SELECT

b.TransDate, b.TransType, b.SortCode, b.AccNum, b.TransDesc,

(ISNULL(b.Debit, 0) + ISNULL (b.Credit, 0)) as Amount,

ISNULL(r.OrthoID, 999) as OrthoID

ISNULL(t.PatientID, 0) as PatientID

FROM

EPSBankTransactions b

...

can you use those functions in the FROM section?

This is the working vers, not my sample from above and includes a ref table as well and there's a bit more...

sorted it out, didn't think it would work for some reason but

LEFT JOIN EPSBankRef r on SUBSTRING(b.TransDesc, 0, 21) = r.BankID

does the job.

The performance seems fine, I'll test it with our biggest client tomorrow but I'll note your comments/links anyway for future ref, thanks.

You're a genius John (or at least someone who knows more sql than me 🙂 thanks.

That's great, thanks guys.

ok 🙂

yep

They both do the job thanks BUT I forgot to mention I wanted to select DISTINCT lastName's, is that possible using a cte?

Hey that seems to work on my test data Mitesh (I'll try it on the live stuff in a mo).

One question tho, why did you add?

AND (r.dbStaffLastName IS NULL OR...

good point, but how do I join them?

cheers!

thanks guys