#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    5
    Rep Power
    0

    Help with complicate query


    Hi you all.

    I have a table (horarios) with columns IDPaciente,Date,Time,Asistencia.

    What I'm trying to do is to know which patient IDs are in each day and based on this value, know how many days per week a patient assists to the clinic. In the query, I specify the beginning and ending of a week date values to simplify it.

    So the result for the week 2012-06-11 / 2012-06-17 should be:

    Number of days Number of Patients
    1 5
    2 7
    3 2
    5 1

    How can I do this? Any suggestions?

    Thanks,
  2. #2
  3. Hockey face
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    St. Catharines, Canada
    Posts
    8,144
    Rep Power
    1316
    what have you tried?
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,927
    Rep Power
    378
    I'm thinking of a number. If I add another number to it I get 712. What number was I thinking of?
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    damn, that's easy

    you were thinking of 937
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    5
    Rep Power
    0
    I don't know what you mean
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    5
    Rep Power
    0
    What I have tried is as follows:

    SELECT COUNT(DISTINCT DAY(horarios.Fecha)) AS NumDays,COUNT(DISTINCT horarios.IDPaciente) AS NumPac FROM horarios,pacientes WHERE horarios.Fecha>'2012-06-11' AND horarios.Fecha<'2012-06-17' AND horarios.IDPaciente=pacientes.ID GROUP BY Fecha;

    The result is:

    NumDays NumPac
    1 1
    1 1
    1 8
    1 2

    But this is incorrect. It is listing 1 in the left column for each day and in the right column the total number of patients served in the corresponding day.

    I think I have to subquery in the WHERE clause. Any idea?
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Location
    Wisconsin
    Posts
    78
    Rep Power
    2
    I would like to help you but I just can't get my head wrapped around your table structure. I also am not too sure what the end goal is.

    From what I understand you are trying to get a list of days containing-
    Patients with visits scheduled for each day and number of visits each patient will have in given time period. Correct?

    also {horarios.IDPaciente=pacientes.ID} is this suppost to be a join?

    The more I look at this the less it makes sense.
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by lebyheavy
    It is listing 1 in the left column for each day ...
    of course

    this is because you have

    SELECT COUNT(DISTINCT DAY(horarios.Fecha)) ... GROUP BY Fecha;

    that GROUP BY will give you 1 row for each distinct fecha

    and of course the number of distinct fechas for each fecha is exactly 1
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,927
    Rep Power
    378
    Originally Posted by lebyheavy
    I don't know what you mean
    Precisely.
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    5
    Rep Power
    0
    Originally Posted by deljr
    I would like to help you but I just can't get my head wrapped around your table structure. I also am not too sure what the end goal is.

    From what I understand you are trying to get a list of days containing-
    Patients with visits scheduled for each day and number of visits each patient will have in given time period. Correct?

    also {horarios.IDPaciente=pacientes.ID} is this suppost to be a join?

    The more I look at this the less it makes sense.

    Hi deljr.

    Let's begin telling you that pacientes is the table where patients are detailed so I can search in horarios.IDPaciente by pacientes.ID. Is that now clear?

    What I am trying to do is to list the amount of days a patient assists to the clinic in the giving time period. So if patient A assists monday,wednesday and friday, the number of days should be 3 and adds 1 to the number of patients column. I don't know if I'm explaining it clearly.

    I hope this can clarify you.
  20. #11
  21. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by lebyheavy
    So if patient A assists monday,wednesday and friday, the number of days should be 3
    so clearly, to get a COUNT() of 3, you can't have a GROUP BY for each visit time
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Location
    Wisconsin
    Posts
    78
    Rep Power
    2
    This might be better to do in 2 querys also for the table structure is each visit a new line? Maby some sample data from the database can clear things up.

    Im not sure if this will help
    Code:
    SELECT horarios.IDPaciente AS IDPac, COUNT(DISTINCT horarios.IDPaciente) AS NumPac FROM horarios WHERE horarios.Fecha>'2012-06-11' AND horarios.Fecha<'2012-06-17' GROUP BY horarios.IDPaciente
    by the way im taking a stab in the dark at that solution
  24. #13
  25. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    5
    Rep Power
    0
    Originally Posted by deljr
    This might be better to do in 2 querys also for the table structure is each visit a new line? Maby some sample data from the database can clear things up.

    Im not sure if this will help
    Code:
    SELECT horarios.IDPaciente AS IDPac, COUNT(DISTINCT horarios.IDPaciente) AS NumPac FROM horarios WHERE horarios.Fecha>'2012-06-11' AND horarios.Fecha<'2012-06-17' GROUP BY horarios.IDPaciente
    by the way im taking a stab in the dark at that solution
    Hi again deljr. Sorry, I've been out for several days.

    This is what I reach for the moment. If I use the following query

    SELECT COUNT(DISTINCT DAY(horarios.Fecha)) AS NumDays,COUNT(DISTINCT horarios.IDPaciente) AS NumPac FROM horarios,pacientes WHERE horarios.Fecha>'2012-06-11' AND horarios.Fecha<'2012-06-17' AND horarios.IDPaciente=pacientes.ID GROUP BY Fecha;

    the result

    | NumDays | NumPac |
    +---------+--------+
    | 1 | 1 |
    | 1 | 1 |
    | 1 | 8 |
    | 1 | 2 |

    This means for each day, the number of citations. This is not what I want.

    The data content in the DDBB is...

    | IDPaciente | Fecha | Hora |
    +------------+------------+----------+
    | 9 | 2012-06-13 | 12:00:00 |
    | 6 | 2012-06-14 | 11:00:00 |
    | 13 | 2012-06-15 | 16:00:00 |
    | 3 | 2012-06-15 | 12:00:00 |
    | 19 | 2012-06-15 | 08:00:00 |
    | 6 | 2012-06-15 | 08:00:00 |
    | 6 | 2012-06-15 | 18:00:00 |
    | 12 | 2012-06-15 | 13:00:00 |
    | 11 | 2012-06-15 | 11:00:00 |
    | 8 | 2012-06-15 | 10:00:00 |
    | 7 | 2012-06-15 | 09:00:00 |
    | 19 | 2012-06-16 | 12:00:00 |
    | 6 | 2012-06-16 | 12:00:00 |


    Any new idea?

    Thanks for your help
  26. #14
  27. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by lebyheavy
    SELECT COUNT(DISTINCT DAY(horarios.Fecha)) AS NumDays,...
    GROUP BY Fecha;

    Any new idea?
    nope, just an old one -- if you want one row per fecha, then NumDays as defined is always going to equal 1

    if you want something other than 1, change your GROUP BY
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  28. #15
  29. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Location
    Wisconsin
    Posts
    78
    Rep Power
    2
    for number of patients a day
    Code:
    SELECT COUNT(horarios.Fecha), horarios.Fecha FROM horarios WHERE horarios.Fecha>'2012-06-11' AND horarios.Fecha<'2012-06-1 GROUP BY Fecha
    if you try it. it should work

    as for the number of visits in a time period
    Code:
    SELECT COUNT(horarios.IDPaciente), horarios.IDPaciente FROM horarios WHERE horarios.Fecha>'2012-06-11' AND horarios.Fecha<'2012-06-17'

IMN logo majestic logo threadwatch logo seochat tools logo