SQL for Service Count per user

Hi Experts,

I have the attached table structure showing only related fields to this questions. For those columns without a heading, I just inserted the client name to reference in this question

I need to show the number of clients served by a given user by service.

For example, user Nataliia had 3 clients (Alissia, Peter and Rose). Under the progress_details you will see that Rose needs help with Family Matters and Senior Support.

Therefore, Nataliia is serving clients that need

1. Senior Support
1. Family Matters

This is part 1.

Part 2 is that If possible, I also need to account for intake.services. So, also under Nataliia:

Alissia needs 12 (Debt Management) and 10 (Employment)

Peter needs Housing

and Rose needs Family Matters and Senior Support.

So, from part 1 plus part 2, Nataliia's final report should be.

1. Senior Support *
1. Family Matters *
1. Debt Management
1. Employment
1. Housing

*Since Senior Support and Family Matters are for Rose, both in Intake and in Progress Details they are for the same client and therefore counted once, but if Peter also require Family Matters then for Nataliia Family Matters would have been 2.

If part 2 is too complex, then I could run a separate query and calculate it with php. But, I don't know how to approach part 1 as well. I would definitely need distinct client_id.
Table-Structure.xlsx
APD TorontoAsked:
Who is Participating?
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
While I am sure Pat could come up with something more elegant, and given my experience with MySQL, because part1 and part 2 are relatively disjoint in terms of joins, and what we want to count, then a union query does seem reasonable to me...

Have a look at :
select userid, username, count(distinct client_id) as clientcount, serviceid,servicename
from 
(
        select u.ID as userid, u.Name as username, s.client_id, s.id as serviceid,s.name as servicename
        from users u
        inner join clients c on c.os = u.id
        left join (select s.id, s.name, i.services, i.client_id from services s cross join intake i where find_in_set(s.id,I.services) > 0) s on s.client_id = c.id
   union all
        select u.ID as userid, u.Name as username, p.client_id, s.id as serviceid,s.name as servicename
        from users u
        inner join clients c on c.os = u.id
        inner join progress p on p.client_id = c.id
        inner join progress_details d on d.progress_id = p.id
        inner join services s on s.id = d.service_id
) d
group by userid, username, serviceid, servicename        
order by userid, username, serviceid, servicename
;

Open in new window

Should be close to the final report....
0
 
Jan LouwerensSoftware EngineerCommented:
The fact that your intake table has a comma separate list for the services is going to be a problem. This is not relational. If there were a separate row for each service, it could be done via a query. But since you're going to have to parse that comma separated list into separate service ids, that processing may have to be done in PHP.
0
 
ste5anSenior DeveloperCommented:
What is the progress table for? It is redundant.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
APD TorontoAuthor Commented:
Ok, in that case dont worry about the intake table. How would I do this only for the progress_details?

As for the progress table, like I mentioned I am only showing fields related to the question. The progress table actually has additional fields such as where the progress took place, date, by who, and general comments.

At each progress (ie. meeting) multiple of services are addressed. Maybe 1 maybe 10.
0
 
ste5anSenior DeveloperCommented:
Well, after a further look at your tables:

clients served by a given user by service.

This is not possible. Cause there is no relationship of users to any of your other tables.
0
 
APD TorontoAuthor Commented:
Cause there is no relationship of users to any of your other tables.

The relationship for the users is my clients.os - sorry I forgot to elaborate on that, os is the user id.

So, I want the service_id summary, which is linked progress_details.progress_id = progress.id, then progress.client_id = clients.id, and finally clients.os = users.id
0
 
PatHartmanCommented:
1. Naming all the primary keys "ID" makes it extremely difficult for the rest of us to figure out how the kneebone is connected to the thighbone.  Do yourself and anyone who ever has to look at this schema a favor and use an intelligent naming standard so that you can actually match FK's to PK's or post a real diagram with relationships.
2. Intake needs to be two tables just like progress.
3. As the tables are now, Progress isn't necesary, you would simply link progress_details to clients.  I have created applications for this purpose so I'm guessing that you over simplified the model and you actually do need progress because there are data fields it needs that aren't shown in the picture.
4. To create the query, you would join users to clients to progress to progress_details to services but you would only select the fields you want in your final output and summarize the rest away.
Select  UserID, UserName, ServiceID, ServiceName, Count(*)  as NumClients
From ...
GroupBy UserID, UserName, ServiceID, ServiceName

