hi
I want to created a pivot which shows a count of how many operations have been completed over the last 12 months by Month for each resource we have.
Sounds straightforward…
subquery:
Along with calculated fields to work out the Month name:
Top level:
Buty my results are many many resource lines
What am I missing please?
Update- each resource is showing 12 lines - which must be because there’s 12 months - how do i roll it all up to one line?
Instead of pivoting I would just do a case statement for each month. i.e., SUM(CASE WHEN Month=January THEN 1 ELSE 0 END). Group by resource ID.
Based on the images shown, I agree with @aosemwengie1, easiest way is SUM of CASE in Top level query grouping by your ResourceID.
Example grouped by Company, Plant, WH Code, PartNum:
Calculated_M1:
SUM( case when WhsePartTrans.Calculated_TranMonth = [master].[dbo].[fx_TranMonth](1) THEN WhsePartTrans.Calculated_Qty ELSE 0 END )
Mine creates a rolling 12 Month prior +current month inventory usage along with costing and current inventory levels.
Your pivot is duplicating lines because you have the job number in the query below it. It will do the “grouping” by that lower query, even if you aren’t showing it in the pivot. You need to remove that field, and then it will roll up by Resource ID. You’ll need to adjust your formula to something like Count(1) to count the number of rows.
