SQL Help

My predecessor wrote somethings up to view via Chrome to bypass Epicor and on one of them it is giving us an odd number. So I tracked down the part that is doing this and wanted a little help on this. Its nested and I am wanting to make sure I am looking at this correctly. If Ia m looking at this correctly the GetDate() is the date that is selected. Overall to me, this is talking in circles. The issue is its giving me a negative number and it shouldn’t. This is a piece to a much larger whole. If anyone can help and wants to see the whole, I will post it.

cast((avg(Load.Calculated_Load)) as decimal(9,2)) - case when (cast((avg(Load.Calculated_Load)) as decimal(9,2)) > case when [ShopCap].[LoadDate] = convert

(date,getdate()) then 0 else (sum(ShopCap.Capacity)) end) then (cast((avg(Load.Calculated_Load)) as decimal(9,2)) -

case when [ShopCap].[LoadDate] = convert(date,getdate()) then 0 else (sum(ShopCap.Capacity)) end) else 0 end as [LoadHours]

So, here’s how I’m reading this – I’m writing this down in pseudocode to hopefully make it a bit easier to read:

if (ShopCap.LoadDate is Today) then capacity = 0 else capacity = sum(ShopCap.Capacity) end
if (average(Load.Calculated_Load) > capacity) then load_calc = average(Load.Calculated_Load) - capacity else load_calc = 0 end
return average(Load.Calculated_Load) - load_calc

If the Load Date is Today (when the script is run), then the answer from this formula is always 0.

EDIT TO ADD:
If avg(Load.Calculated_Load) is negative and the Load Date is today, it will return avg(Load.Calculated_Load), not zero.

Otherwise, it becomes:

if (avg(Load.Calculated_Load) > sum(ShopCap.Capacity)) then sum(ShopCap.Capacity) else avg(Load.Calculated_Load) end

which is just the minimum value between the average Load and the sum of Capacity…

I feel like I either missed something or this is a very roundabout way to get the minimum of two values. That also means the only way this would produce a negative is if either avg(Load.Calculated_Load) is negative or sum(ShopCap.Capacity) is negative.

2 Likes

@tsmith Well, here is the whole code, maybe this will make more sense to you.

    <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource2" DataTextField="Description" DataValueField="ResourceGrpID">
    </asp:DropDownList>
    <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:XXXXXX %>" SelectCommand="select ResourceGrpID, Description

FROM ERP.ResourceGroup
WHERE ResourceGrpID<>‘DIES’ and ResourceGrpID<>‘DIG DIES’ and ResourceGrpID<>‘PICKERS’ and Inactive<>1
ORDER BY Description"></asp:SqlDataSource>


<asp:Chart ID=“Chart1” runat=“server” DataSourceID=“SqlDataSource1” OnLoad=“Chart1_Load” Height=“900px” Width=“1500px”>

<asp:Series BorderColor=“Green” ChartType=“StackedBar” Color=“Green” Name=“Series1” XValueMember=“Date” YValueMembers=“LoadHours” Font=“Microsoft Sans Serif, 12pt, style=Bold” IsValueShownAsLabel=“True” LabelForeColor=“White” LabelFormat="#.##">
</asp:Series>
<asp:Series BorderColor=“Green” ChartArea=“ChartArea1” ChartType=“StackedBar” Color=“White” Name=“Series2” XValueMember=“Date” YValueMembers=“AvailableHours” Font=“Microsoft Sans Serif, 12pt, style=Bold” IsValueShownAsLabel=“True” LabelForeColor=“Green” LabelFormat="#.##">
</asp:Series>
<asp:Series BorderColor=“Red” ChartArea=“ChartArea1” ChartType=“StackedBar” Color=“Red” Name=“Series3” XValueMember=“Date” YValueMembers=“OverloadHours” Font=“Microsoft Sans Serif, 12pt, style=Bold” IsValueShownAsLabel=“True” LabelForeColor=“White” LabelFormat="#.##">
</asp:Series>


<asp:ChartArea Name=“ChartArea1”>





</asp:ChartArea>

</asp:Chart>
<asp:SqlDataSource ID=“SqlDataSource1” runat=“server” ConnectionString="<%$ ConnectionStrings:ERP10ConnectionString %>" SelectCommand="select
[ShopCap].[ResourceGrpID] as [ResourceGrpID],
convert(nvarchar(20),[ShopCap].[LoadDate]) as [Date],
case when [ShopCap].[LoadDate] = convert(date,getdate()) then 0 else (sum(ShopCap.Capacity)) end as

