I adapted this syntax to fit my need from a post from @ypercubeᵀᴹ – but in my instance shown below – the Select statement only returns the top most value for the patientID
it does not SUM()
the value for each DoctorName
listed. In this instance, I want the SUM()
for TotalBill
by each DoctorName
What is set-up wrong in the query below?
Declare @TotalBill Table
(
doctorname varchar(100)
,patientid varchar(10)
,totalbill decimal(10,2)
,vdate date
)
Declare @HospitalInfo Table
(
doctorname varchar(100)
,patientid varchar(10)
,vdate date
)
INSERT INTO @TotalBill (doctorname, totalbill, patientid, vdate) VALUES
('Doctor Z', '11.48', '4321', '2017-01-25'),
('Doctor Z', '11804.00', '3431', '2017-01-31'),
('Doctor Z', '879.40', '1111', '2017-01-26'),
('Doctor Z', '11.24', '0089', '2017-01-26'),
('Doctor A', '11.24', '6654', '2017-01-26'),
('Doctor A', '55.11', '0011', '2017-01-26'),
('Doctor A', '66.24', '5466', '2017-01-26'),
('Doctor A', '22.24', '1098', '2017-01-26'),
('Doctor A', '13.24', '9987', '2017-01-26')
Insert Into @HospitalInfo (doctorname, patientid, vdate) Values
('Doctor Z', '1111', '2017-01-26'),
('Doctor Z', '0089', '2017-01-26'),
('Doctor Z', '3431', '2017-01-31'),
('Doctor Z', '4321', '2017-01-25'),
('Doctor A', '6654', '2017-01-26'),
('Doctor A', '0011', '2017-01-26'),
('Doctor A', '5466', '2017-01-26'),
('Doctor A', '1098', '2017-01-26'),
('Doctor A', '9987', '2017-01-26')
Declare @Startdate datetime = '01/01/2017', @Enddate datetime = '01/31/2017'
Select
a.[doctorname]
,a.[patientid]
,[Total Bill] = COALESCE(sumTB,0)
FROM
( SELECT [doctorname]
,patientid
,vdate
,rn = ROW_NUMBER() OVER (PARTITION BY a.[doctorname]
ORDER BY [patientid])
FROM @HospitalInfo As a
) As a
OUTER APPLY
( SELECT sumTB = SUM(b.[totalbill])
FROM @TotalBill b
WHERE a.patientid = b.patientid
AND a.[doctorname] = b.[doctorname]
AND a.rn = 1
) AS b
WHERE CAST([vdate] As Date) BETWEEN @Startdate AND @Enddate
EDIT
My desired output is like the image below. SUM the totalbill – and only show it for ONE patientID. I do not care if it is a random id or the Max() or Min() as longn as it is only one
Best Answer
remove
a.patientid = b.patientid
from theouter apply()
where
clause:results: http://rextester.com/ZWS42033