1

I have a problem generating a monthly attendance report in each class of students. I have this table that stores all the attendance of student.

enter image description here

I was able to get the desired output I want using PIVOT with this query:

SELECT DISTINCT
tbl_subjectattendance.student_id,
COALESCE((
    CASE WHEN tbl_subjectattendance.Date = "2017-08-30" THEN tbl_subjectattendance.status END), "-") AS "2017-08-30", COALESCE(( CASE WHEN tbl_subjectattendance.Date = "2017-08-31" THEN tbl_subjectattendance.status END), "-") AS "2017-08-31" FROM tbl_subjectattendance WHERE `section_id` = "ST. AUGUSTINE"

enter image description here

But I also want to make the dates dynamic. A date range to be exact, but how do I do it? What should I add on my query to make the dates dynamic? I badly need your help.

Community
  • 1
  • 1
khrisdaniels
  • 79
  • 10
  • Basically, you'll have to add all other dates in the same way. In php, write a loop and add a column for each date. In MySQL, use dynamic sql, see e.g. [here](https://stackoverflow.com/a/12005676/6248528). The first query (with `INTO @sql`) will query all dates you want (e.g. `select distinct date from tbl_subjectattendance order by date`, or something if you want to have a column for dates even if noone attended, use something like [here](https://stackoverflow.com/a/3538926/6248528) (point 1 to 3)). Or consider not pivoting, but e.g. a `group by` and/or pivoting when you show it in e.g. html. – Solarflare Sep 10 '17 at 07:13
  • Seriously consider handling issues of data display in application code – Strawberry Sep 10 '17 at 07:24
  • @Strawberry my application code is based on a PHP. Do you have any suggestions how I would do it? – khrisdaniels Sep 10 '17 at 09:17
  • There are thousands of examples out there, but, if you're still struggling, see See [Why should I provide an MCVE for what seems to me to be a very simple SQL query](http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) – Strawberry Sep 10 '17 at 09:29
  • @Solarflare I added a CALENDAR table in my db in which contains all dates from 2017-2020 with a format of YMD, how can I combine these two in order to come up with the output I want? [Stored Procedure](https://stackoverflow.com/questions/27921637/display-monthly-attendance-report-with-dynamic-dates-in-mysql-stored-procedure) I was reading this solution but I cannot seem to get it to work on mine. – khrisdaniels Sep 10 '17 at 09:35
  • Since you are using php, you do not need this way. As I mentioned earlier, you can just write a loop and create the code on the fly. You need the calender table (or something similar) if you write dynamic sql. This can be done as shown in your link. If you cannot get it to work, show that code and some sample data, as strawberry mentioned. The alternative is to not do the pivot in sql, but retrieve the rows ordered/grouped by date and user, and display it in your app/html that way (it will depend on your php code/how you are using it, e.g. if you are using a framework, display html, ...) – Solarflare Sep 10 '17 at 10:13
  • @Strawberry I tried it on [SQL Fiddle](http://sqlfiddle.com/#!9/a45af/2). I didn't understand it and cant see the result. – khrisdaniels Sep 10 '17 at 11:01

0 Answers0