Hello,
I need your help.
currently I have a problem with a SQL LEFT JOIN statement
I need it for a hour report. The statement should report all calendar days and all activities from the users.
Currently I got only the data entries where the user has activities and I miss the date entries for the weekend and the public holidays.
If I run the select statement (AAA) alone it shows me all calendar days correct
> SELECT
DATE(AAA.EventDate) as EventDate,
IFNULL(BBB.Akt_Mitarbeiter,0) as Akt_Mitarbeiter,
BBB.MA_Name,
DATE_FORMAT(IFNULL(BBB.Akt_Datum,AAA.EventDate),'%Y') as Akt_Datum_Jahr,
DATE_FORMAT(IFNULL(BBB.Akt_Datum,AAA.EventDate),'%m') as Akt_Datum_Monat,
DATE_FORMAT(IFNULL(BBB.Akt_Datum,AAA.EventDate),'%u') as Akt_Datum_Woche,
CASE DATE_FORMAT(IFNULL(BBB.Akt_Datum,AAA.EventDate),'%w')
WHEN 0 THEN 'So'
WHEN 1 THEN 'Mo'
WHEN 2 THEN 'Di'
WHEN 3 THEN 'Mi'
WHEN 4 THEN 'Do'
WHEN 5 THEN 'Fr'
WHEN 6 THEN 'Sa'
ELSE ' ' END as Akt_Datum_WT,
DATE(IFNULL(BBB.Akt_Datum,AAA.EventDate) ) as Akt_Datum
FROM
(
select * from
(select adddate('2021-12-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) EventDate from
(select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
(select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
(select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
(select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
(select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where EventDate between '2021-12-01' and '2023-12-31'
) AAA LEFT OUTER JOIN (SELECT
Akt_Datum,
Akt_FB,
Akt_Mitarbeiter,
MA_Name
FROM
qry_Akt
GROUP BY
MA_Name, Akt_Datum
ORDER BY
Akt_Datum
) BBB ON Date(AAA.EventDate) = Date(BBB.Akt_Datum)