...
I can combine this with a element to cope when the user makes no selection.SELECT
| Code Block |
|---|
SELECT sum(nr_sales.QUANTITY) AS Y0, |
...
nr_sales.PAYMENT_METHOD AS X0 |
...
FROM retail.nr_sales nr_sales |
...
JOIN retail.nr_stores nr_stores ON nr_sales.STORE_ID = nr_stores.STORE_ID |
...
WHERE |
...
(FIND_IN_SET(nr_stores.STORE_MANAGER , REPLACE('{{Store Manager}}', '::::', ',')) OR '{{Store Manager}}' LIKE '%{Store Manager}}') |
...
GROUP BY |
...
nr_sales.PAYMENT_METHOD |
...
ORDER BY |
...
nr_sales.PAYMENT_METHOD |
In your db - you can find your syntax from the web / your favourite LLM
...
| Note |
|---|
Wow that’s complicated - Yes it is / are you still sure you want to use free hand SQL? Pi handles all of this very elegantly for you when you don’t use free hand SQL. FREE HAND SQL SHOULD BE A LAST RESORT |
Putting it all together.
So the SQL that does everything above looks like.
| Code Block |
|---|
SELECT
sum(nr_sales.QUANTITY) AS Y0,
nr_sales.PAYMENT_METHOD AS X0
FROM
retail.nr_sales nr_sales
JOIN retail.nr_stores nr_stores ON nr_sales.STORE_ID = nr_stores.STORE_ID
WHERE
(nr_stores.REGION = '{{Region}}' OR '{{Region}}' LIKE '%{Region}}')
AND
(FIND_IN_SET(nr_stores.STORE_MANAGER , REPLACE('{{Store Manager}}', '::::', ',')) OR '{{Store Manager}}' LIKE '%{Store Manager}}')
AND
(DATE_FORMAT(nr_sales.SALE_DATE, '%d %M %Y') BETWEEN '{{Sale Date[0]}}' AND '{{Sale Date[1]}}' OR '{{Sale Date[0]}}' LIKE '%{Sale Date[0]}}')
AND
(DATE_FORMAT(nr_sales.SALE_DATE, '%d %M %Y') < '[[TODAY]]')
GROUP BY
nr_sales.PAYMENT_METHOD
ORDER BY
nr_sales.PAYMENT_METHOD |