Max Effective Date Based On Year

[John1Chr]

This is in the SQL Expression editor Crystal 8.5.

Works for grabbing everything with a max effect date of as of now. Is there a way to write this based on fiscal year like listed below? I think I will have a parameter field that feeds it like {fiscal Year}


If Fiscal Year is 2012, then max effective date <7/1/2013
If Fiscal Year is 2013, then max effective date <7/1/2014
If Fiscal Year is 2014, then max effective date <7/1/2015
If Fiscal Year is 2015, then max effective date <7/1/2016

(SELECT MAX(PS_X_S_M_CFATTRIB2.EFFDT)
FROM SYSADM.PS_X_S_M_CFATTRIB PS_X_S_M_CFATTRIB2
WHERE PS_X_S_M_CFATTRIB2.CHARTFIELD_VALUE = PS_X_S_M_CFATTRIB.CHARTFIELD_VALUE and
PS_X_S_M_CFATTRIB2.W_CF_ATTRIBUTE = PS_X_S_M_CFATTRIB.W_CF_ATTRIBUTE and ((PS_X_S_M_CFATTRIB2.W_CF_ATTRIBUTE not in ('BUD_PROG','BUD_ACT') or
PS_X_S_M_CFATTRIB2.EFFDT < {fn NOW()})) )

There might be a better way to do this. What I am trying to do is create a report that will max effect date based on effect date being less than a certain date. I'm playing around with the grouping option and max effect dating on that.