Hi, I'm trying to translate the below SQL into a Caspio task...
INSERT INTO sl_tbl_btp_report (client, month, year, y_percent)
SELECT
c.full_name AS client,
YEAR(a.date) AS year,
MONTH(a.date) AS month,
(COUNT(CASE WHEN b.btp = 1 AND a.btp_score = 'Y' THEN 1 END) / COUNT(CASE WHEN b.btp = 1 THEN 1 END)) * 100 AS y_percent
FROM
sl_tbl_attendance_outcome_result a
INNER JOIN
sl_tbl_outcomes b ON a.outcome_fk = b.outcome_id
INNER JOIN
attendance att ON a.attendance_fk = att.attendance_id
INNER JOIN
sl_tbl_clients c ON att.client_fk = c.client_id
GROUP BY
c.full_name, YEAR(a.date), MONTH(a.date);
I'm sure it will just be translated back again which is frustrating. There is an entry in the ideas section to allow SQL to be written directly into the system which I think would save a lot of time so please vote on it.
In the mean time if anyone could help with this I would be grateful. I got as far as the diagram below but it seems I can't connect the calculation directly to the field...
You can post now and register later.
If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.
Question
BrianI
Hi, I'm trying to translate the below SQL into a Caspio task...
INSERT INTO sl_tbl_btp_report (client, month, year, y_percent)
SELECT
c.full_name AS client,
YEAR(a.date) AS year,
MONTH(a.date) AS month,
(COUNT(CASE WHEN b.btp = 1 AND a.btp_score = 'Y' THEN 1 END) / COUNT(CASE WHEN b.btp = 1 THEN 1 END)) * 100 AS y_percent
FROM
sl_tbl_attendance_outcome_result a
INNER JOIN
sl_tbl_outcomes b ON a.outcome_fk = b.outcome_id
INNER JOIN
attendance att ON a.attendance_fk = att.attendance_id
INNER JOIN
sl_tbl_clients c ON att.client_fk = c.client_id
GROUP BY
c.full_name, YEAR(a.date), MONTH(a.date);
I'm sure it will just be translated back again which is frustrating. There is an entry in the ideas section to allow SQL to be written directly into the system which I think would save a lot of time so please vote on it.
In the mean time if anyone could help with this I would be grateful. I got as far as the diagram below but it seems I can't connect the calculation directly to the field...
Thanks
Link to comment
Share on other sites
2 answers to this question
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.