You possibly need a UNION.
-- sent from myPhone --
I can't honestly say that I understand 100% the scenario that you describe, but one thing that looks suspect to me is:
I see no constraint in the above query which limits which records from CHARTING_CASE are to be included in the calc.
I'm kind of expecting to see something like what you've got posted above, but with a WHERE clause that specifically targets the records in the date range that you want.
Otherwise all records will all be included in the ExecuteSQL tally regardless of the current status of any relationship between CHARTING and CHARTING_CASE.
I'm wondering if that's what is going on here. Are there multiple years of records in the CHARTING_CASE table?
HTH & very best,
p.s. One semi-related tidbit that I took away from playing around with ExecuteSQL is that, whenever I'm using a GROUP BY clause, it's worth seeing if I can get the same result, but with better performance, by using a hybrid approach which uses regular old FM calcs to concatenate a series of ExecuteSQL statements. It sounds inelegant, I know, (and it feels inelegant, too), but I've found that sometimes such an approach yielded better performance.
So, that may be the problem, indeed. But I did try once for fun to target the 'case' table and I remember getting the question mark.
Tomorrow I'll take a look.
Sent from my iPhone
Okay, so this is not getting me the right count either. Intake_Month_Name is a calculation that gives me the month name for the Intake_Date. If I use "CASE" for the table it doesn't give me any results. And CASE is the table, charting case is the TO.
WHERE Case_Type='Auto' and
Intake_Date between '2013-9-1'
GROUP BY Intake_Month_Name"
And what's the best way to concatenate results? I do get this working I'll hopefully end up with multiple variables that will look like this (summing up how many casess have the case_type "auto" for month one, two three, etc.
and I need to combine 4 of them so they end up looking like this:
115, 125, 45, 33
84, 45, 56, 23
1 of 1 people found this helpful
I would check out the syntax you are using for using dates in your query.
Intake_Date BETWEEN '2013-9-1' AND '2014-9-30'
I believe that you want to be using the DATE term to let ExecuteSQL know that those strings are to be treated as Dates, e.g.:
Intake_Date BETWEEN DATE '2013-9-1' AND DATE '2014-9-30'
One nice way to avoid dealing with this is to supply your dates as parameters in your call to ExecuteSQL, e.g.:
varQUERY = "SELECT ....... FROM ..... WHERE Intake_Date BETWEEN ? AND ?"
ExecuteSQL( varQuery; ""; ""; Date( 9; 1; 2013 ); Date( 9; 30; 2014 ) )
As for the concatenation:
It's my hope that it will be possible for you to craft some SQL that will generate a single row for a single month, but which will include all of the columns you need in that one row.
That's what I'm hoping will pan out of this. If so, that, of course, would be a much easier task as far as the concatenation goes.
If not, we'll figure something out.
1 of 1 people found this helpful
I think you want that already you have.
WHERE Intake_Date between '2013-9-1' AND '2014-9-30'
(and removing COUNT(*) )
to the query in first post may be the answer.
If your table name is "CASE", it should be quoted in SQL since it is reserved word.
Thank you. Indeed, CASE was the name of the table. I need to learn the reserved words.
With great help from Steve we got this working and the interactive chart looks really nice: http://cloud.zerobluetech.com/image/3a2j14291i1j
Thank you, again!