Shop Load Report - WrkCtrCap calculations?

, ,

Like many reports, the RDD for Shop Load has calculated fields. Does anyone have ideas how Epicor calculates the Capacity buckets? Out plant manager is basically asking for a dashboard version of this report and I’m struggling to figure out how these are calculated.

Is it not the sum of the capacity for the Resource Group based on the time period selected on the print dialog?

The numbers aren’t lying up unless I’m doing something wrong.

Do you have multiple Production Calendars? Looking at our report, I could see how it would be difficult to calculate capacity if there are different calendars involved.

Hey John,

Yes we do have multiple but each Resource Group ID is assigned to one. I guess my struggle is how to parse the ProdCal table and also to check for holidays. I’ve never touched the ProdCal table before.

Edit: I believe you’re right, it’s the Resource Group “DailyCapacity” fields, just need to check for holidays that fall within the week. Thanks!

See if this helps at all.

Also, search for calendar and @hkeric.wci together as there are some good posts around calculating dates based off of using the calendars.

Yeah, seen that one, that’s calculating the load not capacity row.

When you run the Capacity Generation, do you delete the old capacity? Maybe you need to filter the results by CapRevID?

I wonder if that’s what’s causing this, some resource groups are perfect others are way off

What are your hours per day (8, 10, etc.)? How many resources in that resource group?

Isn’t the Daily Capacity fields “Hours”? I was just summing them all together for a “Weekly” total.

Yes, but I don’t know the specifics of your setup and was trying to see why the hours were so far off. I figured if I knew how many hours your calendar has and the number of resources in the group might help me figure it out.

1 Like

Correct again, we have disabled Resources in some groups so I need to account for that.

Closer, most are working but I’ve found a few problem children. This one doesn’t have any active Resources so doesn’t get onto JobOps at least so that may be part of the issue. This also doesn’t account for Holidays yet as I’m still verifying the “base” Weekly Capacity calculations.

Ok, the issue above was because the Resource Group didn’t have an assigned calendar and thus used the Site’s default Calendar. Plus I wasn’t checking for the number of Active & Total resources which are needed to get the proper hours using the RG’s fields.

I ended up coming up with two queries, one using ProdCal table the other ResourceGroup.

The ProdCal query below isn’t tied to the RG table yet but easy enough to add it.

