I have the following query in MySQL:

set 
  @userid = '97c73a4d-11b2-4492-XXXX-XXXXXXXXXXXX';
set 
  @lastdate = '2024-08-31';
SET 
  @sql = NULL;
SELECT 
  GROUP_CONCAT(
    DISTINCT CONCAT(
      'Count(CASE WHEN Checkedby = ''', 
      IFNULL(Checkedby, 0), 
      ''' THEN IFNULL(Checkedby,0) END) AS ''', 
      CONCAT(FirstName, ' ', LastName), 
      ''''
    )
  ) INTO @sql 
FROM 
  Bookings B 
  JOIN USERINFO UI ON B.CheckedBy = UI.ID 
  left join tours T on T.TourID = B.TourID 
  left join Providers P on P.ProviderID = T.ProviderID;
SET 
  @sql = CONCAT (
    'SELECT  Y.*,X.*
 FROM (SELECT ifnull(F.DAY,0) as DAY, IFNULL(Count(B.Checkedby),0) as Total, ', 
    @sql, ' FROM ORDERS O inner join BOOKINGS B on O.ORDERID = B.OrderID 
    left JOIN 
  (SELECT day(ifnull(N.Checkin,0)) as DAY, Count(N.Checkin) as Total FROM 
  Bookings N group by day(ifnull(N.Checkin,0))) F on F.DAY = day(B.Checkin)
 WHERE year(B.Checkin)*1 = year(@lastdate) and month(B.Checkin)*1 = month(@lastdate)  and 
 (B.UserID = @userid) 
 GROUP BY F.DAY) X RIGHT JOIN (select day(last_day(@lastdate) - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY) as Day , DAYNAME(last_day(@lastdate) - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY) as DayName
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
  where year(last_day('2024-12-31') - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY)=year(@lastdate) and month(last_day(@lastdate) - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY)=month(@lastdate)
    order by Day) Y ON X.DAY=Y.DAY order by Y.DAY'
  );
PREPARE stmt 
FROM 
  @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

When there is data, it returns the following:
contains data image

When there isn’t data, it returns:
no data image

What I would like to accomplish is:

1. remove second “DAY” column from the query
2. convert all nulls to 0

I tried:

1. to remove X or Y
2. to add ISNULL(column, 0)

This is not working.