SELECT F_SGRP, F_TEST, F_VAL
FROM SGRP_TST
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.
Quote:
What I don't understand is this:
Code:
F_SGRP>%F_SGRP%
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
FROM SGRP_TST
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
Sorry I keep posting one after the other but I'm getting excited now!
I Determined the most recent/largest "F_SGRP" value with the specified "F_TEST" tag in my STARTUP
In this case - the two values (Production Line 4 Accumulated downtime and Production Line 4 Accumulated available time) are saved at the same time so they share a "F_SGRP" Number
I've added the statement
AND F_SGRP=%F_SGRP%
and now i'm only returning the last record for each "F_TEST" Tag
This is progress as I will no longer need a separate provider for both "F_TEST" (Downtime and Available Time) on Line 4
But I'm not quite there yet as the "F_TEST" records for say Production Line 2 will not be saved at the same time as Production Line 4 so they will have a different "F_SGRP" value
"F_SGRP" increments each time a record is saved. I only want the record containing the largest "F_SGRP" frome each of the "F_TEST" tags
Time to learn a little more about SQL.
Code:
SELECT F_TEST, MAX(F_SGRP) AS MAXSGRP
FROM SGRP_TEST
WHERE F_TEST IN ( ...any length list of numbers ... )
GROUP BY F_TEST
ORDER BY F_TEST
I still don't understand what your "startup" is for.
That query, alone, will give you *EXACTLY* what it says: That maximum F_SGRP value *PER F_TEST* for all the F_TEST values given in that IN ( ... ) list.
I had to look up "SPC". "Statistical Process Control"?
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.
__________________
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
Code:
SELECT F_TEST, MAX(F_SGRP) AS MAXSGRP
FROM SGRP_TEST
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
Quote:
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
Quote:
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
FROM SGRP_TST
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!
Before you post, read our: 
Sql combine queries help
