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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 MAX Document for each visit

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_id
where billing_number is not null AND billing_number != ''
AND archive_date >= DATEADD(DD, -1, GETDATE())
Group by doctype, patient_type, billing_number
order 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 output
Distinct 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 seq
from AM inner join VV on AM.visit_id=VV.visit_id
where billing_number is not null AND billing_number != ''
AND archive_date >= DATEADD(DD, -1, GETDATE())
Group by doctype, patient_type, billing_number
)t
where seq=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

babloo
Starting Member

35 Posts

Posted - 2013-06-21 : 15:52:19
Thanks alot Visakh..
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-22 : 02:30:31
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -