1

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

Image

  • Can you give us a clue what your desired output looks like? I like to see rows with values rather than reverse engineer word problems. – Aaron Bertrand Feb 9 '17 at 19:25
  • @AaronBertrand -- see my edit. I want the SUM(totalbill) returned, but for only one patientID. It does not matter to me if it is a random patientID or the Min() or Max(). Whatever is easier to return. – BellHopByDayAmetuerCoderByNigh Feb 9 '17 at 19:32
  • 2
    Try removing a.patientid = b.patientid from the outer apply. – ypercubeᵀᴹ Feb 9 '17 at 19:36
  • @ypercubeᵀᴹ - that got it. Such a simple SQL mistake, ruining my desired output. Thank you. – BellHopByDayAmetuerCoderByNigh Feb 9 '17 at 19:38
  • Why is a join required? Seems everything in your result comes from @TotalBill and there is a lot of redundant info in @HospitalInfo. – Aaron Bertrand Feb 9 '17 at 19:43
2

remove a.patientid = b.patientid from the outer apply() where clause:

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

results: http://rextester.com/ZWS42033

+------------+-----------+------------+
| doctorname | patientid | Total Bill |
+------------+-----------+------------+
| Doctor A   |      0011 | 168,07     |
| Doctor A   |      1098 | 0,00       |
| Doctor A   |      5466 | 0,00       |
| Doctor A   |      6654 | 0,00       |
| Doctor A   |      9987 | 0,00       |
| Doctor Z   |      0089 | 12706,12   |
| Doctor Z   |      1111 | 0,00       |
| Doctor Z   |      3431 | 0,00       |
| Doctor Z   |      4321 | 0,00       |
+------------+-----------+------------+
  • This is probably right. You're far less lazy about re-coding than I am. – Erik Darling Feb 9 '17 at 19:28
  • @sp_BlitzErik I'm not so sure it's this simple... I'm guessing the example data doesn't match his use case. – SqlZim Feb 9 '17 at 19:30
  • Well, yeah, but you have my vote anyway. – Erik Darling Feb 9 '17 at 19:31
  • @SqlZim - see my edit. I want to return each patientID - but SUM() the totalbill amount and display it for only one TotalBill per doctorname. – BellHopByDayAmetuerCoderByNigh Feb 9 '17 at 19:33
  • 2
    Nope! I pulled patientid out, assuming people wanted results that would make sense :) – Erik Darling Feb 9 '17 at 19:41
3

You seem to be making the query a lot more complicated than it needs to be, and while I agree with Erik that the results you want don't seem to make any logical sense (if you want total bills to a doctor, then why have individual rows for each patient?), here is a simpler way:

DECLARE @Startdate date = '20170101', 
        @Enddate   date = '20170131';

;WITH data AS
(
  SELECT doctorname, patientid, bill = SUM(totalbill) OVER (PARTITION BY doctorname),
    r = ROW_NUMBER() OVER (PARTITION BY doctorname ORDER BY doctorname)
  FROM @TotalBill
  WHERE vdate >= @Startdate
    AND vdate <  DATEADD(DAY,1,@Enddate)
)
SELECT doctorname, patientid, totalbill = COALESCE(CASE r WHEN 1 THEN bill END, 0)
FROM data;

A couple of notes:

  • don't use datetime for your variables when the column is date
  • don't use messy and unnecessary CONVERT() functions (why convert date to date?)
  • don't use unsafe regional formats like 'mm/dd/yyyy' - further reading
  • don't use BETWEEN for any range queries - further reading
    • (While it's true that BETWEEN works fine for date queries, it is far too easy to copy that logic elsewhere, where it doesn't work - just be consistent.)
  • also see this post about "dating responsibly"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.