Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


result set > just one row for each patientid


result set > just one row for each patientid

Author
Message
mattech06
mattech06
SSC-Enthusiastic
SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)

Group: General Forum Members
Points: 138 Visits: 236
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


TransactionID   TransactionDate   PatientID   PatientFirstName   PatientLastName   Doctor   LedgerDescription   LedgerAmount   LedgerType
133763   2013-04-29 13:04:02.000   90003212   Anita   Button   Hodgson   Card   -110.00   24
133762   2013-04-29 13:04:25.000   90003212   Anita   Button   Hodgson   Private Exam Adult   110.00   13
136198   2013-06-03 11:06:56.000   90003212   Anita   Button   Hodgson   PPP contract - Contract   5235.00   1
136199   2013-06-03 11:06:56.000   90003212   Anita   Button   Hodgson   PPP contract - Initial Fee (Contract)   2535.00   2
137111   2013-06-20 15:06:13.000   90003212   Anita   Button   Hodgson   Card   -2535.00   24
137660   2013-07-01 00:07:00.000   90003212   Anita   Button   Hodgson   Standing Order   -135.00   26
138001   2013-07-01 00:07:00.000   90003212   Anita   Button   Hodgson   PPP contract - Contract Charge   135.00   3
139524   2013-08-01 00:08:00.000   90003212   Anita   Button   Hodgson   Standing Order   -135.00   26
139860   2013-08-01 00:08:00.000   90003212   Anita   Button   Hodgson   PPP contract - Contract Charge   135.00   3
141164   2013-09-01 00:09:00.000   90003212   Anita   Button   Hodgson   Standing Order   -135.00   26
141495   2013-09-01 00:09:00.000   90003212   Anita   Button   Hodgson   PPP contract - Contract Charge   135.00   3
142977   2013-10-01 00:10:00.000   90003212   Anita   Button   Hodgson   Standing Order   -135.00   26
143306   2013-10-01 00:10:00.000   90003212   Anita   Button   Hodgson   PPP contract - Contract Charge   135.00   3
144929   2013-11-01 00:11:00.000   90003212   Anita   Button   Hodgson   Standing Order   -135.00   26
145285   2013-11-01 00:11:00.000   90003212   Anita   Button   Hodgson   PPP contract - Contract Charge   135.00   3
146768   2013-12-01 00:12:00.000   90003212   Anita   Button   Hodgson   Standing Order   -135.00   26
147113   2013-12-01 00:12:00.000   90003212   Anita   Button   Hodgson   PPP contract - Contract Charge   135.00   3
148245   2014-01-01 00:01:00.000   90003212   Anita   Button   Hodgson   Standing Order   -135.00   26
148688   2014-01-01 00:01:00.000   90003212   Anita   Button   Hodgson   PPP contract - Contract Charge   135.00   3
149874   2014-02-01 00:02:00.000   90003212   Anita   Button   Hodgson   Standing Order   -135.00   26
150548   2014-02-01 00:02:00.000   90003212   Anita   Button   Hodgson   PPP contract - Contract Charge   135.00   3



This is the epsreferralkpis table


RefID   dbPatID   dbPatLastName   dbPatBirthday   dbAddDate   FirstName   LastName   dbStaffLastName   dbStatusDesc   dbOtherRefType   RefTypeWord
15861   90003212   Button   1964-03-29 00:03:00.000   2013-04-18 10:04:59.000      Google   Hodgson   Invisalign   1   OTHER



And this is the resulting dataset
patientid   PatientFirstName   patientlastname   dbstatusdesc   dbAddDate   LastName   dbStaffLastName   LedgerType   Charges   Payments   Contracts
90003212   Anita   Button   Invisalign   2013-04-18 10:04:59.000   Google   Hodgson   1   0.0000   0.0000   -5235.0000
90003212   Anita   Button   Invisalign   2013-04-18 10:04:59.000   Google   Hodgson   2   2535.0000   0.0000   0.0000
90003212   Anita   Button   Invisalign   2013-04-18 10:04:59.000   Google   Hodgson   3   1080.0000   0.0000   0.0000
90003212   Anita   Button   Invisalign   2013-04-18 10:04:59.000   Google   Hodgson   13   110.0000   0.0000   0.0000
90003212   Anita   Button   Invisalign   2013-04-18 10:04:59.000   Google   Hodgson   24   0.0000   -2535.0000   0.0000
90003212   Anita   Button   Invisalign   2013-04-18 10:04:59.000   Google   Hodgson   24   0.0000   -110.0000   0.0000
90003212   Anita   Button   Invisalign   2013-04-18 10:04:59.000   Google   Hodgson   26   0.0000   -1080.0000   0.0000
mattech06
mattech06
SSC-Enthusiastic
SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)

Group: General Forum Members
Points: 138 Visits: 236
sorry this posted before I formatted the tables....what's the easiest way of posting tables so the columns and data are aligned??
twin.devil
twin.devil
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1366 Visits: 2594
share the a proper sample data table for us to understand the problem to provide any help.

help us to help u :-)
mattech06
mattech06
SSC-Enthusiastic
SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)

Group: General Forum Members
Points: 138 Visits: 236
this might sound stupid but how do I best do that. I've provided the two data tables in the post above
twin.devil
twin.devil
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1366 Visits: 2594
this link will help you in this regard

http://www.sqlservercentral.com/articles/Best+Practices/61537/
mattech06
mattech06
SSC-Enthusiastic
SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)

Group: General Forum Members
Points: 138 Visits: 236
I'll start a new post for this which has my tables and data in called 'just on row for each patientid 2'
MyDoggieJessie
MyDoggieJessie
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4856 Visits: 7363
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; They'll drag you down to their level and beat you with experience" ;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search