SELECT F_SGRP, F_TEST, F_VAL
WHERE F_TEST IN (comma delimited list of ids) AND F_SGRP>%F_SGRP%
ORDER BY F_SGRP DESC
If anyone knows of a website that can offer ColdFusion help that isn't controlled by neurotic, pedantic jerks* (stackoverflow.com), please PM me with a link.
* The neurotic, pedantic jerks are not the owners; just the people who are in control of the "popularity contest".
Thanks for the responses! ... Sorry about the mix up guelphdad
I'm going to try wolfshades suggestion and play around some more today... I'll get back to you
Hi Old Pedant... The F_Test #'s are not unique. In this case, one of the F_TEST numbers represent "accumulated dowtime" for a specific processing line. Another F_TEST number may represent the "accumulated uptime" for that specific process. These are accumulated values which is why I need to retrieve the most recent.
What I don't understand is this:
My fault. I apologize for not disclosing all the details. ... So I am working with an application called InfinityQS. We are running a Data Management System Provider Manager (part of the InfinityQS package) on the server to collect automated data.
The DMS OLEDB Provider actually has a "Startup" command which produces a "Token" and a "value" ... (Again I don't fully understand what I'm doing but I've been somewhat successful.)
This is my "Startup"
SELECT TOP 1 F_SGRP
WHERE F_TEST=1342169960 ORDER BY F_SGRP ASC
So the %F_SGRP% is looking at the Startup. The "F_SGRP" increments with each record made. Startup is looking at the first F_SGRP (record) made with that F_TEST name... which is why the one in my "Processing" code has to find a one that is greater.
The reason for combining these queries is because I have only been able to do this for a single value in each DMS Provider. I am trying to avoid creating multiple DMS Providers as we've learned in the past that this can slow things down significantly and causes problems with our atuomated data collection.
Oh that F_VAL1 only appeared because I put "Select TOP 1 F_VAL, F_VAL"... The system just returned the value F_VAL from the first record it found twice and appended a "1" at the end of the second one to distinguish between variable names.
So... we are accumulating several different values through out the day each being recorded with a specific "F_TEST" tag ... The TOP 1 (last saved database value) is the only value with that "F_TEST" tag I care about.
At the end of each day from another system I am triggering an automated data collection. At this point I refer to "F_VAL" from one Provider and "F_VAL" from another Provider (each provider using a different "F_TEST" tag) and then perform a calculation.
In this case I am grabbing the total accumulated downtime (F_TEST=1342169960) on a production line for the day and dividing it by the total accumulated available time (F_TEST=1342169992) to determine the % Downtime for each day in order to trend
This worked successfully and I'm beinging to turn heads... Now my superiors would like me to expand this to every production Line. I will need more accumulated values which are being distinguished by different "F_TEST" tags. I am trying to avoid creating seperate providers for each "F_TEST" as I am sure it is unneccesary and it's already been suggested that we consolidate our providers as each one is checking in every second to see if the value has changed and this is extremely taxing on our systems resource
SELECT F_TEST, MAX(F_SGRP) AS MAXSGRP
WHERE F_TEST IN ( ...any length list of numbers ... )
GROUP BY F_TEST
ORDER BY F_TEST
Thank you for this
This gets me SO close... I will play some more to see if i can get the F_VAL associated with these records as well
I still don't understand what your "startup" is for.
I believe the only purpose of the start-up is to provide a variable for use with conditions in the processing code
And this InfinityQS isn't smart enough to give you a report such as you are after automatically? Makes me wonder how close to infinity it really is.
In Infinity's defense, my company is pushing the limits with downtime and all this automated data... We have the software "communicating" with the PLC that our processing machines are connected to, to create automated downtime event records. Infinity is most commonly used for Quality Control and Specification Compliance. Most users never even use the DMS App and Providers. The main InfinitySPC application excels at custom user/gauge input screens and generating charts based off the collected information. It would be nice if there was a pre-canned way to calculate the percent for us but they've been good about listening to customer (our) needs and releasing new features with software revisions, so I can't complain about them.
Using the last bit of code you supplied and some common sense I did some googling and discovered what I needed.
End Result looks like this:
SELECT T.F_SGRP, T.F_TEST, T.F_VAL
FROM SGRP_TST T
INNER JOIN (SELECT F_TEST, MAX(F_SGRP) AS MAXSGRP
WHERE F_TEST IN (1342169960, 1342169992)
GROUP BY F_TEST) Q
ON T.F_TEST = Q.F_TEST
AND T.F_SGRP = Q.MAXSGRP
Next step is to add more F_TEST to the query once I create them.
Big Ups to OLDPEDANT for sticking around to figure out which tools I was working with and my exact needs!