Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
babloo
Starting Member
35 Posts |
Posted - 2013-06-21 : 11:21:08
|
Hi, I am trying to create a Query that will give me Max number of documents in a visit there could be thousands of visits but I want only 1 visit which has the most documents based on the document type and the patient type.Here is my Query I am working on:select distinct doctype, patient_type, billing_number, count(doctype) from AM inner join VV on AM.visit_id=VV.visit_idwhere billing_number is not null AND billing_number != ''AND archive_date >= DATEADD(DD, -1, GETDATE())Group by doctype, patient_type, billing_numberorder by count(doctype)Current output gives me the list of documents then patient types and all the billing numbers with count.I would like to get this outputDistinct document type then grouped by Patient type and the only visit which has the most documents in that document type. |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-21 : 12:42:59
|
[code]select *from(select doctype, patient_type, billing_number, count(doctype) ,row_number() over (partition by doctype, patient_type order by count(doctype) desc) as seqfrom AM inner join VV on AM.visit_id=VV.visit_idwhere billing_number is not null AND billing_number != ''AND archive_date >= DATEADD(DD, -1, GETDATE())Group by doctype, patient_type, billing_number)twhere seq=1[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
babloo
Starting Member
35 Posts |
Posted - 2013-06-21 : 15:52:19
|
Thanks alot Visakh.. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-22 : 02:30:31
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|