### Thread: Help with complicate query

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. what have you tried?
3. No Profile Picture
Contributing User
Devshed Intermediate (1500 - 1999 posts)

Join Date
Mar 2008
Posts
1,934
Rep Power
379
I'm thinking of a number. If I add another number to it I get 712. What number was I thinking of?
4. damn, that's easy

you were thinking of 937
5. 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
6. 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?
7. No Profile Picture
Contributing User
Devshed Newbie (0 - 499 posts)

Join Date
Jun 2012
Location
Wisconsin
Posts
78
Rep Power
3
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.
8. 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
9. No Profile Picture
Contributing User
Devshed Intermediate (1500 - 1999 posts)

Join Date
Mar 2008
Posts
1,934
Rep Power
379
Originally Posted by lebyheavy
I don't know what you mean
Precisely.
10. 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.
11. 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
12. No Profile Picture
Contributing User
Devshed Newbie (0 - 499 posts)

Join Date
Jun 2012
Location
Wisconsin
Posts
78
Rep Power
3
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
13. 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?

14. 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
15. No Profile Picture
Contributing User
Devshed Newbie (0 - 499 posts)

Join Date
Jun 2012
Location
Wisconsin
Posts
78
Rep Power
3
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'`