On Wed, Dec 11, 2019 at 5:53 PM Kerrie Stevens <kerrie.stev...@ac.edu.au>
wrote:
> We have version 18.11.11 and I’ve looked through the reports library but
> can’t see anything that looks similar to be tweaked into what I need.
>
Try this, for year 2019
SELECT month, day, suc+uns AS total, suc AS succesful, uns AS unsuccesful
FROM (
SELECT
CASE WHEN A.m1 IS NULL THEN A.m2 ELSE A.m1 END AS month,
CASE WHEN A.d1 IS NULL THEN A.d2 ELSE A.d1 END AS day,
CASE WHEN A.suc IS NULL THEN 0 ELSE A.suc END AS suc,
CASE WHEN A.uns IS NULL THEN 0 ELSE A.uns END AS uns
FROM (
SELECT * FROM
(
SELECT month(time) AS m1, day(time) AS d1, count(*)
AS suc
FROM search_history
WHERE total > 0 AND year(time) = '2019'
GROUP BY m1,d1
) AS S
LEFT JOIN
(
SELECT month(time) AS m2, day(time) AS d2, count(*)
AS uns
FROM search_history
WHERE total = 0 AND year(time) = '2019'
GROUP BY m2,d2
) AS U
ON S.m1 = U.m2 and S.d1 = U.d2
UNION
SELECT * FROM
(
SELECT month(time) AS m1, day(time) AS d1, count(*)
AS suc
FROM search_history
WHERE total > 0 AND year(time) = '2019'
GROUP BY m1,d1
) AS SR
RIGHT JOIN
(
SELECT month(time) AS m2, day(time) AS d2, count(*)
AS uns
FROM search_history
WHERE total = 0 AND year(time) = '2019'
GROUP BY m2,d2
) AS UR
ON SR.m1 = UR.m2 and SR.d1 = UR.d2
) AS A
ORDER BY month,day
) AS B;
Hope that helps
Bernardo
_______________________________________________
Koha mailing list http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha
