I'm loading a table to present in a query/graph. Each record counts the instances of PHS created in the month queried. The query is germane to a specific Organization which is passed to the process. Here's the code. Where you see -1, I am simply decrementing the current month from -0 to -11 to capture the previous 12 months. So I have 12 similar rules that are identical but I use -0, -1, -2, etc...
CREATE WorkflowStats WITH
WorkflowStats.CasesOpened=COUNT PHS WHERE (PHS.Organization=Organization AND MONTH(MONTH_ADD(PHS.Case_DateOpened,-1))=MONTH(MONTH_ADD(CURRENT_DATE,-1)) AND
YEAR(PHS.Case_DateOpened)=YEAR(MONTH_ADD(CURRENT_DATE,-1)))
As a refresh, MONTH return the month number (i.e., 11), and MONTH_ADD returns a date.
Interesting result. I actually have just one record that will qualify for the entire year and it should be counted in the previous month. But the process loads 12 records with zero count. If I change the date of the qualifying record to this month, my process loads all 12 records with a 1 except for the 12th which is zero. Each rule is exactly the same except for the decrement number.
I'm hopeful that by posting my failure here I will have an epiphany, but in case I don't, please accept the challenge and guide me toward the light.
