Ok @NateS , I really want to get you there as once you get there you will 
. Now that I see what you have done, I think I changed it to something that will work. I could not remember if you could use formulas in joins, but I did it as an example. You will see them on the two labor CTEs. Give this a try and let me know what happens.
PercWorkNonShipJobsByMonth (1).baq (67.1 KB)
Thank you @jkane . This is sooooo close! I had to resolve a couple of SQL errors (grouping issues) but after that, it seems like it is returning one row per job, and at least some of the data is correct. I noticed some interesting things about the BAQ you sent.
In my Inner CTE queries, Non/ShippingJobsLabor, you have a criteria on the laborDtl but when I click on it, there are no criteria fields. I am not sure what that is about.
I see how you joined shipments to both of those inner CTEs. In one case my nonshiplabor is doubled. I suspect this is due to multiple shipment lines.
PercWorkNonShipJobsByMonth2.baq (61.4 KB)
Yeah, I have no criteria, I deleted what you had in your original. It might just be a ghost from that.
So, one thing you can do to try and track down why that is happening is to turn that CTE into a Top Level, move it to the last subquery and change the other Top Level to a CTE. Preview it and you can see what the data is returning in the CTE. You might even be able to turn it into a Top Level and just run it, I forget what happens when there are two top levels.
I have been following this since I rarely do CTEs and I have a job that shipped in three different months, so the hours are in both shipment month and non shipment month for all three months.
I added in counts for the ship packs and lines. I think the issue is when there is more than one pack and/or line. Here you can see a section of my data. The yellow highlight is one correct job showing the right hours on the ship side and the right hours on the nonship side. In the rows above it you can see the job from my first example. 88.59 is the correct hours spent on the job in the same month that it shipped. However, 205.36 hours is double the number of hours spent outside of the shipping month.
I was hoping I could count up the packs and lines and those numbers would make sense so that I could just do a divide by #lines and get the right amount. But there is clearly something else going on here.
In this case (26053/7) There were two shipment packs, one had 4 lines and one had 2 lines. I think that I need to divide by the number of packs, but I can’t seem to return that value correctly. When I remove the packs and lines columns, then I only get one row for each job, and the pack count stays stuck at 1.
Why don’t I do CTEs? This… exactly this.
I really appreciate your help on this. It really seems like it should be an easy win, but I am just not getting it.
I am not sure it is a CTE issue, it is just you are splitting the job two ways. once by shipments and once by hours in a month. I did this not with CTEs for margin analysis and I just took the last date for the job to avoid this. Otherwise you would have to slice the job down to hours per unit of labor and allocate those to the shipments by quantity.
So, if you do multiple shipments off of a job, you need to do a group by on the JobNum and a Min or Max on the PackNum. That will get you down to a distinct list of Jobs.
Actually, let’s think about that. If you shipped something this month from Job 123 and you shipped a quantity last month from Job 123, how do you want to handle that?
That does make things confusing. I think, I would want to know the total labor hours that were performed on the job in the same month that it shipped. All time spent on the job in the same month that a shipment went out should be ShipLabor, and any labor performed in months that a shipment did not go out can be considered NonShipLabor. Does that make sense?
That is what I would have said.
So, the Shipments CTE, group on Job and bring in the shipdate in an expression to take the MAX ship date. That should get rid of any duplicates.
You need to take out the Month and Year because you are still creating rows for each shipment. Remove those, add in another CTE that takes in the first CTE and make expressions for the Month and Year from that.
do i relink the inner CTEs to the unique shipment CTE or leave them on the original shipments cte?
You now need to bring in the new shipment CTE and delete the old one.
This is what I have. I think it is working perfectly. I will be checking the results thoroughly before I release it to the managers. Thank you for your help. I can’t say I get CTEs anymore now… but I do see the potential!
PercWorkNonShipJobsByMonth2.baq (68.5 KB)
A CTE is just an in-memory table.
