September 15, 2015 at 10:48 pm
newbie just trying to figure this out. Trying to get the max count grouped by all the fields. All the fields are the same, but trying to get the location for each physician that has the largest number of patients.
if the output for the sql below is:
101, 10, Jon, Smith, MD, Ortho, OR, 15
101, 10, Jon, Smith, MD Ortho, 1, 12
101, 10, Jon, Smith, MD, Ortho, 2, 10
24, 3, Mike, Jones, MD, Neuro, OR, 21
24, 3, Mike, Jones, MD, Neuro, 2, 43
I'd like to have the query rewritten so the results are as:
101, 10, Jon, Smith, MD, Ortho, OR, 15
24, 3, Mike, Jones, MD, Neuro, 2, 43
SELECT
a.attendingmdkey,e.[provider id],e.[first name],e.[last name],e.title,e.specialty,l.locationname,count(a.accountid) as Count
FROM accounts a
left outer join location l on l.locationid=a.locationid
left outer join providers e on e.[ProviderID]=a.attendingmdkey
where a.dischargedate>='2014-12-01' and a.dischargedate<'2015-01-01'
and a.divisioncode in ('1','2','$')
group by a.AttendingMdKey,e.[provider id],e.[first name],e.[last name],e.title,e.Specialty,l.locationname
order by a.AttendingMdKey
September 15, 2015 at 11:24 pm
Since you're new here, and we can't really help without some create table scripts and some data.... Please read this[/url]
It's not that we don't want to help, it's just that without enough information, it's hard. =)
If you post the create table and insert scripts, you'll get a tested and working solution.
September 15, 2015 at 11:28 pm
You can try this and see if it does what you are looking for.
declare @t table
(
MD_key int,
Provider_ID int,
FName char(10),
LName char(10),
Title char(2),
Specialty char(10),
Abbr char(2),
Cnt_Num int
)
insert @t (MD_Key, Provider_ID, FName, LName, Title, Specialty, Abbr, Cnt_Num) values
(101, 10, 'Jon', 'Smith', 'MD', 'Ortho', 'OR', 15),
(101, 10, 'Jon', 'Smith', 'MD', 'Ortho', '1', 12),
(101, 10, 'Jon', 'Smith', 'MD', 'Ortho', '2', 10),
(24, 3, 'Mike', 'Jones', 'MD', 'Neuro', 'OR', 21),
(24, 3, 'Mike', 'Jones', 'MD', 'Neuro', '2', 43)
;with Accounts_cte as
(
Select MD_Key, Provider_ID, Fname, LName, Title, Specialty, Abbr, Cnt_Num,
Row_Number() Over(Partition by MD_Key order by Cnt_Num desc) RowNum
from @t
)
Select *
from Accounts_cte a
where a.RowNum = 1
order by a.cnt_num
GROUP is not really the best choice in this situation. A windows function allows you to get the highest value per MD_Key as well as all the other fields without have to group by them.
Treat the cte as any table. You can add your joins to it just like you would any other table.
Please note how I formatted the data and ddl for future posts. It makes it much easier and faster to load it and find a solution. Plus, I would have used your table names instead of making up my own!
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 16, 2015 at 2:32 am
Dunno if this might work?
SELECTa.attendingmdkey,e.[provider id],e.[first name],e.[last name],e.title,
e.specialty,l.locationname,
[Count]
FROM(
SELECTa.attendingmdkey, a.locationid, COUNT(a.accountid) AS [Count],
Row_Number() Over(
Partition by a.attendingmdkey
order by COUNT(a.accountid) desc) AS RowNum
FROMaccounts AS a
where a.dischargedate>='2014-12-01'
and a.dischargedate<'2015-01-01'
and a.divisioncode in ('1','2','$')
GROUP BY a.attendingmdkey
) AS A
left outer join location l on l.locationid=a.locationid
left outer join providers e on e.[ProviderID]=a.attendingmdkey
WHERERowNum = 1
I'm not sure about the "order by COUNT(a.accountid)" in the Row_Number() clause ...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply