CMCITD
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.
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.
ASKER
Stating invalid column name 'VisitID' on the last c.VisitID line
Thanks for helping!
Thanks for helping!
Ohh yes, sorry. Please try this -
Hope it helps!
--
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
--
Hope it helps!
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.
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 -
Hope it helps!
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
--
Hope it helps!
Sorry the above will not work. Let me just rewrite it.
Hi,
Please try this -
Hope it helps!
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
Hope it helps!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That's got it, thank you!!
Glad to help!
Please try this -
Open in new window
Hope it helps!