Mysql attendence report creates syntax error on prepare stmt

Question

I have two tables: One with just a list of dates called date_range (column name is Date) and one called wp_wpdatatable_1 in which all the date is stored (after each practice, a row for every player is created with the name of the player (player), date of practice (date), duration (sporttrainingduration), what practice group (practiceheldby) etc...

Now I want to create a report.

I want every day of the month across, the players names in the first column and on every day a player was at a practice I want to list which practice he attended (wp_wpdatatable_1.practiceheldby)

-- 1. Create an expression that builds the columns
set @sql = (
    select group_concat(distinct
        concat(
            "max(case when date_range.`date`='", date_range.`Date`, "' then `practiceheldby` end) as `", date_range.`Date`, "`"
        )
    )
    from wp_wpdatatable_1, date_range  
    where date_range.`Date`>=2019-06-01
              and date_range.`Date` <= 2019-06-07
);

-- 2. Complete the SQL instruction
set @sql = concat("select `player`, ", @sql , " from wp_wpdatatable_1 group by `player`");

-- 3. Create a prepared statement
PREPARE stmt from @sql;

-- 4. Execute the prepared statement
execute stmt;
DEALLOCATE PREPARE stmt;

I'm not a pro and I've played with this for 3 or four days now. I think I'm very close, but I get this error message:

PREPARE stmt from @sql MySQL meldet: Dokumentation

#1064 - Fehler in der SQL-Syntax. Bitte die korrekte Syntax im Handbuch nachschlagen bei 'NULL' in Zeile 1

What am I missing? thank you!

Answer 1

You're very close. Besides the missing quotes on dates, the case when date_range.date should be case when date in line 5 of your code.

-- 0. Toy data
CREATE TABLE date_range  (
  `Date` datetime
);
CREATE TABLE wp_wpdatatable_1  (
  `player` VARCHAR(5),
  `Date` DATE,
  `sporttrainingduration` FLOAT,
  `practiceheldby` VARCHAR(10)
);
INSERT INTO date_range 
VALUES
  ('2019-06-01'),
  ('2019-06-02'),
  ('2019-06-03')
  ;
INSERT INTO wp_wpdatatable_1 
VALUES
  ('AAA','2019-06-01','1','group1'),
  ('AAA','2019-06-02','2','group2'),
  ('AAA','2019-06-03','3','group3'),
  ('AAA','2019-06-04','1','gorup1'),
  ('BBB','2019-06-02','2','group2'), 
  ('CCC','2019-06-03','3','group3')
  ;
select * from date_range;
select * from wp_wpdatatable_1;

date_range
===================
Date
===================
2019-06-01 00:00:00
2019-06-02 00:00:00
2019-06-03 00:00:00

wp_wpdatatable_1
=======================================================
player  Date    sporttrainingduration   practiceheldby
=======================================================
AAA 2019-06-01  1   group1
AAA 2019-06-02  2   group2
AAA 2019-06-03  3   group3
AAA 2019-06-04  1   gorup1
BBB 2019-06-02  2   group2
CCC 2019-06-03  3   group3

The updated code below:

-- 1. Create an expression that builds the columns
set @sql = (
    select group_concat(distinct
        concat(
            -- "max(case when date_range.`date`='", date_range.`Date`, "' then `practiceheldby` end) as `", date_range.`Date`, "`"
            "max(case when `date`='", date_range.`Date`, "' then `practiceheldby` end) as `", date_range.`Date`, "`"
        )
    )
    from wp_wpdatatable_1, date_range  
    -- where date_range.`Date`>=2019-06-01
              -- and date_range.`Date` <= 2019-06-07
    where date_range.`Date`>='2019-06-01'
              and date_range.`Date` <= '2019-06-07'
);

Output:

===========================================================================
player  2019-06-01 00:00:00     2019-06-02 00:00:00     2019-06-03 00:00:00
===========================================================================
AAA group1  group2  group3
BBB null    group2  null
CCC null    null    group3

Code here