[Capacity],
cast((avg(Load.Calculated_Load)) as decimal(9,2)) - case when (cast((avg(Load.Calculated_Load)) as decimal(9,2)) > case when [ShopCap].[LoadDate] = convert

(date,getdate()) then 0 else (sum(ShopCap.Capacity)) end) then (cast((avg(Load.Calculated_Load)) as decimal(9,2)) -

case when [ShopCap].[LoadDate] = convert(date,getdate()) then 0 else (sum(ShopCap.Capacity)) end) else 0 end as [LoadHours],
case when (cast((avg(Load.Calculated_Load)) as decimal(9,2)) > case when [ShopCap].[LoadDate] = convert

(date,getdate()) then 0 else (sum(ShopCap.Capacity)) end) then 0 else ((case when [ShopCap].[LoadDate] = convert

(date,getdate()) then 0 else (sum(ShopCap.Capacity)) end) - cast((avg(Load.Calculated_Load)) as decimal(9,2))) end

as [AvailableHours],
case when (cast((avg(Load.Calculated_Load)) as decimal(9,2)) > case when [ShopCap].[LoadDate] = convert

(date,getdate()) then 0 else (sum(ShopCap.Capacity)) end) then (cast((avg(Load.Calculated_Load)) as decimal(9,2)) -

case when [ShopCap].[LoadDate] = convert(date,getdate()) then 0 else (sum(ShopCap.Capacity)) end) else 0 end as

[OverloadHours]
from Erp.ShopCap as ShopCap
inner join Erp.Resource as Resource on
ShopCap.Company = Resource.Company
And
ShopCap.ResourceGrpID = Resource.ResourceGrpID
And
ShopCap.ResourceID = Resource.ResourceID
and ( Resource.Inactive = 0 )

left outer join (select
[JobOpDtl].[ResourceGrpID] as [JobOpDtl_ResourceGrpID],
(case when JobHead.ReqDueDate <= getdate() then convert(date, getdate()) else JobHead.ReqDueDate end) as

[Calculated_LoadDate],
(sum(((case when JobOper.SetupComplete = 0 then JobOper.EstSetHours else 0 end))+((case when

JobOper.QtyCompleted > 0 and JobOper.RunQty > 0 then JobOper.EstProdHours - (JobOper.QtyCompleted/JobOper.RunQty)

*JobOper.EstProdHours else JobOper.EstProdHours end)))) as [Calculated_Load]
from Erp.JobOpDtl as JobOpDtl
inner join Erp.JobOper as JobOper on
JobOpDtl.Company = JobOper.Company
And
JobOpDtl.JobNum = JobOper.JobNum
And
JobOpDtl.AssemblySeq = JobOper.AssemblySeq
And
JobOpDtl.OprSeq = JobOper.OprSeq
and ( JobOper.OpComplete = 0 )

inner join Erp.JobHead as JobHead on
JobOper.Company = JobHead.Company
And
JobOper.JobNum = JobHead.JobNum
and ( JobHead.JobReleased = 1 and JobHead.JobComplete = 0 )

where (JobOpDtl.ResourceGrpID = @Grp)
group by [JobOpDtl].[ResourceGrpID],
(case when JobHead.ReqDueDate <= getdate() then convert(date, getdate()) else JobHead.ReqDueDate end)) as

Load on
ShopCap.ResourceGrpID = Load.JobOpDtl_ResourceGrpID
And
ShopCap.LoadDate = Load.Calculated_LoadDate

where (ShopCap.ResourceGrpID = @Grp and ShopCap.ResourceID <> ‘’ and ShopCap.LoadDate >= convert(date, GETDATE

()) and ShopCap.LoadDate < DATEADD (week, 2, convert(date,GETDATE())))
group by [ShopCap].[ResourceGrpID],
[ShopCap].[LoadDate]
order by ShopCap.LoadDate ASC">

<asp:ControlParameter ControlID=“DropDownList1” DefaultValue="%" Name=“Grp” PropertyName=“SelectedValue” />

</asp:SqlDataSource>

And you were getting a negative value in the LoadHours column?

Yes.

Here’s the graphic that shows it.

Check to see if, on the day you’re getting a negative, there is a job that has more quantity completed than the run quantity:

(sum(((CASE WHEN JobOper.SetupComplete = 0
			THEN JobOper.EstSetHours
			ELSE 0 END)) + 
         ((CASE WHEN JobOper.QtyCompleted > 0 AND JobOper.RunQty > 0
			THEN JobOper.EstProdHours - (JobOper.QtyCompleted / JobOper.RunQty) * JobOper.EstProdHours
			ELSE JobOper.EstProdHours	END)))) AS [Calculated_Load]

If JobOper.QtyCompleted > JobOper.RunQty, then Calculated_Load will be negative. For example: JobOper.QtyCompleted = 1500, JobOper.RunQty = 1000, and JobOper.EstProdHours = 5 gives

Calculated_Load = 5 - ((1500 / 1000) * 5) = 5 - 7.5 = -2.5

You can fix this by checking if QtyCompleted > RunQty:

(sum(((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)))) AS [Calculated_Load]
1 Like

Here’s my final code with the tweaks. It looks reasonable against my data, but I would double check against your own dataset to see what you get.

