That could be because of date field value. Load the date field separately like:
date(MEMBER_EXIT_DATE) as Date,
Do you get proper dates in Date field ? Check in the front-end. You might have to format the date field using date#() or so like:
date(date#(MEMBER_EXIT_DATE, 'YYYY-MMM-DD')) as Date, // change date format to suit your data
What is format of your date field MEMBER_EXIT_DATE??? Is it in Time stamp format??
If yes then you need to use Now() in the script instead of today() and need to use Floor function.
Use this code..It should get your desired result.
EMPLOYEE_POL_ID as EMPLOYEE_POL_ID_resid,
PLAN_ID as PLAN_ID_resid,
MEMBER_EXIT_DATE as MEMBER_EXIT_DATE_resid,
if(Floor(MEMBER_EXIT_DATE)>=Floor(Now()),count(EMPLOYEE_ID)) as new_id
Resident EMPLOYEE Group by EMPLOYEE_POL_ID,PLAN_ID,EMPLOYEE_ID;