SUM values BETWEEN Dates

customaware

Maybe I am not seeing the wood for the tree but...... can anyone suggest what might be wrong with this Process Action?... returns zero even though record definitely exist with HoursNumeric value between those dates.

SystemSettings.StatHoursThisMonth=SUM EmployeeShiftHours.HoursNumeric WHERE (EmployeeShiftHours.Date BETWEEN FIRST_DAY_OF_MONTH(CURRENT_DATE) AND LAST_DAY_OF_MONTH(CURRENT_DATE))

customaware

From my evaluations.....

It is fine under MS-SQL but fails under MySQL

BenHayat

eagles9999 wrote
From my evaluations.....

It is fine under MS-SQL but fails under MySQL

Perhaps Aware is not creating proper SQL for MySQL. I wonder how it works with default database that comes with Aware?

hpl123

Mark,
I've seen some problems when using functions inside of other functions like this, try allocating seperate date attributes for first/last day of month and then use the attributes in then SUM BETWEEN instead and see if that works.

BLOMASKY

have NO clue as usual, but instead of BETWEEN did you try >= 1st date AND <= last date?

That helps narrow down where mysql doesn't like this statement.

bruce

customaware

Yes I did Bruce.

I am guessing BETWEEN is tranlated to that internally in any event.

customaware

Will be fixed in next build.