NOTE: I cross-posted the same question in the Avaya Definity Systems forum.
I am trying to create a new query in Avaya CMS (Call Management System) v11.0, a proprietary application that uses Informix SQL. I have been creating queries and reports with this tool for more then a couple of years, but recently have been pushing the application more. The application uses a front-end that limits the types and ways that queries are entered.
I am trying to create a query that provides month-to-date and year-to-date totals. I have successfully created a week-to-date formula (see below), but have not been successful yet with the other queries. The $var fields indicate user inputs when they run the report.
Here is the week-to-date query that DOES work.
SELECT ROWS WHERE:
dsplit.ACD = $i_acd AND dsplit.SPLIT = $i_split AND dsplit.ROW_DATE <= $i_date AND (dsplit.ROW_DATE >= ($i_date – WEEKDAY($i_date)))
The weekly query subtracts the day of week (0 – Sunday, 7 – Saturday) from the variable $i_date. So if I was running a report for Thursday, the query would subtract 4 from the date to give me data for Sunday thru Thursday. This query WORKS.
According to INFORMIX SQL documentation, the next query should work, but Avaya's CMS is not returning the correct information. I am beginning to think the CMS does not recognize all of the INFORMIX SQL functions, but of course, Avaya does not document which ones they support. Here is the query that DOES NOT work.
SELECT ROWS WHERE:
dsplit.ACD = $i_acd AND dsplit.SPLIT = $i_split AND dsplit.ROW_DATE <= $i_date AND (dsplit.ROW_DATE >= ($i_date – DAY($i_date)))
I am trying to subtract the DAY from the variable $i_date to get the beginning of the month. The query passes testing and the report generates, but it does not produce the correct data. I have tried several revisions, without success.
Any help or a different method would be appreciated. Thank you.
Eric
I am trying to create a new query in Avaya CMS (Call Management System) v11.0, a proprietary application that uses Informix SQL. I have been creating queries and reports with this tool for more then a couple of years, but recently have been pushing the application more. The application uses a front-end that limits the types and ways that queries are entered.
I am trying to create a query that provides month-to-date and year-to-date totals. I have successfully created a week-to-date formula (see below), but have not been successful yet with the other queries. The $var fields indicate user inputs when they run the report.
Here is the week-to-date query that DOES work.
SELECT ROWS WHERE:
dsplit.ACD = $i_acd AND dsplit.SPLIT = $i_split AND dsplit.ROW_DATE <= $i_date AND (dsplit.ROW_DATE >= ($i_date – WEEKDAY($i_date)))
The weekly query subtracts the day of week (0 – Sunday, 7 – Saturday) from the variable $i_date. So if I was running a report for Thursday, the query would subtract 4 from the date to give me data for Sunday thru Thursday. This query WORKS.
According to INFORMIX SQL documentation, the next query should work, but Avaya's CMS is not returning the correct information. I am beginning to think the CMS does not recognize all of the INFORMIX SQL functions, but of course, Avaya does not document which ones they support. Here is the query that DOES NOT work.
SELECT ROWS WHERE:
dsplit.ACD = $i_acd AND dsplit.SPLIT = $i_split AND dsplit.ROW_DATE <= $i_date AND (dsplit.ROW_DATE >= ($i_date – DAY($i_date)))
I am trying to subtract the DAY from the variable $i_date to get the beginning of the month. The query passes testing and the report generates, but it does not produce the correct data. I have tried several revisions, without success.
Any help or a different method would be appreciated. Thank you.
Eric