select 
	[ProdCal].[CalendarID] as [ProdCal_CalendarID],
	[ProdCal].[HoursPerDay] as [ProdCal_HoursPerDay],
	(CAST(ProdCal.WorkWeek1 as INT) 
 + CAST(ProdCal.WorkWeek2 as INT) 
 + CAST(ProdCal.WorkWeek3 as INT)
 + CAST(ProdCal.WorkWeek4 as INT)
 + CAST(ProdCal.WorkWeek5 as INT)
 + CAST(ProdCal.WorkWeek6 as INT)
 + CAST(ProdCal.WorkWeek7 as INT)) as [Calculated_numWorkDays],
	(CAST(ProdCal.Hour001 as INT) +
 CAST(ProdCal.Hour002 as INT) +
 CAST(ProdCal.Hour003 as INT) +
 CAST(ProdCal.Hour004 as INT) +
 CAST(ProdCal.Hour005 as INT) +
 CAST(ProdCal.Hour006 as INT) +
 CAST(ProdCal.Hour007 as INT) +
 CAST(ProdCal.Hour008 as INT) +
 CAST(ProdCal.Hour009 as INT) +
 CAST(ProdCal.Hour010 as INT) +
 CAST(ProdCal.Hour011 as INT) +
 CAST(ProdCal.Hour012 as INT) +
 CAST(ProdCal.Hour013 as INT) +
 CAST(ProdCal.Hour014 as INT) +
 CAST(ProdCal.Hour015 as INT) +
 CAST(ProdCal.Hour016 as INT) +
 CAST(ProdCal.Hour017 as INT) +
 CAST(ProdCal.Hour018 as INT) +
 CAST(ProdCal.Hour019 as INT) +
 CAST(ProdCal.Hour020 as INT) +
 CAST(ProdCal.Hour021 as INT) +
 CAST(ProdCal.Hour022 as INT) +
 CAST(ProdCal.Hour023 as INT) +
 CAST(ProdCal.Hour024 as INT) +
 CAST(ProdCal.Hour025 as INT) +
 CAST(ProdCal.Hour026 as INT) +
 CAST(ProdCal.Hour027 as INT) +
 CAST(ProdCal.Hour028 as INT) +
 CAST(ProdCal.Hour029 as INT) +
 CAST(ProdCal.Hour030 as INT) +
 CAST(ProdCal.Hour031 as INT) +
 CAST(ProdCal.Hour032 as INT) +
 CAST(ProdCal.Hour033 as INT) +
 CAST(ProdCal.Hour034 as INT) +
 CAST(ProdCal.Hour035 as INT) +
 CAST(ProdCal.Hour036 as INT) +
 CAST(ProdCal.Hour037 as INT) +
 CAST(ProdCal.Hour038 as INT) +
 CAST(ProdCal.Hour039 as INT) +
 CAST(ProdCal.Hour040 as INT) +
 CAST(ProdCal.Hour041 as INT) +
 CAST(ProdCal.Hour042 as INT) +
 CAST(ProdCal.Hour043 as INT) +
 CAST(ProdCal.Hour044 as INT) +
 CAST(ProdCal.Hour045 as INT) +
 CAST(ProdCal.Hour046 as INT) +
 CAST(ProdCal.Hour047 as INT) +
 CAST(ProdCal.Hour048 as INT) +
 CAST(ProdCal.Hour049 as INT) +
 CAST(ProdCal.Hour050 as INT) +
 CAST(ProdCal.Hour051 as INT) +
 CAST(ProdCal.Hour052 as INT) +
 CAST(ProdCal.Hour053 as INT) +
 CAST(ProdCal.Hour054 as INT) +
 CAST(ProdCal.Hour055 as INT) +
 CAST(ProdCal.Hour056 as INT) +
 CAST(ProdCal.Hour057 as INT) +
 CAST(ProdCal.Hour058 as INT) +
 CAST(ProdCal.Hour059 as INT) +
 CAST(ProdCal.Hour060 as INT) +
 CAST(ProdCal.Hour061 as INT) +
 CAST(ProdCal.Hour062 as INT) +
 CAST(ProdCal.Hour063 as INT) +
 CAST(ProdCal.Hour064 as INT) +
 CAST(ProdCal.Hour065 as INT) +
 CAST(ProdCal.Hour066 as INT) +
 CAST(ProdCal.Hour067 as INT) +
 CAST(ProdCal.Hour068 as INT) +
 CAST(ProdCal.Hour069 as INT) +
 CAST(ProdCal.Hour070 as INT) +
 CAST(ProdCal.Hour071 as INT) +
 CAST(ProdCal.Hour072 as INT) +
 CAST(ProdCal.Hour073 as INT) +
 CAST(ProdCal.Hour074 as INT) +
 CAST(ProdCal.Hour075 as INT) +
 CAST(ProdCal.Hour076 as INT) +
 CAST(ProdCal.Hour077 as INT) +
 CAST(ProdCal.Hour078 as INT) +
 CAST(ProdCal.Hour079 as INT) +
 CAST(ProdCal.Hour080 as INT) +
 CAST(ProdCal.Hour081 as INT) +
 CAST(ProdCal.Hour082 as INT) +
 CAST(ProdCal.Hour083 as INT) +
 CAST(ProdCal.Hour084 as INT) +
 CAST(ProdCal.Hour085 as INT) +
 CAST(ProdCal.Hour086 as INT) +
 CAST(ProdCal.Hour087 as INT) +
 CAST(ProdCal.Hour088 as INT) +
 CAST(ProdCal.Hour089 as INT) +
 CAST(ProdCal.Hour090 as INT) +
 CAST(ProdCal.Hour091 as INT) +
 CAST(ProdCal.Hour092 as INT) +
 CAST(ProdCal.Hour093 as INT) +
 CAST(ProdCal.Hour094 as INT) +
 CAST(ProdCal.Hour095 as INT) +
 CAST(ProdCal.Hour096 as INT) +
 CAST(ProdCal.Hour097 as INT) +
 CAST(ProdCal.Hour098 as INT) +
 CAST(ProdCal.Hour099 as INT) +
 CAST(ProdCal.Hour100 as INT) +
 CAST(ProdCal.Hour101 as INT) +
 CAST(ProdCal.Hour102 as INT) +
 CAST(ProdCal.Hour103 as INT) +
 CAST(ProdCal.Hour104 as INT) +
 CAST(ProdCal.Hour105 as INT) +
 CAST(ProdCal.Hour106 as INT) +
 CAST(ProdCal.Hour107 as INT) +
 CAST(ProdCal.Hour108 as INT) +
 CAST(ProdCal.Hour109 as INT) +
 CAST(ProdCal.Hour110 as INT) +
 CAST(ProdCal.Hour111 as INT) +
 CAST(ProdCal.Hour112 as INT) +
 CAST(ProdCal.Hour113 as INT) +
 CAST(ProdCal.Hour114 as INT) +
 CAST(ProdCal.Hour115 as INT) +
 CAST(ProdCal.Hour116 as INT) +
 CAST(ProdCal.Hour117 as INT) +
 CAST(ProdCal.Hour118 as INT) +
 CAST(ProdCal.Hour119 as INT) +
 CAST(ProdCal.Hour120 as INT) +
 CAST(ProdCal.Hour121 as INT) +
 CAST(ProdCal.Hour122 as INT) +
 CAST(ProdCal.Hour123 as INT) +
 CAST(ProdCal.Hour124 as INT) +
 CAST(ProdCal.Hour125 as INT) +
 CAST(ProdCal.Hour126 as INT) +
 CAST(ProdCal.Hour127 as INT) +
 CAST(ProdCal.Hour128 as INT) +
 CAST(ProdCal.Hour129 as INT) +
 CAST(ProdCal.Hour130 as INT) +
 CAST(ProdCal.Hour131 as INT) +
 CAST(ProdCal.Hour132 as INT) +
 CAST(ProdCal.Hour133 as INT) +
 CAST(ProdCal.Hour134 as INT) +
 CAST(ProdCal.Hour135 as INT) +
 CAST(ProdCal.Hour136 as INT) +
 CAST(ProdCal.Hour137 as INT) +
 CAST(ProdCal.Hour138 as INT) +
 CAST(ProdCal.Hour139 as INT) +
 CAST(ProdCal.Hour140 as INT) +
 CAST(ProdCal.Hour141 as INT) +
 CAST(ProdCal.Hour142 as INT) +
 CAST(ProdCal.Hour143 as INT) +
 CAST(ProdCal.Hour144 as INT) +
 CAST(ProdCal.Hour145 as INT) +
 CAST(ProdCal.Hour146 as INT) +
 CAST(ProdCal.Hour147 as INT) +
 CAST(ProdCal.Hour148 as INT) +
 CAST(ProdCal.Hour149 as INT) +
 CAST(ProdCal.Hour150 as INT) +
 CAST(ProdCal.Hour151 as INT) +
 CAST(ProdCal.Hour152 as INT) +
 CAST(ProdCal.Hour153 as INT) +
 CAST(ProdCal.Hour154 as INT) +
 CAST(ProdCal.Hour155 as INT) +
 CAST(ProdCal.Hour156 as INT) +
 CAST(ProdCal.Hour157 as INT) +
 CAST(ProdCal.Hour158 as INT) +
 CAST(ProdCal.Hour159 as INT) +
 CAST(ProdCal.Hour160 as INT) +
 CAST(ProdCal.Hour161 as INT) +
 CAST(ProdCal.Hour162 as INT) +
 CAST(ProdCal.Hour163 as INT) +
 CAST(ProdCal.Hour164 as INT) +
 CAST(ProdCal.Hour165 as INT) +
 CAST(ProdCal.Hour166 as INT) +
 CAST(ProdCal.Hour167 as INT) +
 CAST(ProdCal.Hour168 as INT)) as [Calculated_HoursPerWeek],
	(CAST(ProdCal.Hour001 as INT) +
 CAST(ProdCal.Hour002 as INT) +
 CAST(ProdCal.Hour003 as INT) +
 CAST(ProdCal.Hour004 as INT) +
 CAST(ProdCal.Hour005 as INT) +
 CAST(ProdCal.Hour006 as INT) +
 CAST(ProdCal.Hour007 as INT) +
 CAST(ProdCal.Hour008 as INT) +
 CAST(ProdCal.Hour009 as INT) +
 CAST(ProdCal.Hour010 as INT) +
 CAST(ProdCal.Hour011 as INT) +
 CAST(ProdCal.Hour012 as INT) +
 CAST(ProdCal.Hour013 as INT) +
 CAST(ProdCal.Hour014 as INT) +
 CAST(ProdCal.Hour015 as INT) +
 CAST(ProdCal.Hour016 as INT) +
 CAST(ProdCal.Hour017 as INT) +
 CAST(ProdCal.Hour018 as INT) +
 CAST(ProdCal.Hour019 as INT) +
 CAST(ProdCal.Hour020 as INT) +
 CAST(ProdCal.Hour021 as INT) +
 CAST(ProdCal.Hour022 as INT) +
 CAST(ProdCal.Hour023 as INT) +
 CAST(ProdCal.Hour024 as INT)) as [Calculated_HoursSun],
	(CAST(ProdCal.Hour025 as INT) +
 CAST(ProdCal.Hour026 as INT) +
 CAST(ProdCal.Hour027 as INT) +
 CAST(ProdCal.Hour028 as INT) +
 CAST(ProdCal.Hour029 as INT) +
 CAST(ProdCal.Hour030 as INT) +
 CAST(ProdCal.Hour031 as INT) +
 CAST(ProdCal.Hour032 as INT) +
 CAST(ProdCal.Hour033 as INT) +
 CAST(ProdCal.Hour034 as INT) +
 CAST(ProdCal.Hour035 as INT) +
 CAST(ProdCal.Hour036 as INT) +
 CAST(ProdCal.Hour037 as INT) +
 CAST(ProdCal.Hour038 as INT) +
 CAST(ProdCal.Hour039 as INT) +
 CAST(ProdCal.Hour040 as INT) +
 CAST(ProdCal.Hour041 as INT) +
 CAST(ProdCal.Hour042 as INT) +
 CAST(ProdCal.Hour043 as INT) +
 CAST(ProdCal.Hour044 as INT) +
 CAST(ProdCal.Hour045 as INT) +
 CAST(ProdCal.Hour046 as INT) +
 CAST(ProdCal.Hour047 as INT) +
 CAST(ProdCal.Hour048 as INT)) as [Calculated_HoursMon],
	(CAST(ProdCal.Hour049 as INT) +
 CAST(ProdCal.Hour050 as INT) +
 CAST(ProdCal.Hour051 as INT) +
 CAST(ProdCal.Hour052 as INT) +
 CAST(ProdCal.Hour053 as INT) +
 CAST(ProdCal.Hour054 as INT) +
 CAST(ProdCal.Hour055 as INT) +
 CAST(ProdCal.Hour056 as INT) +
 CAST(ProdCal.Hour057 as INT) +
 CAST(ProdCal.Hour058 as INT) +
 CAST(ProdCal.Hour059 as INT) +
 CAST(ProdCal.Hour060 as INT) +
 CAST(ProdCal.Hour061 as INT) +
 CAST(ProdCal.Hour062 as INT) +
 CAST(ProdCal.Hour063 as INT) +
 CAST(ProdCal.Hour064 as INT) +
 CAST(ProdCal.Hour065 as INT) +
 CAST(ProdCal.Hour066 as INT) +
 CAST(ProdCal.Hour067 as INT) +
 CAST(ProdCal.Hour068 as INT) +
 CAST(ProdCal.Hour069 as INT) +
 CAST(ProdCal.Hour070 as INT) +
 CAST(ProdCal.Hour071 as INT) +
 CAST(ProdCal.Hour072 as INT)) as [Calculated_HoursTues],
	(CAST(ProdCal.Hour073 as INT) +
 CAST(ProdCal.Hour074 as INT) +
 CAST(ProdCal.Hour075 as INT) +
 CAST(ProdCal.Hour076 as INT) +
 CAST(ProdCal.Hour077 as INT) +
 CAST(ProdCal.Hour078 as INT) +
 CAST(ProdCal.Hour079 as INT) +
 CAST(ProdCal.Hour080 as INT) +
 CAST(ProdCal.Hour081 as INT) +
 CAST(ProdCal.Hour082 as INT) +
 CAST(ProdCal.Hour083 as INT) +
 CAST(ProdCal.Hour084 as INT) +
 CAST(ProdCal.Hour085 as INT) +
 CAST(ProdCal.Hour086 as INT) +
 CAST(ProdCal.Hour087 as INT) +
 CAST(ProdCal.Hour088 as INT) +
 CAST(ProdCal.Hour089 as INT) +
 CAST(ProdCal.Hour090 as INT) +
 CAST(ProdCal.Hour091 as INT) +
 CAST(ProdCal.Hour092 as INT) +
 CAST(ProdCal.Hour093 as INT) +
 CAST(ProdCal.Hour094 as INT) +
 CAST(ProdCal.Hour095 as INT) +
 CAST(ProdCal.Hour096 as INT)) as [Calculated_HoursWed],
	(CAST(ProdCal.Hour097 as INT) +
 CAST(ProdCal.Hour098 as INT) +
 CAST(ProdCal.Hour099 as INT) +
 CAST(ProdCal.Hour100 as INT) +
 CAST(ProdCal.Hour101 as INT) +
 CAST(ProdCal.Hour102 as INT) +
 CAST(ProdCal.Hour103 as INT) +
 CAST(ProdCal.Hour104 as INT) +
 CAST(ProdCal.Hour105 as INT) +
 CAST(ProdCal.Hour106 as INT) +
 CAST(ProdCal.Hour107 as INT) +
 CAST(ProdCal.Hour108 as INT) +
 CAST(ProdCal.Hour109 as INT) +
 CAST(ProdCal.Hour110 as INT) +
 CAST(ProdCal.Hour111 as INT) +
 CAST(ProdCal.Hour112 as INT) +
 CAST(ProdCal.Hour113 as INT) +
 CAST(ProdCal.Hour114 as INT) +
 CAST(ProdCal.Hour115 as INT) +
 CAST(ProdCal.Hour116 as INT) +
 CAST(ProdCal.Hour117 as INT) +
 CAST(ProdCal.Hour118 as INT) +
 CAST(ProdCal.Hour119 as INT) +
 CAST(ProdCal.Hour120 as INT)) as [Calculated_HoursThurs],
	(CAST(ProdCal.Hour121 as INT) +
 CAST(ProdCal.Hour122 as INT) +
 CAST(ProdCal.Hour123 as INT) +
 CAST(ProdCal.Hour124 as INT) +
 CAST(ProdCal.Hour125 as INT) +
 CAST(ProdCal.Hour126 as INT) +
 CAST(ProdCal.Hour127 as INT) +
 CAST(ProdCal.Hour128 as INT) +
 CAST(ProdCal.Hour129 as INT) +
 CAST(ProdCal.Hour130 as INT) +
 CAST(ProdCal.Hour131 as INT) +
 CAST(ProdCal.Hour132 as INT) +
 CAST(ProdCal.Hour133 as INT) +
 CAST(ProdCal.Hour134 as INT) +
 CAST(ProdCal.Hour135 as INT) +
 CAST(ProdCal.Hour136 as INT) +
 CAST(ProdCal.Hour137 as INT) +
 CAST(ProdCal.Hour138 as INT) +
 CAST(ProdCal.Hour139 as INT) +
 CAST(ProdCal.Hour140 as INT) +
 CAST(ProdCal.Hour141 as INT) +
 CAST(ProdCal.Hour142 as INT) +
 CAST(ProdCal.Hour143 as INT) +
 CAST(ProdCal.Hour144 as INT)) as [Calculated_HoursFri],
	(CAST(ProdCal.Hour145 as INT) +
 CAST(ProdCal.Hour146 as INT) +
 CAST(ProdCal.Hour147 as INT) +
 CAST(ProdCal.Hour148 as INT) +
 CAST(ProdCal.Hour149 as INT) +
 CAST(ProdCal.Hour150 as INT) +
 CAST(ProdCal.Hour151 as INT) +
 CAST(ProdCal.Hour152 as INT) +
 CAST(ProdCal.Hour153 as INT) +
 CAST(ProdCal.Hour154 as INT) +
 CAST(ProdCal.Hour155 as INT) +
 CAST(ProdCal.Hour156 as INT) +
 CAST(ProdCal.Hour157 as INT) +
 CAST(ProdCal.Hour158 as INT) +
 CAST(ProdCal.Hour159 as INT) +
 CAST(ProdCal.Hour160 as INT) +
 CAST(ProdCal.Hour161 as INT) +
 CAST(ProdCal.Hour162 as INT) +
 CAST(ProdCal.Hour163 as INT) +
 CAST(ProdCal.Hour164 as INT) +
 CAST(ProdCal.Hour165 as INT) +
 CAST(ProdCal.Hour166 as INT) +
 CAST(ProdCal.Hour167 as INT) +
 CAST(ProdCal.Hour168 as INT)) as [Calculated_HoursSat]
