Further to a personal project outlined in this previous question, I am looking to take a percentage value returned from a Firebird query and output an ASCII style progress bar, using characters such as ▓.
The code I have at the moment is shown below:
SELECT FIRST 1
(
SELECT
SUM("Day") +
SUM("Night")
FROM
"Duty"
WHERE
"Date" > DATEADD (DAY, -28, CURRENT_TIMESTAMP)
) AS "Hours Last 28 Days",
(
SELECT
SUM("Day") +
SUM("Night")
FROM
"Duty"
WHERE
"Date" > DATEADD (DAY, -365, CURRENT_TIMESTAMP)
) AS "Hours Last 365 Days"
FROM
"Duty"
I'm using Libreoffice Base as a frontend, and putting together a form to display a large amount of information, some of which I'd like some graphical representations of. Ideally, I'd like to avoid using macros and code this purely through the query, so I was thinking I'd take the two values returned from this query, divide it by the maximum legal hours, and have an output of one ▓ character per percent (rounded to nearest whole number).
So if the limit for Hours Last 365 Days was 1000, then I'd need to select "Hours Last 365 Days" / 1000 * 100 AS "365 Day Percentage", rounded to the nearest whole number, then run the code which displays this as the progress bar.
At the moment, the only method I know of is to use 100 nested IIF statements. I'm guessing I don't need to explain why that would be an issue. Is there a more elegant way of achieving the same outcome?
You can use RPAD (or LPAD) for this. For example, for 20%:
select rpad('', 20, '▓') from rdb$database
Fiddle: https://dbfiddle.uk/_wOyUU-G
Depending on the exact form of query, you may need to wrap it in an explicit cast to ensure Firebird knows the eventual length of the string value (e.g. cast(rpad('', percent, '▓') as varchar(100))).
As an aside, I'd advise to read the Firebird 3.0 Language Reference, chapter Built-in Scalar Functions if you're looking for functions. It is probably more efficient than asking a question each time.