SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 MAX Document for each visit
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

babloo
Starting Member

USA
33 Posts

Posted - 06/21/2013 :  11:21:08  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 06/21/2013 :  12:42:59  Show Profile  Reply with Quote

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


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

babloo
Starting Member

USA
33 Posts

Posted - 06/21/2013 :  15:52:19  Show Profile  Reply with Quote
Thanks alot Visakh..
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 06/22/2013 :  02:30:31  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000