Click here to open the full SQL query
SELECT [ShopCap].[ResourceGrpID] AS [ResourceGrpID]
	,convert(NVARCHAR(20), [ShopCap].[LoadDate]) AS [Date]
	,CASE 
		WHEN [ShopCap].[LoadDate] = convert(DATE, getdate())
			THEN 0
		ELSE (sum(ShopCap.Capacity))
		END AS [Capacity]
	,CASE 
		WHEN (
				avg(LOAD.Calculated_Load) > CASE 
					WHEN ShopCap.LoadDate = convert(DATE, getdate())
						THEN 0
					ELSE sum(ShopCap.Capacity)
					END
				)
			THEN CASE 
					WHEN ShopCap.LoadDate = convert(DATE, getdate())
						THEN 0
					ELSE sum(ShopCap.Capacity)
					END
		ELSE avg(LOAD.Calculated_Load)
		END AS [LoadHours]
	,(
		CASE 
			WHEN [ShopCap].[LoadDate] = convert(DATE, getdate())
				THEN 0
			ELSE (sum(ShopCap.Capacity))
			END
		) - (
		CASE 
			WHEN (
					avg(LOAD.Calculated_Load) > CASE 
						WHEN ShopCap.LoadDate = convert(DATE, getdate())
							THEN 0
						ELSE sum(ShopCap.Capacity)
						END
					)
				THEN CASE 
						WHEN ShopCap.LoadDate = convert(DATE, getdate())
							THEN 0
						ELSE sum(ShopCap.Capacity)
						END
			ELSE avg(LOAD.Calculated_Load)
			END
		) AS [AvailableHours]
	,CASE 
		WHEN (
				cast((avg(LOAD.Calculated_Load)) AS DECIMAL(9, 2)) > CASE 
					WHEN [ShopCap].[LoadDate] = convert(DATE, getdate())
						THEN 0
					ELSE (sum(ShopCap.Capacity))
					END
				)
			THEN (
					cast((avg(LOAD.Calculated_Load)) AS DECIMAL(9, 2)) - CASE 
						WHEN [ShopCap].[LoadDate] = convert(DATE, getdate())
							THEN 0
						ELSE (sum(ShopCap.Capacity))
						END
					)
		ELSE 0
		END AS [OverloadHours]
FROM Erp.ShopCap AS ShopCap
INNER JOIN Erp.Resource AS Resource ON ShopCap.Company = Resource.Company
	AND ShopCap.ResourceGrpID = Resource.ResourceGrpID
	AND ShopCap.ResourceID = Resource.ResourceID
	AND (Resource.Inactive = 0)
LEFT OUTER JOIN (
	SELECT [JobOpDtl].[ResourceGrpID] AS [JobOpDtl_ResourceGrpID]
		,(
			CASE 
				WHEN JobHead.ReqDueDate <= getdate()
					THEN convert(DATE, getdate())
				ELSE JobHead.ReqDueDate
				END
			) AS [Calculated_LoadDate]
		,(
			sum((
					(
						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
						)
					))
			) AS [Calculated_Load]
	FROM Erp.JobOpDtl AS JobOpDtl
	INNER JOIN Erp.JobOper AS JobOper ON JobOpDtl.Company = JobOper.Company
		AND JobOpDtl.JobNum = JobOper.JobNum
		AND JobOpDtl.AssemblySeq = JobOper.AssemblySeq
		AND JobOpDtl.OprSeq = JobOper.OprSeq
		AND (JobOper.OpComplete = 0)
	INNER JOIN Erp.JobHead AS JobHead ON JobOper.Company = JobHead.Company
		AND JobOper.JobNum = JobHead.JobNum
		AND (
			JobHead.JobReleased = 1
			AND JobHead.JobComplete = 0
			)
	WHERE (JobOpDtl.ResourceGrpID = @Grp)
	GROUP BY [JobOpDtl].[ResourceGrpID]
		,(
			CASE 
				WHEN JobHead.ReqDueDate <= getdate()
					THEN convert(DATE, getdate())
				ELSE JobHead.ReqDueDate
				END
			)
	) AS LOAD ON ShopCap.ResourceGrpID = LOAD.JobOpDtl_ResourceGrpID
	AND ShopCap.LoadDate = LOAD.Calculated_LoadDate
WHERE (
		ShopCap.ResourceGrpID = @Grp
		AND ShopCap.ResourceID <> ''
		AND ShopCap.LoadDate >= convert(DATE, GETDATE())
		AND ShopCap.LoadDate < DATEADD(week, 2, convert(DATE, GETDATE()))
		)
GROUP BY [ShopCap].[ResourceGrpID]
	,[ShopCap].[LoadDate]
ORDER BY ShopCap.LoadDate ASC

2 Likes

How did you put the code in that drop down like that? That way I can keep things cleaner next time.

Click the Gear, then Hide Details.

1 Like