Percent of Work Performed on Jobs Shipped in the Same Month Work is Performed

[NateS (Nate Schoonover)]

I am working on a BAQ to give some information about how much work is done on a job in the same month that it ships. I am very close, but I am getting duplicates in my output data. I am having trouble tracking and eliminating the duplicates.

My BAQ starts with the ship tables to determine the date shipments went out. From that, we lookup the number of hours spent on the job where the payroll date is within the same month and year as the ShipDate. We also gather the other side of that data to learn how many hours were spent working on a job where the payroll date is in a different month and/or year from the ShipDate.

If there is only one shipment, then this seems to return the correct data, but if there was more than one shipment (or more than one shipping line) then the values get duplicated at the top level.

Here is a brief example of the output:

Here you can see job 26053/7 has 87.71 hours of labor incurred in April, and 0.88 hours incurred in May. In the other column you can see 4 records of labor incurred in a month or year other than the month/year of the shipment. I have the month and year in here for debugging, but I think I would like to get rid of those columns and just have a single row for each job that shows in total how much time was spent on jobs that ship and jobs that don’t ship.

As you can see the value 87.71 and 0.88 are duplicated, and the values in the other column are duplicated as well.

At the very bottom you can see job 26093/2 correctly reporting a single line with 13.18 hours logged on the same month as the shipment, and only 0.05 hours logged outside of the month.

Here is my SQL and BAQ file:

select distinct
	[ShipDtl2].[JobNum] as [ShipDtl2_JobNum],
	[JobHead].[PartNum] as [JobHead_PartNum],
	(year(ShipHead2.ShipDate)) as [Calculated_Year],
	(month(ShipHead2.ShipDate)) as [Calculated_Month],
	[ShippingJobsLabor].[Calculated_TotalLaborHours] as [Calculated_TotalLaborHours],
	[NonShippingJobsLabor].[Calculated_TotalLabor] as [Calculated_TotalLabor]
from Erp.ShipHead as ShipHead2
inner join Erp.ShipDtl as ShipDtl2 on 
	ShipHead2.Company = ShipDtl2.Company
	and ShipHead2.PackNum = ShipDtl2.PackNum
inner join Erp.JobHead as JobHead on 
	ShipDtl2.Company = JobHead.Company
	and ShipDtl2.JobNum = JobHead.JobNum
left outer join  (select distinct
	[LaborDtl].[JobNum] as [LaborDtl_JobNum],
	(month(LaborDtl.PayrollDate)) as [Calculated_LaborMonth],
	(year(LaborDtl.PayrollDate)) as [Calculated_LaborYear],
	(sum(LaborDtl.LaborHrs)) as [Calculated_TotalLaborHours]
from Erp.LaborDtl as LaborDtl
group by [LaborDtl].[JobNum],
	(month(LaborDtl.PayrollDate)),
	(year(LaborDtl.PayrollDate)))  as ShippingJobsLabor on 
	ShipDtl2.JobNum = ShippingJobsLabor.LaborDtl_JobNum
left outer join  (select distinct
	[LaborDtl1].[JobNum] as [LaborDtl1_JobNum],
	(year(LaborDtl1.PayrollDate)) as [Calculated_LaborYear],
	(month(LaborDtl1.PayrollDate)) as [Calculated_LaborMonth],
	(sum(LaborDtl1.LaborHrs)) as [Calculated_TotalLabor]
from Erp.LaborDtl as LaborDtl1
group by [LaborDtl1].[JobNum],
	(year(LaborDtl1.PayrollDate)),
	(month(LaborDtl1.PayrollDate)))  as NonShippingJobsLabor on 
	ShipDtl2.JobNum = NonShippingJobsLabor.LaborDtl1_JobNum
where (ShipHead2.ShipDate >= dateadd (year, -4, Constants.Today))
 and (not ShipDtl2.JobNum = ''  and (month(ShipHead2.ShipDate)) = ShippingJobsLabor.Calculated_LaborMonth  and (year(ShipHead2.ShipDate)) = ShippingJobsLabor.Calculated_LaborYear  and (month(ShipHead2.ShipDate)) <> NonShippingJobsLabor.Calculated_LaborMonth  and (year(ShipHead2.ShipDate)) <> NonShippingJobsLabor.Calculated_LaborYear)

PercWorkNonShipJobsByMonth.baq (43.8 KB)

Thanks for taking a look!
Nate

