CMS Historical Report Help

[eholen]

I am trying to create a new query in Avaya CMS v11.0 (Terminal not Supervisor) to provide month-to-date and year-to-date totals. I have successfully created a week-to-date formula, but have not been successful yet with the other queries. 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 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 list which ones they do 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