These queries are designed to determine a true false value to use as variables.
Looking for Open/Close status of an area not the area received by the event:
SELECT AREAOC = CASE
WHEN AREAOC LIKE "1" THEN "TRUE"
ELSE "FALSE" END
FROM SAREA
WHERE SERIALNO={ME}
AND AREA LIKE "2"
Looking for Open/Close status:
SELECT AREAOC = CASE
WHEN AREAOC LIKE "1" THEN "TRUE"
ELSE "FALSE" END
FROM SAREA
WHERE SERIALNO={ME}
Looking for Zone description details
SELECT DESCR = CASE
WHEN DESCR LIKE "%door%" THEN "TRUE"
ELSE "FALSE" END
FROM SZONES
WHERE SERIALNO={ME} and ZONE = {ZN}
Looking for Zone number in a group:
SELECT ZONE = CASE
WHEN ZONE in (1,2,3) THEN "ONETOTHREE"
WHEN ZONE in (4,5,6) THEN "FOURTOSIX"
ELSE "FALSE" END
FROM SZONES
WHERE SERIALNO={ME} and ZONE = {ZN}
Selecting the top Open in the last 5 minutes:
IF EXISTS (SELECT TOP 1 "TRUE" FROM CLOG{0} WHERE OSTCODE="OP" AND SERIALNO={ME} AND LOGDATE >= DATEADD(MINUTE,5,GETUTCDATE() )
+
(SELECT TOP 1 "TRUE" FROM CLOG{0} WHERE OSTCODE="OP" AND SERIALNO={ME} AND LOGDATE >= DATEADD(MINUTE,-5,GETUTCDATE()) ) SELECT "TRUE" AS RES ELSE SELECT "FALSE" AS RES
Find Resolution Code
This is failing to function in Post processing (2.1.9) as of writing this. The query is working fine in SQL but will not run in Manitou.
SELECT FACHRS = CASE
WHEN FACHRS LIKE "%AC%" THEN "TRUE"
ELSE "FALSE" END
FROM CLOG{0}
WHERE SERIALNO={ME}
and STCODE={EC}
and AREA = {AR}
and ZONE = {ZN}
