max effective date pull based on effective dates

[John1Chr]

SELECT t.CHARTFIELD_VALUE, t.W_CF_ATTRIBUTE, t.W_CF_ATTRIB_VALUE, t.EFFDT, t.EFF_STATUS
FROM SYSADM_PS_X_S_M_CFATTRIB AS t
WHERE (((t.W_CF_ATTRIBUTE)="BUD_PROG" Or (t.W_CF_ATTRIBUTE)="BUD_ACT") AND ((t.EFFDT)=(SELECT Max(m.EFFDT)
FROM SYSADM_PS_X_S_M_CFATTRIB AS m
WHERE m.CHARTFIELD_VALUE = t.CHARTFIELD_VALUE
AND m.W_CF_ATTRIBUTE = t.W_CF_ATTRIBUTE
AND m.EFFDT between #7/1/2012# and #7/1/2013#)))
ORDER BY t.CHARTFIELD_VALUE;


Is there a way to write this based on fiscal year like listed below? I would like to have a parameter field that feeds it like [fiscal Year] that we could feed the query with and then it would grab the attributes base on fiscal year as listed below. I want to grab the greatest date and the date can be as low as 1/1/1901


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

 

[BitZero]

Here's a "divide and conquer" approach. First, create a query named qry_cfattrib_FY that looks like this:

SELECT a.*, [Fiscal Year]
FROM sysadm_ps_x_s_m_cfattrib AS a
WHERE a.effdt between cdate("7/1/" & [Fiscal Year]) and cdate("6/30/" & ([Fiscal Year] + 1));

This query uses the [Fiscal Year] parameter to select all of the records where effdt is in that fiscal year.

Next, create this query:

SELECT t.CHARTFIELD_VALUE, t.W_CF_ATTRIBUTE, t.W_CF_ATTRIB_VALUE, t.EFFDT, t.EFF_STATUS
FROM qry_cfattrib_fy AS t
WHERE (((t.W_CF_ATTRIBUTE)="BUD_PROG" Or (t.W_CF_ATTRIBUTE)="BUD_ACT")
AND ((t.EFFDT)=(SELECT Max(m.EFFDT)
FROM qry_cfattrib_fy AS m
WHERE m.CHARTFIELD_VALUE = t.CHARTFIELD_VALUE
AND m.W_CF_ATTRIBUTE = t.W_CF_ATTRIBUTE)))
ORDER BY t.CHARTFIELD_VALUE;

which is very similar to your original query, except the table name has been changed to qry_cfattrib_fy, and the between 7/1/2012 and 7/1/2013 line has been removed.