I am trying to write an SQL query to select / change a value based on a formula

Below is the query I have written so far –

SELECT
PV_Job.JobCode,
PV_Customer.CustName,
PV_JobLine.ItemCode,
PM_Item.ItemShortDesc,
PV_JobLine.OrderedQty,
ROUND ((PV_JobLine.ReceivedQty – PV_JobLine.ReturnQty),0) AS FGQty,
ROUND ((PV_JobLine.OrderedQty – PV_JobLine.ReceivedQty – PV_JobLine.ReturnQty),0) AS Shortfall,
PV_Job.ProductionStatus,
PV_Job.ProdCompDate,
PV_Job.JobCreateDate,
CASE PV_Job.ProductionStatus
WHEN 0 THEN ”
WHEN 1 THEN ‘Unplanned’
WHEN 4 THEN ‘Planned’
WHEN 8 THEN ‘Started’
WHEN 9 THEN ‘Completed’
WHEN 99 THEN ‘PSA Completed’
END as ‘ProdStatus’
FROM PV_Job (NOLOCK)
INNER JOIN PV_JobLine (NOLOCK) ON
((PV_Job.CompNum=PV_JobLine.CompNum) AND
(PV_Job.PlantCode=PV_JobLine.PlantCode)) AND
(PV_Job.JobCode=PV_JobLine.JobCode)
LEFT OUTER JOIN PV_Customer (NOLOCK) ON
(PV_Job.CompNum=PV_Customer.CompNum) AND
(PV_Job.CustCode=PV_Customer.CustCode)
LEFT OUTER JOIN PM_Item (NOLOCK) ON
(PV_JobLine.CompNum=PM_Item.CompNum) AND
(PV_JobLine.ItemCode=PM_Item.ItemCode)

I have created a “Shortfall” figure, but want this to display as 0 if it is negative

I then want to show the Shortfall as  percentage of the PV_JobLine.OrderedQty

Regards, Helen

​I am trying to write an SQL query to select / change a value based on a formulaBelow is the query I have written so far -SELECTPV_Job.JobCode,PV_Customer.CustName,PV_JobLine.ItemCode,PM_Item.ItemShortDesc,PV_JobLine.OrderedQty,ROUND ((PV_JobLine.ReceivedQty – PV_JobLine.ReturnQty),0) AS FGQty,ROUND ((PV_JobLine.OrderedQty – PV_JobLine.ReceivedQty – PV_JobLine.ReturnQty),0) AS Shortfall,PV_Job.ProductionStatus,PV_Job.ProdCompDate,PV_Job.JobCreateDate,CASE PV_Job.ProductionStatusWHEN 0 THEN ”WHEN 1 THEN ‘Unplanned’WHEN 4 THEN ‘Planned’WHEN 8 THEN ‘Started’WHEN 9 THEN ‘Completed’WHEN 99 THEN ‘PSA Completed’END as ‘ProdStatus’FROM PV_Job (NOLOCK)INNER JOIN PV_JobLine (NOLOCK) ON((PV_Job.CompNum=PV_JobLine.CompNum) AND(PV_Job.PlantCode=PV_JobLine.PlantCode)) AND(PV_Job.JobCode=PV_JobLine.JobCode)LEFT OUTER JOIN PV_Customer (NOLOCK) ON(PV_Job.CompNum=PV_Customer.CompNum) AND(PV_Job.CustCode=PV_Customer.CustCode)LEFT OUTER JOIN PM_Item (NOLOCK) ON(PV_JobLine.CompNum=PM_Item.CompNum) AND(PV_JobLine.ItemCode=PM_Item.ItemCode) I have created a “Shortfall” figure, but want this to display as 0 if it is negativeI then want to show the Shortfall as  percentage of the PV_JobLine.OrderedQty Regards, Helen  Read More

​