This is probably obvious to SQL people, but can I do math on columns in a union?
I have two subqueries - one is a summary of UD08 (production goals) and the other is a summary of Project (actual production). One is the top level and the other is a union.
How well we are doing is the math between them. So in this example (from a pic from last week), we are -14 for “Offline to date” - that’s 77-63.
Can a BAQ do this math itself, to subtract the union from the top level?
I can think of two ways to do this off the top of my head - and depending on how you use this table, it might make more sense to go one path vs. the other.
Make a new top level subquery and pick the first subquery in the union… which you would change to a CTE subquery.
Running total:
To do a “running total” you can do a sum over partition by type calculated field. Effectively, you’d partition by Range here and Order by Type. I can send an example of something I’ve done like this or you can google that.
Subquery with Calculated Field:
Group By Range and create 6 calculated fields (Goals and Actuals for each column). Then for each of the calc fields, do a case statement to sum the column only if the type = goals or actuals. Then you can do a calculated field for each of the columns to get the difference… something like (Gold To Date Actuals) - (Gold To Date Goals)
Again, pardon my SQL ignorance in advance.
So, the running total - I think this is an additional field in the same dataset? So I’d still have 2 rows but additional columns? Not a third row, right?
Then the subquery - I think results in 1 row with lots of fields? (3 per old numerical column)
Edit: I see that I didn’t explain what I wanted in a good way. You certainly did answer the question. But I was thinking of having the math in a new, third row. Like you might do in Excel.
FYI, I’d like this to work in Epicor, but ultimately the goal is to email the report daily, so I think at that point I can live with it as is and manipulate the format as needed. I think.
But if this can be done all at once, all the better.
If you’re trying to email the report, and you’re creating a report for this… let the report do the work.
You can put in a calculated field in your report as a new row under the data table you are putting in there. That’s yet another option. Might be the easiest option, honestly.
Huh. Yeah that’s a good point.
So, I said “report” in a generic sense; really I meant “an HTML table.”
But you are implying SSRS reports, and really, I don’t know why I didn’t think of that in the first place. That would work the best.
I’m marking that as the solution, though sorry to anyone in the future since maybe that’s not your solution lol.
This can be done, it’s a hierarchical CTE. A little harder to set up (I would have to play around to figure it out) but that’s do-able.