SQL Clone
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 (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)

Group: General Forum Members
Points: 192 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 (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)

Group: General Forum Members
Points: 192 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
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2350 Visits: 2668
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 (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)

Group: General Forum Members
Points: 192 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
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2350 Visits: 2668
this link will help you in this regard

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

Group: General Forum Members
Points: 192 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
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6662 Visits: 7393
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