Link to home
Start Free TrialLog in
Avatar of CMCITD
CMCITDFlag for United States of America

asked on

Count with a subquery showing details

Right now I have;

select COUNT(DISTINCT av.VisitID) as count, av.Name, ap.PrimaryCareID from AdmVisits av
JOIN AdmProviders ap ON av.VisitID = ap.VisitID
where av.LocationID = 'ER'
AND av.ServiceDateTime >= '20161001' AND av.ServiceDateTime <= '20161231'
GROUP BY av.Name, ap.PrimaryCareID
HAVING COUNT(*) > 1


This gives me a good result where it shows;

Count        Name              PrimcaryCareID
2                Patient X          DrLongFinger

However, I've now been asked to show the details of each of those visits (What my distinct count is looking at---av.VisitID).  So I need to add the fields, av.ReasonForVisit, av.ServiceDatetime.  The idea is it would show;

Count        Name              PrimaryCareID
2                Patient X          DrLongFinger
                               Smashed Finger                 2016-10-01
                               Chest Pain                           2016-10-01
4                Patient S          DrPain
                               Dog Bite                               2016-10-31
                               Dragon Attack                     2016-10-31
                               Dehydrated                         2016-10-31
                               SQL Migraine                      2016-10-31


Now, I understand I cannot get that format in SQL--so my plan was to pull VisitID across, put the command line in Crystal, then tie the table together with the command using VisitID.  Then I could just Group it up in Crystal, put the ReasonForVisit/Dates in the detail section in Crystal and have a nice format with all visits > 2, and their details.

However, I cannot pull the AdmVisitID into the query of course due to the group by--and if I had that to the aggregate function it breaks it due to the count.  So basically I'm trying to COUNT AND get the details of each account # IF a patient has 2+ visits in that date range.  Hope that makes sense.
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Hi CMCITD,
Please try this -

--

;WITH CTE AS
(
	select COUNT(DISTINCT av.VisitID) as count, av.Name, ap.PrimaryCareID 
	from AdmVisits av
	JOIN AdmProviders ap ON av.VisitID = ap.VisitID
	where av.LocationID = 'ER' 
	AND av.ServiceDateTime >= '20161001' AND av.ServiceDateTime <= '20161231'
	GROUP BY av.Name, ap.PrimaryCareID
	HAVING COUNT(*) > 1
)
SELECT c.*,k.* FROM CTE c
CROSS APPLY 
(
	SELECT av.ReasonForVisit, av.ServiceDatetime
	FROM AdmVisits av
	WHERE av.VisitID = c.VisitID
)k

--

Open in new window


Hope it helps!
Avatar of CMCITD

ASKER

Stating invalid column name 'VisitID' on the last c.VisitID line

Thanks for helping!
Ohh yes, sorry. Please try this -

--
SELECT COUNT(DISTINCT av.VisitID) as count, av.Name, ap.PrimaryCareID , k.*
from AdmVisits av
JOIN AdmProviders ap ON av.VisitID = ap.VisitID
CROSS APPLY ( SELECT av1.ReasonForVisit, av1.ServiceDatetime FROM AdmVisits av1 WHERE av1.VisitID = av.VisitID AND av1.VisitID = ap.VisitID )k
where av.LocationID = 'ER' 
AND av.ServiceDateTime >= '20161001' AND av.ServiceDateTime <= '20161231'
GROUP BY av.Name, ap.PrimaryCareID
HAVING COUNT(*) > 1
--

Open in new window


Hope it helps!
Avatar of CMCITD

ASKER

Hey Pawan,

Receiving the following;

Msg 8120, Level 16, State 1, Line 2
Column 'k.ReasonForVisit' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Hi,
Updated -

--
SELECT COUNT(DISTINCT av.VisitID) as count, av.Name, ap.PrimaryCareID
from AdmVisits av
JOIN AdmProviders ap ON av.VisitID = ap.VisitID
CROSS APPLY ( SELECT av1.ReasonForVisit, av1.ServiceDatetime FROM AdmVisits av1 WHERE av1.VisitID = av.VisitID AND av1.VisitID = ap.VisitID )k
where av.LocationID = 'ER' 
AND av.ServiceDateTime >= '20161001' AND av.ServiceDateTime <= '20161231'
GROUP BY av.Name, ap.PrimaryCareID
HAVING COUNT(*) > 1
--

Open in new window


Hope it helps!
Sorry the above will not work. Let me just rewrite it.
Hi,
Please try this -

SELECT * FROM
(
	SELECT 
		  COUNT(DISTINCT av.VisitID) OVER (PARTITION BY av.Name, ap.PrimaryCareID) cnt
		, av.Name, ap.PrimaryCareID , av.ReasonForVisit, av.ServiceDatetime
	FROM AdmVisits av
	JOIN AdmProviders ap ON av.VisitID = ap.VisitID
	where av.LocationID = 'ER' 
	AND av.ServiceDateTime >= '20161001' AND av.ServiceDateTime <= '20161231'
)k WHERE cnt > 1

Open in new window


Hope it helps!
ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of CMCITD

ASKER

That's got it, thank you!!
Glad to help!