[jkane (John Kane)]

@Mark_Wonsil , what do you do when you find yourself answering questions the same way over and over again? Is there an emoji I should use?

@NateS , you have heard this from me before, and you will hear it again and again.

CTE

Build CTE’s to summarize the data into one line, identify the key at the top level that will return 1 row, then join them together and you will have the top query driving the key to the CTE to pull in the summarized row from your CTE.

[NateS (Nate Schoonover)]

@jkane

you have heard this from me before, and you will hear it again and again.

CTE

nooooooooo!!!

[jkane (John Kane)]

For me, it is really simple, I know that does not help. But let me try to get you there.

What is your key(s) for the end result? I would guess it is the first column with the Job Num and… not sure what the second part of that column is. Can you create a BAQ that will return one row for that and whatever other fields that you want?

Then, can you create a BAQ that summarizes the data you want to not repeat into rows with the same key(s) that you are using in the first one?

If you can do both of those separate BAQs, then all you need to do is create one BAQ with 2 CTEs, then use those CTEs as the tables for the top level, join on your key(s), and you will have the single rows that you want.

Hopefully that makes sense.

[Mark_Wonsil]

@jkane

what do you do when you find yourself answering questions the same way over and over again?

[jkane (John Kane)]

There you go! Whenever I answer a question saying to use a CTE, I’ll start them all with that gif.

I expect to see that on your responses about dev tools!!

[NateS (Nate Schoonover)]

@jkane

[jkane (John Kane)]

Bookmarked!!

[Mark_Wonsil]

In fairness, if something better comes up, I’m all about it. It’ll be a while before something better than CTE’s come up though. I mean the kind you don’t get in football or boxing.

[jkane (John Kane)]

@Mark_Wonsil

I mean the kind you don’t get in football or boxing.

[dcamlin (David Camlin)]

I just saw one the other day that immediately made me think of you…

When does a joke become a dad joke?

When it becomes apparent!

[NateS (Nate Schoonover)]

@jkane

For me, it is really simple

I gave you the answer, but I am still struggling to implement it.

I think that my two CTEs are ShippingJobLabor and NonShippingJobsLabor. These have the same fields: Job, LaborMonth, LaborYear, and TotalLaborHours.

How do I join them together at the top level? I also have a Shipments Subquery that lists all the shipments by job.

I don’t know why this is always so difficult for me!
PercWorkNonShipJobsByMonth.baq (60.0 KB)

[jkane (John Kane)]

Start at the base, what is your lowest level of data. Sounds like the Shipments Subquery, is that a CTE? Is that the data you are summarizing?

Next question is, how do you get to your list of jobs?

If you can break down your queries into the following:

CTE Query: Labor Ship Jobs

• Field: Job
• Field: Top Part
• Field: Total Labor

CTE Query: Labor Not Ship Jobs

• Field: Job
• Field: Top Part
• Field: Total Labor

CTE Query: List of Jobs You Want

• Field: Job
• Field: Top Part

Top Level Query: Final Query
Add in the 3 CTEs as tables, set List of Jobs You Want as table 1 as that will be your base. Add in the other tables joining them to Top Level on Job and Part Num.

• Field: Top Level.Job
• Field: Top Level.Top Part
• Field: Labor Ship Jobs.Total Labor
• Field: Labor Not Ship Jobs.Total Labor

[NateS (Nate Schoonover)]

I keep getting:
Possible incorrect order of subqueries. CTE subquery is going after Top Level subquery.
My Top level is a “Top”. The other three subqueries are “CTE”. The top is the first in the subquery list.
Screenshots to show how I setup the CTEs.

[NateS (Nate Schoonover)]

[NateS (Nate Schoonover)]

@jkane

what is your lowest level of data. Sounds like the Shipments Subquery, is that a CTE?

My list of jobs is the lowest level of data. I want a unique row for every job (shipped in the last 4 years).
Part Num is not critical for joining, but I would like to add it on as a reference at the very end.

[jkane (John Kane)]

Can you show me the Subquery List tab?

[NateS (Nate Schoonover)]

[jkane (John Kane)]

Top Level needs to be #4.

[NateS (Nate Schoonover)]

That allowed my CTE to run, but I am still not getting the results I expect. I still get duplicate values. I know it is giving me exactly what I am asking for, I just don’t know how to ask it nicely enough.

PercWorkNonShipJobsByMonth.baq (65.9 KB)