I didn't even attempt the join because I don't know what variant of SQL you are using.
0
 
Mark WillsTopic AdvisorCommented:
Well, I always enjoy a challenge and suggest (a starting point for discussion)
-- part 1 - initial selection for prograss_details
select u.ID, u.Name, u.type, c.id,c.name,c.os, p.id, d.progress_id, d.service_id, s.id,s.name
from users u
inner join clients c on c.os = u.id
inner join progress p on p.client_id = c.id
inner join progress_details d on d.progress_id = p.id
inner join services s on s.id = d.service_id
order by 1,2,3,4,5,6,7,8,9,10


-- part 2 - initial selection for intake
select u.ID, u.Name, u.type, c.id,c.name,c.os, i.id, i.services, s.id,s.name
from users u
inner join clients c on c.os = u.id
inner join intake i on c.id = i.client_id
cross apply (select s.id, s.name from services s where charindex(format(s.id,'\,###\,'), ','+i.services+',')>0)s 
order by 1,2,3,4,5,6,7,8,9,10

Open in new window

If you could get back to me, we can then work on the counters and the final report.

Oh, and I use format() which is available from sql2012 or more recent, so let us know what version of SQL Server you are running...
0
 
APD TorontoAuthor Commented:
I am using MySQL and I will start build my query now.
0
 
APD TorontoAuthor Commented:
Mark, what is order by 1,2,3,...?
0
 
APD TorontoAuthor Commented:
so far I came up with the below, but because Rose had 2 entries for Senior Support in the progress_details table Nataliia is getting a count of 2 for Rose where it should be only 1.

SELECT services.*, det.progress_id, prog.client_id, users.first_name, users.last_name 

, COUNT(services.id) AS service_count
                    FROM services 
                    	INNER JOIN progress_details AS det ON det.service_id = services.id
                        INNER JOIN progress AS prog ON prog.id = det.progress_id
                        INNER JOIN clients ON clients.id = prog.client_id
                        INNER JOIN users ON users.id = clients.specialist_id
                        
GROUP BY clients.id, prog.id

Open in new window

0
 
APD TorontoAuthor Commented:
Some how it should count distinct clients.id
0
 
Mark WillsTopic AdvisorCommented:
"Order by" is just a sort sequence. and thought you were using SQL Server - sorry about that.

For count, you can :
count(distinct clients.id) as ClientCount

Open in new window

0
 
APD TorontoAuthor Commented:
OK... I got the following to work for Part 1:
SELECT services.id AS service_id, services.name AS service_name, users.first_name, users.last_name 

, COUNT(DISTINCT clients.id) AS service_count
                    FROM services 
                    	INNER JOIN progress_details AS det ON det.service_id = services.id
                        INNER JOIN progress AS prog ON prog.id = det.progress_id
                        INNER JOIN clients ON clients.id = prog.client_id
                        INNER JOIN users ON users.id = clients.specialist_id
                        
GROUP BY users.id, services.id

Open in new window


As for Part 2, any ideas or should I do that part with PHP?
0
 
Mark WillsTopic AdvisorCommented:
well the only tricky part about part 2 is the cross apply (per sql server)

From memory, you would need to do something like :

select s.id, s.name from services s cross join intake i where find_in_set(s.id,I.services) > 0

OR

select s.id, s.name from services s cross join intake i where LOCATE(CONCAT(',', s.id,','),CONCAT(',',i.services,',')) > 0

I will add MySQL to your topics to see if we can get a few MySQL experts involved
0
 
Mark WillsTopic AdvisorCommented:
Here you go, think this will answer Part 2
select u.ID, u.Name, u.type, c.id,c.name,c.os, i.id, i.services, s.id,s.name
from users u
inner join clients c on c.os = u.id
inner join intake i on c.id = i.client_id
left join (select s.id, s.name, i.services, i.client_id from services s cross join intake i where find_in_set(s.id,I.services) > 0) s on s.client_id = c.id;

Open in new window

Though, I think the locate would be faster - will await your feedback.
0
 
APD TorontoAuthor Commented:
I just did another "sub question"

What if I do a UNION SELECT with general GROUP BY and ORDER BY
0
 
PatHartmanCommented:
Why don't you try the query the way I suggested?  You don't need subselects but you DO need to group by in order to get the counts you need.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.