Results 1 to 11 of 11
  1. #1
    Join Date
    May 2009
    Posts
    6

    Exclamation Unanswered: tricky duplicates

    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!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by feednovascotia
    Okay I'm stumped over this requirement.
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2009
    Posts
    6
    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

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by feednovascotia
    Here is my original query ...
    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)

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    May 2009
    Posts
    6
    Yes this would be a monthly report. The visit would come from the fulfillDate field in cr_item.

  7. #7
    Join Date
    May 2009
    Posts
    6
    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...

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by feednovascotia
    The visit would come from the fulfillDate field in cr_item.
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    May 2009
    Posts
    6
    why do you need this?

  10. #10
    Join Date
    May 2009
    Posts
    6
    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

  11. #11
    Join Date
    Aug 2005
    Posts
    30
    Try this query:

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •