result set > just one row for each patientid

Question

result set > just one row for each patientid

mattech06

SSCrazy

Points: 2484
More actions
February 11, 2014 at 3:33 am

#283545

Hi,
I have this sql
SELECT
r.dbPatID, t.PatientFirstName, t.patientlastname, r.dbstatusdesc, r.dbAddDate, r.LastName, r.dbStaffLastName,t.LedgerType,
SUM(CASE WHEN t.LedgerAmount > 0 AND t.LedgerType != 1 THEN t.LedgerAmount ELSE 0.00 END) AS Charges,
SUM(CASE WHEN t.LedgerAmount < 0 AND t.LedgerType != 1 THEN t.LedgerAmount ELSE 0.00 END) AS Payments,
(CASE WHEN t.LedgerType = 1 THEN t.LedgerAmount ELSE 0.00 END) * -1 AS Contracts
FROM
EPSReferralKPIs r
LEFT JOIN EPSTransactions t ON t.PatientID = r.dbPatID
GROUP BY
r.dbPatID, t.PatientFirstName, t.patientlastname, r.dbstatusdesc, r.dbAddDate, r.LastName, r.dbStaffLastName, t.LedgerType, t.LedgerAmount
So for example patientid = 1. There are 21 rows in the epstransactions table and 1 row in the epsreferralkpi's table and the result set displays 7 rows!?
What I actually want is just one row for each patient where we have a total sum for Charges, Payments and Contracts.
any help would be greatly appreciated.
thanks
This is the transaction epstransaction table
TransactionIDTransactionDatePatientIDPatientFirstNamePatientLastNameDoctorLedgerDescriptionLedgerAmountLedgerType
1337632013-04-29 13:04:02.00090003212AnitaButtonHodgsonCard-110.0024
1337622013-04-29 13:04:25.00090003212AnitaButtonHodgsonPrivate Exam Adult110.0013
1361982013-06-03 11:06:56.00090003212AnitaButtonHodgsonPPP contract - Contract5235.001
1361992013-06-03 11:06:56.00090003212AnitaButtonHodgsonPPP contract - Initial Fee (Contract)2535.002
1371112013-06-20 15:06:13.00090003212AnitaButtonHodgsonCard-2535.0024
1376602013-07-01 00:07:00.00090003212AnitaButtonHodgsonStanding Order-135.0026
1380012013-07-01 00:07:00.00090003212AnitaButtonHodgsonPPP contract - Contract Charge135.003
1395242013-08-01 00:08:00.00090003212AnitaButtonHodgsonStanding Order-135.0026
1398602013-08-01 00:08:00.00090003212AnitaButtonHodgsonPPP contract - Contract Charge135.003
1411642013-09-01 00:09:00.00090003212AnitaButtonHodgsonStanding Order-135.0026
1414952013-09-01 00:09:00.00090003212AnitaButtonHodgsonPPP contract - Contract Charge135.003
1429772013-10-01 00:10:00.00090003212AnitaButtonHodgsonStanding Order-135.0026
1433062013-10-01 00:10:00.00090003212AnitaButtonHodgsonPPP contract - Contract Charge135.003
1449292013-11-01 00:11:00.00090003212AnitaButtonHodgsonStanding Order-135.0026
1452852013-11-01 00:11:00.00090003212AnitaButtonHodgsonPPP contract - Contract Charge135.003
1467682013-12-01 00:12:00.00090003212AnitaButtonHodgsonStanding Order-135.0026
1471132013-12-01 00:12:00.00090003212AnitaButtonHodgsonPPP contract - Contract Charge135.003
1482452014-01-01 00:01:00.00090003212AnitaButtonHodgsonStanding Order-135.0026
1486882014-01-01 00:01:00.00090003212AnitaButtonHodgsonPPP contract - Contract Charge135.003
1498742014-02-01 00:02:00.00090003212AnitaButtonHodgsonStanding Order-135.0026
1505482014-02-01 00:02:00.00090003212AnitaButtonHodgsonPPP contract - Contract Charge135.003
This is the epsreferralkpis table
RefIDdbPatIDdbPatLastNamedbPatBirthdaydbAddDateFirstNameLastNamedbStaffLastNamedbStatusDescdbOtherRefTypeRefTypeWord
1586190003212Button1964-03-29 00:03:00.0002013-04-18 10:04:59.000GoogleHodgsonInvisalign1OTHER
And this is the resulting dataset
patientidPatientFirstNamepatientlastnamedbstatusdescdbAddDateLastNamedbStaffLastNameLedgerTypeChargesPaymentsContracts
90003212AnitaButtonInvisalign2013-04-18 10:04:59.000GoogleHodgson10.00000.0000-5235.0000
90003212AnitaButtonInvisalign2013-04-18 10:04:59.000GoogleHodgson22535.00000.00000.0000
90003212AnitaButtonInvisalign2013-04-18 10:04:59.000GoogleHodgson31080.00000.00000.0000
90003212AnitaButtonInvisalign2013-04-18 10:04:59.000GoogleHodgson13110.00000.00000.0000
90003212AnitaButtonInvisalign2013-04-18 10:04:59.000GoogleHodgson240.0000-2535.00000.0000
90003212AnitaButtonInvisalign2013-04-18 10:04:59.000GoogleHodgson240.0000-110.00000.0000
90003212AnitaButtonInvisalign2013-04-18 10:04:59.000GoogleHodgson260.0000-1080.00000.0000

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

mattech06 SSCrazy Points: 2484 More actions · Answer 1

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

Umer Akram SSC-Insane Points: 22209 More actions · Answer 2

share the a proper sample data table for us to understand the problem to provide any help.

help us to help u 🙂

mattech06 SSCrazy Points: 2484 More actions · Answer 3

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

Umer Akram SSC-Insane Points: 22209 More actions · Answer 4

this link will help you in this regard

http://www.sqlservercentral.com/articles/Best+Practices/61537/

mattech06 SSCrazy Points: 2484 More actions · Answer 5

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

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 6

mattech06 (2/15/2014)
I'll start a new post for this which has my tables and data in called 'just on row for each patientid 2'

Please see your other post: http://www.sqlservercentral.com/Forums/Topic1541840-391-1.aspx

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience