Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

result set > just one row for each patientid Expand / Collapse
Author
Message
Posted Tuesday, February 11, 2014 3:33 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, August 22, 2014 7:14 AM
Points: 96, Visits: 159
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
Post #1540112
Posted Tuesday, February 11, 2014 3:34 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, August 22, 2014 7:14 AM
Points: 96, Visits: 159
sorry this posted before I formatted the tables....what's the easiest way of posting tables so the columns and data are aligned??
Post #1540113
Posted Tuesday, February 11, 2014 3:38 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 4:00 AM
Points: 747, Visits: 1,313
share the a proper sample data table for us to understand the problem to provide any help.

help us to help u
Post #1540115
Posted Tuesday, February 11, 2014 3:44 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, August 22, 2014 7:14 AM
Points: 96, Visits: 159
this might sound stupid but how do I best do that. I've provided the two data tables in the post above
Post #1540117
Posted Tuesday, February 11, 2014 3:56 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 4:00 AM
Points: 747, Visits: 1,313

this link will help you in this regard

http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1540119
Posted Saturday, February 15, 2014 5:41 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, August 22, 2014 7:14 AM
Points: 96, Visits: 159
I'll start a new post for this which has my tables and data in called 'just on row for each patientid 2'
Post #1541839
Posted Saturday, February 15, 2014 8:43 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 9:12 AM
Points: 3,926, Visits: 7,158
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"
Post #1541852
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse