max effective date non future date

[John1Chr]

Greetings,

I would like to modify on one of the working max effective date sqls below or come up with new sql to capture the max effect date <7/1/2012.

Ideally, I would like the sql to grab the max effective date for each year and have it classify year like:

<#7/1/2012# is FY12
>=#7/1/2012# and <=#6/30/2013 is FY13
>=#7/1/2013# and <=#6/30/2014 is FY14
>=#7/1/2014# and <=#6/30/2015 is FY15



SELECT t.CHARTFIELD_VALUE, t.W_CF_ATTRIBUTE, t.W_CF_ATTRIB_VALUE, t.EFFDT, t.EFF_STATUS
FROM (SELECT CHARTFIELD_VALUE, W_CF_ATTRIBUTE, MAX(EFFDT) AS latest FROM SYSADM_PS_X_S_M_CFATTRIB GROUP BY CHARTFIELD_VALUE, W_CF_ATTRIBUTE) AS m INNER JOIN SYSADM_PS_X_S_M_CFATTRIB AS t ON (m.CHARTFIELD_VALUE = t.CHARTFIELD_VALUE) AND (m.W_CF_ATTRIBUTE = t.W_CF_ATTRIBUTE) AND (m.latest = t.EFFDT)
WHERE (((t.W_CF_ATTRIBUTE)="PROG" Or (t.W_CF_ATTRIBUTE)="ACT"))
ORDER BY t.CHARTFIELD_VALUE;


or


SELECT SYSADM_PS_X_S_M_CFATTRIB.CHARTFIELD_VALUE, SYSADM_PS_X_S_M_CFATTRIB.W_CF_ATTRIBUTE, SYSADM_PS_X_S_M_CFATTRIB.W_CF_ATTRIB_VALUE, SYSADM_PS_X_S_M_CFATTRIB.EFFDT
FROM SYSADM_PS_X_S_M_CFATTRIB
GROUP BY SYSADM_PS_X_S_M_CFATTRIB.CHARTFIELD_VALUE, SYSADM_PS_X_S_M_CFATTRIB.W_CF_ATTRIBUTE, SYSADM_PS_X_S_M_CFATTRIB.W_CF_ATTRIB_VALUE, SYSADM_PS_X_S_M_CFATTRIB.EFFDT
HAVING (((SYSADM_PS_X_S_M_CFATTRIB.W_CF_ATTRIBUTE)="PROG") AND ((SYSADM_PS_X_S_M_CFATTRIB.EFFDT)=(Select Max(EFFDT) From [SYSADM_PS_X_S_M_CFATTRIB] M WHERE M.CHARTFIELD_VALUE = [SYSADM_PS_X_S_M_CFATTRIB]![CHARTFIELD_VALUE] )));

 

[SkipVought]

hi,

Maybe this...

, 
if(t.EFFDT<#7/1/2012#,'FY12',
iif(t.EFFDT<=#6/30/2013,'FY13',
iif(t.EFFDT<=#6/30/2014,'FY14',
iif(t.EFFDT<=#6/30/2015,'FY15','OTHER')))) as FiscalYR

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

oopa, I miss spelled the first iif!!!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dhookom]

I don't care for nesting IIf()s in queries (or anywhere).
If you want to convert a date to a fiscal year, try something like:

FiscalYear: "FY" & Format(DateAdd("m",6,[YourDateFieldHere]),"yy")

Ideally you would create a one record, one field table with the number 6 stored to describe the number of months to add to determine the fiscal calendar.

Duane
Hook'D on Access
MS Access MVP

 

[John1Chr]

Not sure how to write the total formula.

Let's say CHARTFIELD_VALUE IS S041A11 and in FY12 the Prog was S0401 and the ACT was S040111, respectively. (ie max effective date for those attributes was 1/1/1901 falling into the FY2012 parameters. It was changed in FY13 (ie max effective date 7/1/2012) to S0402 and S040112 and in FY15 (ie max effect date 7/1/2014 just the PROG attribute was changed to S0403. In FY14 there were no changes so I would like to report the max effective date from FY13. Many of the CHARTFIELD_VALUEs have no changes (ie max effect date 1/1/1901) but I want to report for every year the unchanged and changed values. (PROG and ACT)

I need to have PROG and ACT values for all years so I would like to report:

FOR CHARTFIELD_VALUE SO41A11

FY12 FY13 FY14 FY15

PROG S0401 S0402 S0402 S0403

ACT S040111 S040112 S040112 S040112

EFF DT 1/1/1901 7/1/2012 7/1/2012 7/1/2014


Skip, I think your iif might work but not sure exactly how to fit in. Sorry for all the jargon...hard to explain without it. Not sure if there is a easy way to do.

I can do it easily with make table and appending but thought I would run it by the wizards to see if their might be Sql that could do it in one query.