I’m working on a downtime reporting function that runs a query when the downtime event is over. It currently logs the time of the downtime event (datetime), the machine it occurred on (int) , the reason code (real), the duration (real) and the crew (char).
Script from the tag value changed script looks like this:
My end results are working fine - I’m able to create a bar chart in Perspective, but I’m having trouble in the reporting system “transforming” the downtime reason code to the text label it represents, eg 20 = “Broken Press”. I’ve created a second table with a reason code (real) and reason code label (nvarchar(50)). What I’d like to do is when I create the entry into the downtime table, I’d like to include the label text into downtime table. I have added a column in the DownTime table called LabelText (nvarchar(50)). It seems like I want to use some sort of JOIN function, but I’m not sure how to do that with the INSERT INTO VALUES function..
When my Data Source for the report looks like this:
SELECT Downtime.ReasonCode, SUM(Downtime.Duration)
FROM DownTime
WHERE Downtime.t_stamp BETWEEN ? AND ?
GROUP BY "ReasonCode"
ORDER BY "ReasonCode" ASC
Heading toward the right answer - each ‘category’ of down time lists the duration of downtime associated with it. Now I want to replace the numbers on the X-axis with their corresponding category labels. but I can’t seem to structure my JOIN correctly. My best guess is:
SELECT DownTimeLabels.LabelDescription, SUM(DownTime.Duration)
FROM DownTime
WHERE DownTime.t_stamp BETWEEN '2025-09-08' AND '2025-09-09'
GROUP BY DownTime.ReasonCode
ORDER BY DownTime.ReasonCode ASC
INNER JOIN DownTimeLabels ON DownTime.ReasonCode=DownTimeLabels.NumericID
Where the table DownTimeLabels contains two columns NumericID (which should correspond to the column ReasonCode from the table DownTime) and LabelDescription (which contains the nvarchar(50) reason label) (I replaced the ? Parameters with actual values to use in the Database Query Browser). This gives me an incorrect syntax error near INNER.
This clearly tells you that the SQL syntax is incorrect, so you should Google for the syntax needed in your DB flavor. However, I think the inner join always goes after the FROM clause.
SELECT
DownTimeLabels.LabelDescription, SUM(DownTime.Duration)
FROM
DownTime
INNER JOIN
DownTimeLabels ON DownTime.ReasonCode=DownTimeLabels.NumericID
WHERE
DownTime.t_stamp BETWEEN '2025-09-08' AND '2025-09-09'
GROUP BY
DownTime.ReasonCode
ORDER BY
DownTime.ReasonCode ASC
That was it. JOIN should have been earlier. Also I can’t group by ReasonCode because I didn’t include it in my select. This was the final thing that worked:
SELECT DownTimeLabels.LabelDescription, SUM(DownTime.Duration/60)
FROM DownTime
JOIN DownTimeLabels ON DownTime.ReasonCode=DownTimeLabels.NumericID
WHERE DownTime.t_stamp BETWEEN ? AND ?
GROUP BY DownTimeLabels.LabelDescription
ORDER BY SUM(DownTime.Duration/60) ASC
Is it actually a REAL? That's almost certainly not what you want. Just because a reason code happens to be something like digits.digits, doesn't mean you should represent it with REAL. If you do, then how do you distinguish between 123.1 and 123.10 and 123.100?
Fair point. The idea was that each major category (20, 30, etc.) was a different functional area of the process and then I could add subcategories (20.1, 20.2, 20.3). By making it a real I thought it would be easier in a query to sum up all of the subcategories (WHERE ReasonCode BETWEEN 20 AND 30, for example). I’ll have to keep in mind the 123.1 / 123.10 “Gottcha”. Maybe make the first level of decimal subcategories 20,00001, 20.00002, etc.?
No, decimals stored in real (floating point) columns are not perfect decimals and you will not be able to use "equals" comparisons reliably. Don't try to create hierarchies with decimals.
I actually just bumped into that. My downtime code for 60.1, shows up as 60.09999847….. in my bar chart. I could format but that may be a show-stopper for that approach. I’ve done this different ways in past lives but never found an approach that didn’t have some limitation - such is life, Thanks for all of the suggestions - richardNZ15’s suggestion with INTs might work for me.
