|
| [WB19] need help with SQL query |
| Iniciado por guest, 30,mar. 2016 17:50 - 3 respuestas |
| |
| | | |
|
| |
| Publicado el 30,marzo 2016 - 17:50 |
Hi everyone - I need to create a query, but not sure how to do it - I figure I nee several queries and them UNION them together. First I need a select all records in the database that have enrollments - that's easy. The next step is to look at the Attendance database and only keep the records from the first query that have some records in the attendance database. This way if there's no attendance records yet, I don't need to worry about absentees. Finally, out of the records that are left, I need to figure out who hasn't had an attendance record in the last "X" days. So someone that hasn't checked in in the last 10 days or so would be left in the list.
I probably can figure most of this out, but I really have no idea how to exclude records that have no attendance records at all.
Appreciate any help! |
| |
| |
| | | |
|
| | |
| |
| Publicado el 30,marzo 2016 - 18:16 |
Joel,
I assume you are using Mysql. If so, make an sql query -
xdate is user defined - your X days
select * from enrollments, Attendance where
enrollments.ID = Attendance.ID AND Attendance.lastattended < xdate
This should give you everybody on the enrollment list that has not attended since xdate .
Hope this helps,
Mike |
| |
| |
| | | |
|
| | |
| |
| Publicado el 30,marzo 2016 - 18:31 |
whitout knowing your db tables structure and database type (HF/CS, MYSQL,...) is dificult but you don't need several queries for this.
In your case you need at least one inner join between enrollments and Attendance. This is just one sample i don't know the structure of your Attendance table it can be a litle more dificult than this depending on your data structure.
select * from enrollments
inner join Attendance ON enrollments.ID = Attendance.ID
where Attendance.lastattended < xdate
check this help page about joins:
http://doc.windev.com/en-US/… |
| |
| |
| | | |
|
| | |
| |
| Publicado el 03,abril 2016 - 08:38 |
| Thanks everyone! Appreciate the input! |
| |
| |
| | | |
|
| | | | |
| | |
|