HELP!!! I am having difficulty creating set it and forget it reporting. I have a transaction table that contains posting dates mm/dd/yy. and in comparision with previous day reporting today()-1. I need to continuously be able to pull current month to date, previous month to date, prior year month to date, current quarter to date, prior quarter to date, etc. All of this without having to go into my report daily and hardcode the dates I am looking for.
What I have tried thus far, which worked for the month of March, but not since then is
current date = today()-1 since our flat files are from yesterday! Then i have this date broken down in to Yr, Qtr, Mon, Day
Prior Month date = today()-31 which doesn't necessarily work for the month roll over or for February since its a shorter month
Prior Year Month date = today()-366 for this year only, broken this out in to Yr, Qtr, Mon, Day
then to put beside my transactions
for current day = case when (t1.'CURRENT DATE DAY'n = t1.'POST DATE DAY'n) and (t1.'CD MONTH'n = t1.'POST DATE MONTH'n) and (t1.'CD YEAR'n = t1.'POST DATE YEAR'n) then 'CD' else '' end
for MTD = CASE WHEN (( t1.'CD YEAR'n =t1.'POST DATE YEAR'n) AND t1.'CD MONTH'n = t1.'POST DATE MONTH'n) THEN 'MTD' ELSE '' END
for PMTD =CASE WHEN (( t1.'PMTD YEAR'n =t1.'POST DATE YEAR'n) AND ( t1.'PMTD MONTH'n = t1.'POST DATE MONTH'n)) THEN 'PMTD' ELSE '' END
etc, etc.
and it just didn't work.... If anyone has any workable and tested solutions, I would be grateful if you would share.
Thanks in advance
El
