Okay I'm stumped over this requirement. I am looking for clients who visited more than one Organization per month. They are allowed to visit the same Organization multiple times


Health# Client Organization Date
000-000-001 Smith, John ABC 4/9/2009
000-000-001 Smith, John Acme 4/15/1009
000-111-123 Jones, Dave XYZ 4/1/2009
000-111-123 Jones, Dave XYZ 4/15/2009
000-222-456 Doe, Jane Northwind 4/7/2009
000-222-456 Doe, Jane Seabreeze 4/7/2009
000-222-456 Doe, Jane Northwind 4/21/2009
000-222-456 Doe, Jane Seabreeze 4/21/2009

I tried different queries and wondering what the best way to do this? Appreciate any help!

well, as long as you haven't left this until the night before the assignment is due to be handed in, you should be okay

here's a hint: using GROUP BY, write a query that counts the number of distinct organizations per client

if you get stuck, show what you tried and what happened when you tested it

Here is my original query but I am getting the issue as I stated above...

SELECT hoh.healthnumber AS 'Health #', CONCAT(hoh.lname, ', ', hoh.fname) AS 'Client', a.name AS 'Organization', i.fulfillDate AS 'Date'
FROM cr_item i
JOIN cr_family f on f.id=i.family
JOIN cr_client hoh on hoh.id=f.main
JOIN agency a ON a.id=i.agencyFill
WHERE i.fulfillDate BETWEEN '20090401000000' AND '20090431235959'
AND i.itemDesc='Emergency Hamper'
AND i.family IN
(SELECT family
FROM
(SELECT DISTINCT family, count(*) AS 'Visits'
FROM cr_item
WHERE fulfillDate BETWEEN '20090401000000' AND '20090431235959'
AND itemDesc='Emergency Hamper'
GROUP BY family
) AS FindDups
WHERE Visits > 1
)
ORDER BY Client, i.fulfillDate

Isn't that just the SQL that produced the data for your question?

I think Rudy was more interested in what attempts you'd made to actually solve your coursework.

now it looks a lot less like a homework assignment

would you please explain how your tables work? i see four tables -- cr_item, cr_family, cr_client, agency -- but i can't figure out how a "visit" is represented

so please tell us what each table is for and describe how they are related

your initial requirement was "clients who visited more than one Organization per month" but i notice you are filtering for a specific month, is that correct?

(i also notice that on your planet, there's an april 31st)

Yes this would be a monthly report. The visit would come from the fulfillDate field in cr_item.

Maybe I should break this down into 2 steps. The first is my initial query but do CREATE TABLE test as select...

Then look for clients who visited more than one Organization per month. They are allowed to visit the same Organization multiple times (which I'm stuck at). Currently I am eye balling the results...

would you please explain how your tables work? i see four tables -- cr_item, cr_family, cr_client, agency -- but i still can't figure out how a "visit" is represented

so please tell us what each table is for and describe how they are related

why do you need this?

I created a new table 'test' from my original sql statement I had above. Now I am doing the following but receive a 1064 error.

SELECT
healthnum
FROM
test
GROUP BY
healthnum
HAVING
COUNT (DISTINCT organization) > 1

Try this query:

SELECT healthnum, COUNT (DISTINCT organization) as org_count
FROM test GROUP BY healthnum HAVING org_count > 1