Reputation: 1
I have the following query in MySQL 8.0:
set
@userid = 'XXX';
set
@lastdate = '2024-09-30';
SET
@sql = null;
SELECT
GROUP_CONCAT(
DISTINCT CONCAT(
'Count(CASE WHEN Checkedby = ''',
Checkedby,
''' THEN Checkedby ELSE 0 END) AS ''',
CONCAT(
IFNULL(FirstName, ''),
' ',
IFNULL(LastName, '')
),
''''
)
) into @sql
FROM
aspnetusers A
join userinfo U on A.Id = U.id
left join bookings B ON A.Id = B.CheckedBy
where
(
ParentId = @userid
OR A.Id = @userid
)
and year(Checkin)= year(@lastdate)
and B.UserID = @userid
and month(Checkin)= month(@lastdate);
SET
@sql = CONCAT (
'SELECT Y.*, R.*
FROM
(SELECT day(Checkin) as DayX,
IFNULL(count(IFNULL(Checkin,0)),0) as Total, ',
@sql, '
FROM aspnetusers A join userinfo U on A.Id=U.id left join bookings B ON A.Id=B.CheckedBy
where year(Checkin)= year(@lastdate) and B.UserID= @userid and month(Checkin)= month(@lastdate)
group by day(Checkin)
) R right JOIN
(select day(last_day(@lastdate) - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY) as Day
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(@lastdate) - 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 R.DayX = Y.Day
GROUP BY Y.Day, R.Total order by Y.Day'
);
PREPARE stmt
FROM
@sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
the result is the following: result wo error
if I change the date, I get the following error:
11:29:17 PREPARE stmt FROM @sql Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1 0.000 sec
It seems that when the first query becomes null, I get the error, but I don't know how to correct it.
If someone has a simpler method, it would be great.
PS. I will use this query in a chart and would be also nice to show all users and their values (if its null then 0). I wrote the above script in MySQL, but in the feature I think I will convert all the queries to MariaDB.
Thanks,
Steve
I tried IFNULL and COALESCE but don't seem to work.
EDIT:
WORKING: set @lastdate = '2024-09-30';
-- 0 @sql value
Count(CASE WHEN Checkedby = '0bb63911-cc7b-4db0-bd33-b30edbc27a27' THEN Checkedby ELSE 0 END) AS 'Meggie1 Simpson',Count(CASE WHEN Checkedby = '97c73a4d-11b2-4492-a234-90b4cbff86c5' THEN Checkedby ELSE 0 END) AS 'MOLNAR ISTVAN'
-- 1 after exec
SELECT Y.*, R.*
FROM
(SELECT day(Checkin) as DayX,
IFNULL(count(IFNULL(Checkin,0)),0) as Total, Count(CASE WHEN Checkedby = '0bb63911-cc7b-4db0-bd33-b30edbc27a27' THEN Checkedby ELSE 0 END) AS 'Meggie1 Simpson',Count(CASE WHEN Checkedby = '97c73a4d-11b2-4492-a234-90b4cbff86c5' THEN Checkedby ELSE 0 END) AS 'MOLNAR ISTVAN'
FROM aspnetusers A join userinfo U on A.Id=U.id left join bookings B ON A.Id=B.CheckedBy
where year(Checkin)= year(@lastdate) and B.UserID= @userid and month(Checkin)= month(@lastdate)
group by day(Checkin)
) R right JOIN
(select day(last_day(@lastdate) - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY) as Day
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(@lastdate) - 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 R.DayX = Y.Day
GROUP BY Y.Day, R.Total order by Y.Day
-- 2 table name:
GROUP_CONCAT(
DISTINCT CONCAT(
'Count(CASE WHEN Checkedby = ''',
Checkedby,
''' THEN Checkedby ELSE 0 END) AS ''',
CONCAT(
IFNULL(FirstName, ''),
' ',
IFNULL(LastName, '')
),
''''
):
-- 3 result
Count(CASE WHEN Checkedby = '0bb63911-cc7b-4db0-bd33-b30edbc27a27' THEN Checkedby ELSE 0 END) AS 'Meggie1 Simpson',Count(CASE WHEN Checkedby = '97c73a4d-11b2-4492-a234-90b4cbff86c5' THEN Checkedby ELSE 0 END) AS 'MOLNAR ISTVAN'
NOT WORKING: set @lastdate = '2024-10-31';
-- 2 table name:
GROUP_CONCAT(
DISTINCT CONCAT(
'Count(CASE WHEN Checkedby = ''',
Checkedby,
''' THEN Checkedby ELSE 0 END) AS ''',
CONCAT(
IFNULL(FirstName, ''),
' ',
IFNULL(LastName, '')
),
''''
):
-- 3 result
Null
set @userid = '97c73a4d-11b2-4492-a234-90b4cbXXXX';
set @lastdate = '2024-09-30';//OK - 2024-10-31 //NOK
SET @sql=null;
SELECT
GROUP_CONCAT(
DISTINCT CONCAT(
'Count(CASE WHEN Checkedby = ''',
Checkedby,
''' THEN Checkedby END) AS ''',
CONCAT(IFNULL(FirstName,''), ' ', IFNULL(LastName,'')),
''''
)
) into @sql
FROM aspnetusers A left join userinfo U on A.Id=U.id left join bookings B ON A.Id=B.CheckedBy
where year(Checkin)= year(@lastdate) and month(Checkin)= month(@lastdate);
select @sql;
SET @sql = CONCAT ('SELECT Y.*, R.*
FROM
(SELECT day(Checkin) as DayX,
IFNULL(count(IFNULL(Checkin,0)),0) as Total, ',@sql,'
FROM aspnetusers A join userinfo U on A.Id=U.id left join bookings B ON A.Id=B.CheckedBy
where year(Checkin)= year(@lastdate) and month(Checkin)= month(@lastdate)
group by day(Checkin)
) R right JOIN
(select day(last_day(@lastdate) - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY) as Day
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(@lastdate) - 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 R.DayX = Y.Day
GROUP BY Y.Day, R.Total order by Y.Day');
select @sql;
PREPARE stmt FROM @sql;
select @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
On "2024-09-30" - Working: result 1 img
Result 1:
Count(CASE WHEN Checkedby = '0bb63911-cc7b-4db0-bd33-b30edbc27a07' THEN Checkedby END) AS 'Meggie1 Simpson',Count(CASE WHEN Checkedby = '97c73a4d-11b2-4492-a234-90b4cbff36c5' THEN Checkedby END) AS 'MOLNAR ISTVAN'
Result2:
SELECT Y.*, R.*
FROM
(SELECT day(Checkin) as DayX,
IFNULL(count(IFNULL(Checkin,0)),0) as Total, Count(CASE WHEN Checkedby = '0bb63911-cc7b-4db0-bd33-b30edbc27a07' THEN Checkedby END) AS 'Meggie1 Simpson',Count(CASE WHEN Checkedby = '97c73a4d-11b2-4492-a234-90b4cbff36c5' THEN Checkedby END) AS 'MOLNAR ISTVAN'
FROM aspnetusers A join userinfo U on A.Id=U.id left join bookings B ON A.Id=B.CheckedBy
where year(Checkin)= year(@lastdate) and month(Checkin)= month(@lastdate)
group by day(Checkin)
) R right JOIN
(select day(last_day(@lastdate) - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY) as Day
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(@lastdate) - 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 R.DayX = Y.Day
GROUP BY Y.Day, R.Total order by Y.Day
Result 3:
SELECT Y.*, R.*
FROM
(SELECT day(Checkin) as DayX,
IFNULL(count(IFNULL(Checkin,0)),0) as Total, Count(CASE WHEN Checkedby = '0bb63911-cc7b-4db0-bd33-b30edbc27a07' THEN Checkedby END) AS 'Meggie1 Simpson',Count(CASE WHEN Checkedby = '97c73a4d-11b2-4492-a234-90b4cbff36c5' THEN Checkedby END) AS 'MOLNAR ISTVAN'
FROM aspnetusers A join userinfo U on A.Id=U.id left join bookings B ON A.Id=B.CheckedBy
where year(Checkin)= year(@lastdate) and month(Checkin)= month(@lastdate)
group by day(Checkin)
) R right JOIN
(select day(last_day(@lastdate) - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY) as Day
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(@lastdate) - 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 R.DayX = Y.Day
GROUP BY Y.Day, R.Total order by Y.Day
Result 4: The working table (image above)
On "2024-10-31" - Not Working:
Result 1: NULL result1 img
Result2: NULL result2 img
Upvotes: 0
Views: 66