I believe you want
SUM(Calculated_Total) OVER(PARTITION BY YearMonth ORDER BY YearMonth ROWS BETWEEN CURRENT ROW AND 1 PRECEDING)
If you skip the orderby then you won’t get a running total. It will just be the total in that partition you defined.
You mentioned using coalesce instead of the case statements I have. When I try to make a coalesce statement for the calculated company, part, or date field I get some really weird errors. Below are the code I was trying for the company and the error it causes after implementation:
coalesce(SOByMonth1.OrderRel_Company, ForeQty.Forecast_Company, MPSQty.MasProd_Company, FirmJobQty.JobHead_Company, UnfirmJobQty.JobHead1_Company, OHQty2.PartBin_Company,'Couldnt Find')
I am definitely no expert, so I will probably not say this correctly, but I think it is trying to build the results but does not “know” what that field is yet. Because you are trying to create a field that does not exist at the lower level for what you are coalescing in the first field. You would be better off building the CTE queries like I said earlier. Then build a part list from the 6 CTE queries to get a set listing of parts. Then link the part file to the other 6 CTEs to do your calculation.
1 Like
That was exactly it! Just found the column it was upset about it had dropped the 03 entirely.