sorry, I must have misunderstood. have a good weekend too!

I've solved this now simply by putting this statement within a SUM statement (the same as for charges and payments)

(CASE WHEN t.LedgerType = 1 THEN t.LedgerAmount ELSE 0.00 END) *...

Thanks Igor, but unfortunately we don't have our Contracts column with that sql.

re

(CASE WHEN t.LedgerType = 1 THEN t.LedgerAmount ELSE 0.00 END) * -1 AS

sorry yeah, forgot the sql!

SELECT

r.dbPatID, t.PatientFirstName, t.patientlastname, r.dbstatusdesc, r.dbAddDate, r.LastName, r.dbStaffLastName,

SUM(CASE WHEN t.LedgerAmount > 0 AND t.LedgerType != 1 THEN t.LedgerAmount ELSE 0.00 END) AS Charges,

...

I'll start a new post for this which has my tables and data in called 'just on row for each patientid 2'

Thanks for you're help guys. YB1D is the sql champion winner here! Eugene was also correct but as he mentioned we'd still get some multiple rows. Car, I've not tried...

this might sound stupid but how do I best do that. I've provided the two data tables in the post above

sorry this posted before I formatted the tables....what's the easiest way of posting tables so the columns and data are aligned??

Top draw! Thank you.

Thanks, I've actually worked out we don't need to select the RefTypeWord, so removing this from the select and making dbPatID distinct bought back the correct amount of rows.

I compared...

The current sql will bring back

900032592013-05-28 16:05:13.000GroblerDOCTOR

900032592013-05-28 16:05:13.000GroblerOTHER

Where as I want it to bring back just one row for each dbPatID, I'm not bothered which RefTypeWord it is

thanks

Hi Guys, thanks for your help. I found simply adding distinct has done the trick

Select distinct EPSAgedBalances.dbPatID, EPSAgedBalances.dbPatFirstName, EPSAgedBalances.dbPatLastName,

EPSAllContracts.dbStaffLastName, EPSAgedBalances.TotTot

from EPSAgedBalances, EPSAllContracts

where EPSAllContracts.dbPatID = EPSAgedBalances.dbPatID

and TotTot...

Yep that works, cheers Jeff

cheers Jeff, it is a DATETIME column so I shall try your solutions now. Just a quick question would both examples SELECT any dates that fall with Jan 2013 (eg...

forgot to mention, the dates are stored in 2013-01-31 format.