Hi everyone Hoper everyone is doing ok. I currently assigned an SQL query to a table component in order to get data into the table from a database, I want to display the table in my dashboard but it currently duplicates the data I have and gives various results with slight changes. I only want to display results that have only the same nValue which corresponds to the status of a machine,
The following is the query I’m currently using:
SELECT TOP 10 nValue, nCount,
CONVERT(varchar, tsTotalDuration / 86400 ) + ‘:’ + – Days
CONVERT(varchar, DATEADD(ms, ( tsTotalDuration % 86400 ) * 1000, 0), 108) as “Duration(DD:HH:MM:SS)”,
nShiftNumber, nKey, tLastModified
FROM tblCPDataMessageProtocol
WHERE nShiftNumber = {Can/Filler/Messages/Current Shift Number} AND nObjectNameLink = 1698
ORDER BY nKey DESC, tLastModified DESC, nValue DESC;
This is what I’m getting as a result and I only want the last updated result from the database to be reflected on the table. Which would be the total duration of a machines Status/Program with its quantities (how much times that program/status was called on during the operating time of the machine. I understand group by would remove duplicates but as an error on my part these are not duplicates due to the fact that the rows are not the same. Is it possible to get the last updated record from the db with only one of each status or program values?
Your query won’t return the latest result, just the largest. And the generated row can have the largest count from one record and largest duration from a different record. Consider using sum instead of max to get the true values for the time period, or constructing a join using a latest t_stamp value (if you have one) to help you get the true latest count and duration.
Max did in fact just give me the largest value and not what I was expecting. Values were totally wrong. Tried using sum but did not work either. It jut added every record which gave me a total duration of about a month per shift. Tried checking the time stamp but I have no latest time stamp currently for the records in the db or dont understand how to use it in a query
Hoper everyone is doing ok. I currently assigned an SQL query to a table component in order to get data into the table from a database, I want to display the table in my dashboard but it currently duplicates the data I have and gives various results with slight changes. I only want to display results that have only the same nValue which corresponds to the status of a machine,