from Erp.ProdCal as ProdCal

This one also works as long as all RG’s have an assigned Calendar, I switched to the above instead of modifying this one more.

select distinct
	[ResourceGroup].[JCDept] as [ResourceGroup_JCDept],
	[ResourceGroup].[ResourceGrpID] as [ResourceGroup_ResourceGrpID],
	[ResourceGroup].[Description] as [ResourceGroup_Description],
	(CASE WHEN (ResourceGroup.CalendarID) is null then 9999
 ELSE
   (CASE WHEN TotalResouces = 0 THEN 
     (CASE WHEN ActRes = 0 
     THEN (ResourceGroup.DailyCapacity1 + ResourceGroup.DailyCapacity2 + ResourceGroup.DailyCapacity3 + ResourceGroup.DailyCapacity4 + ResourceGroup.DailyCapacity5 + ResourceGroup.DailyCapacity6 + ResourceGroup.DailyCapacity7)
       
       ELSE ((ResourceGroup.DailyCapacity1 + ResourceGroup.DailyCapacity2 + ResourceGroup.DailyCapacity3 + ResourceGroup.DailyCapacity4 + ResourceGroup.DailyCapacity5 + ResourceGroup.DailyCapacity6 + ResourceGroup.DailyCapacity7) / TotalResouces ) * ActRes
     END)
   ELSE 
     (CASE WHEN ActRes = 0 THEN 0
       ELSE ((ResourceGroup.DailyCapacity1 + ResourceGroup.DailyCapacity2 + ResourceGroup.DailyCapacity3 + ResourceGroup.DailyCapacity4 + ResourceGroup.DailyCapacity5 + ResourceGroup.DailyCapacity6 + ResourceGroup.DailyCapacity7) / TotalResouces ) * ActRes
     END)
   END)
  END) as [Calculated_WeeklyCapacity],
	[ResourceGroup].[CalendarID] as [ResourceGroup_CalendarID],
	(CASE WHEN (Resource1.Calculated_ActiveResources) is null 
   THEN 0
   ELSE Resource1.Calculated_ActiveResources
 END) as [Calculated_ActRes],
	(CASE WHEN (TotResources.Calculated_TotalRes) is null 
   THEN 0
   ELSE TotResources.Calculated_TotalRes
 END) as [Calculated_TotalResouces],
	[ResourceGroup].[DailyCapacity1] as [ResourceGroup_DailyCapacity1],
	[ResourceGroup].[DailyCapacity2] as [ResourceGroup_DailyCapacity2],
	[ResourceGroup].[DailyCapacity3] as [ResourceGroup_DailyCapacity3],
	[ResourceGroup].[DailyCapacity4] as [ResourceGroup_DailyCapacity4],
	[ResourceGroup].[DailyCapacity5] as [ResourceGroup_DailyCapacity5],
	[ResourceGroup].[DailyCapacity6] as [ResourceGroup_DailyCapacity6],
	[ResourceGroup].[DailyCapacity7] as [ResourceGroup_DailyCapacity7],
	[ResourceGroup].[Inactive] as [ResourceGroup_Inactive],
	[JCDept].[Description] as [JCDept_Description]
