InSQL 7.1 - count how many times a tag equals 1 over a specified time period query....

Need help to create a query to count how many times over a specified time the tag(s) equal the value of 1.

Thanks

Gary

Would you please provide some sample data and an example of expected results?

|||SELECT at.TagName,
'month'=month(datetime),
'YEAR'=YEAR(DATETIME),
TAGVALUE =1,
FREQ = COUNT(*)
FROM AnalogTag at, v_AnalogHistory ah
WHERE DateTime >= "2007/07/01 00:00"
AND DateTime <= "2007/07/31 00:00"
AND ah.TagName in ('PV249_STEP_NO')
AND ah.Value = 1
AND ah.TagName = at.TagName
AND wwRetrievalMode = 'DELTA'
GROUP BY at.TagName, month(DATETIME), YEAR(DATETIME)

I think I did it, using the above. I need to test a little more.

the value of the tag increments from 1 to 64, my purpose is to count the cycles of the tag.

Thanks

Gary|||

It looks like you have it correct for TagValue = 1; if you are looking to get it for all 64 different values of the tag, it would be something like:

Code Snippet

SELECT at.TagName,
'month'=month(datetime),
'YEAR'=YEAR(DATETIME),
-- TAGVALUE =1,
ah.value as TAGVALUE,
FREQ = COUNT(*)
FROM AnalogTag at, v_AnalogHistory ah
WHERE DateTime >= "2007/07/01 00:00"
AND DateTime <= "2007/07/31 00:00"
AND ah.TagName in ('PV249_STEP_NO')
-- AND ah.Value = 1
AND ah.TagName = at.TagName
AND wwRetrievalMode = 'DELTA'
GROUP BY at.TagName, ah.value, month(DATETIME), YEAR(DATETIME)