Example of SQL Code from Zoho Analytic’s Query
SELECT
u."User Name" as "Name",
COUNT(CASE
WHEN t.Status != 'Completed' THEN 1
END) as "# of tasks",
COUNT(CASE
WHEN t."Milestone Name" LIKE '%OKR%'
AND t.Status != 'Completed' THEN 1
END) as "# of OKR tasks",
COUNT(CASE
WHEN t.Due < today()
AND t.Status != 'Completed' THEN 1
END) as "# of pd tasks",
COUNT(CASE
WHEN t.Due < today()
AND t."Milestone Name" LIKE '%OKR%'
AND t.Status != 'Completed' THEN 1
END) as "# of pd OKR tasks",
COUNT(CASE
WHEN t.Due < subdate(today(), 7)
AND t."Milestone Name" LIKE '%OKR%'
AND t.Status != 'Completed' THEN 1
END) as "OKR tasks for 7+ days",
FORMAT(ROUND(
COUNT(CASE
WHEN t.Due < today()
AND t.Status = 'In Progress' THEN 1
END) / COUNT(CASE
WHEN t.Status = 'In Progress' THEN 1
END) * 100, 2), 'p') as "% of PD tasks/in progress tasks",
FORMAT(ROUND((COUNT(CASE
WHEN t.Due < today()
AND t.Status != 'Completed' THEN 1
END) / COUNT(CASE
WHEN t.Status != 'Completed' THEN 1
END)) * 100, 2), 'p') as "% of pd tasks/overall tasks"
FROM "Tasks" t
RIGHT JOIN "users" u ON u."User Name" in t."Owner"
WHERE u."Status" = 'active'
AND u."User Role" != 'Client'
AND u."User Name" != 'Urszula Urban'
GROUP BY "Name"