from Erp.ResourceGroup as ResourceGroup
left outer join  (select distinct
	[Resource].[Company] as [Resource_Company],
	[Resource].[ResourceGrpID] as [Resource_ResourceGrpID],
	[Resource].[ResourceID] as [Resource_ResourceID],
	[Resource].[Inactive] as [Resource_Inactive],
	(count(Resource.ResourceID) OVER (Partition By Resource.Company, Resource.ResourceGrpID)) as [Calculated_ActiveResources]
from Erp.Resource as Resource
where (Resource.Inactive = 0))  as Resource1 on 
	ResourceGroup.Company = Resource1.Resource_Company
	and ResourceGroup.ResourceGrpID = Resource1.Resource_ResourceGrpID
left outer join  (select 
	[Resource2].[Company] as [Resource2_Company],
	[Resource2].[ResourceGrpID] as [Resource2_ResourceGrpID],
	[Resource2].[ResourceID] as [Resource2_ResourceID],
	(count(Resource2.ResourceID) OVER (Partition By Resource2.Company, Resource2.ResourceGrpID)) as [Calculated_TotalRes]
from Erp.Resource as Resource2)  as TotResources on 
	ResourceGroup.Company = TotResources.Resource2_Company
	and ResourceGroup.ResourceGrpID = TotResources.Resource2_ResourceGrpID
inner join Erp.JCDept as JCDept on 
	ResourceGroup.Company = JCDept.Company
	and ResourceGroup.JCDept = JCDept.JCDept
2 Likes

Putting this here for future @Randy to find someday when it’s needed again.

Calculating Load

(CASE WHEN JobOper.SetupComplete = 0 
    THEN JobOper.EstSetHours 
    ELSE 0 
  END)
+ 
  (( CASE WHEN JobOper.QtyCompleted > 0 AND JobOper.RunQty > 0 
    THEN (CASE WHEN JobOper.QtyCompleted > JobOper.RunQty 
            THEN 0
            ELSE JobOper.EstProdHours - (JobOper.QtyCompleted / JobOper.RunQty) * JobOper.EstProdHours
          END)
    ELSE JobOper.EstProdHours
  END))
2 Likes

Thank you Randy!

1 Like