Find duplicates then inner join with another table

Transact-SQL
1

I need to find duplicates in one table and then join those results with another table to calculate the number of clients by ClientType.

Here is the query that identifies the duplicates:

with cte as (
select t.ID,
t.screeningdate,
t.Jurisdiction,
t.name,
t.clientid,
t.submitdate,
count(*) over (partition by ClientID) as dup_count
from tblTest t
)
select *
from cte
where dup_count > 1
and submitdate between '06/01/2024' and '06/30/2024'

Here are the results from this query:

image

Here is the table that needs to be joined, calculate the number of clients and group by the Client Type:

SELECT [ID]
,[FirstName]
,[LastName]
,[MiddleInt]
,[CLIENTID]
,[COUNTYID]
,[SUBMITDATE]
,[ClientType]
FROM [dbo].[tblClient]

This is what the results should look like:

ClientType NumScreened
1 2
2 5
3 18

Thanks.

2

please show how does the expected result looks like

4

I figured out how to join the queries but now I have two queries find the ClientType and add the Screenings from two separate queries.

Here is the first query:

with cte as (
select ID,
screeningdate,
Jurisdiction,
name,
clientid,
submitdate,
count(*) over (partition by ClientID) as dup_count
from tblTest
)
Select m.ClientType, Count(m.ClientID) As ReferredForScreening
From tblClient m
Inner Join cte s on
m.CLIENTID = s.ClientID
Where dup_count > 1
and s.SubmitDate between '06/01/2024' and '06/30/2024'
and COUNTYID = 6
Group by ClientType
Order by ClientType

Here is the second query:

select ClientType,
SUM(CASE WHEN ReferredForScreening = 1 THEN 1 ELSE 0 END) as ReferredForScreening
from tblClient
where SUBMITDATE >= '06/01/2024' and SUBMITDATE < '06/30/2024' and COUNTYID = 6
Group By ClientType
order by ClientType

If the ClientTypes are the same add the ReferredforScreening. Here is what the results should look like:

image