Query to count physician encounters

I need to count the total number of patient visits accumulated by each
provider in a medical practice between selected dates. I am using the table
of charges. Each visit may include several individual charges during the
visit, but I want to count the visit only once. Therefore I used this
statement (the date range is not actually hard coded):
SELECT DISTINCT ProviderCode, PatUniqueID, service_date
FROM Charges
WHERE service_date Between '01/01/1995' And '12/31/2005' ;
What I get is:
Provider Code PatUniqueID service_date
-- -- --
RMB 25AAAAAA 01/01/2005
RMB 983AAAAA 02/01/2005
etc.
However, what I readlly want is a count. How many of these lines occur for
provider RMB and each provider. The desired table would be:
Provider Code Encounter Count
-- --
RMB 25
JDR 51
etc.
I am hoping to do this in a single SQL query statements, possibly with
imbedded SELECT statements. Can anyone help me to write this statement,
please.Richard,
If the query you show returns exactly the rows you want to count
(by Provider Code), you can build from this query:
select ProviderCode, count(*) as EncounterCount from (
SELECT DISTINCT ProviderCode, PatUniqueID, service_date
FROM Charges
WHERE service_date Between '01/01/1995' And '12/31/2005'
) as RowsToCount
group by ProviderCode
Steve Kass
Drew University
richardb wrote:

>I need to count the total number of patient visits accumulated by each
>provider in a medical practice between selected dates. I am using the table
>of charges. Each visit may include several individual charges during the
>visit, but I want to count the visit only once. Therefore I used this
>statement (the date range is not actually hard coded):
>SELECT DISTINCT ProviderCode, PatUniqueID, service_date
>FROM Charges
>WHERE service_date Between '01/01/1995' And '12/31/2005' ;
>What I get is:
>Provider Code PatUniqueID service_date
>-- -- --
>RMB 25AAAAAA 01/01/2005
>RMB 983AAAAA 02/01/2005
>etc.
>However, what I readlly want is a count. How many of these lines occur for
>provider RMB and each provider. The desired table would be:
>Provider Code Encounter Count
>-- --
>RMB 25
>JDR 51
>etc.
>I am hoping to do this in a single SQL query statements, possibly with
>imbedded SELECT statements. Can anyone help me to write this statement,
>please.
>
>|||Right! Got it and thanks...
"Steve Kass" wrote:

> Richard,
> If the query you show returns exactly the rows you want to count
> (by Provider Code), you can build from this query:
> select ProviderCode, count(*) as EncounterCount from (
> SELECT DISTINCT ProviderCode, PatUniqueID, service_date
> FROM Charges
> WHERE service_date Between '01/01/1995' And '12/31/2005'
> ) as RowsToCount
> group by ProviderCode
> Steve Kass
> Drew University
> richardb wrote:
>
>