Forum Discussion
Data selection formula
IIF(
ROUND ((PV_JobLine.OrderedQty - PV_JobLine.ReceivedQty - PV_JobLine.ReturnQty),0) < 0,
0,
ROUND ((PV_JobLine.OrderedQty - PV_JobLine.ReceivedQty - PV_JobLine.ReturnQty),0)
)AS Shortfall,
ROUND(
IIF(
(PV_JobLine.OrderedQty - PV_JobLine.ReceivedQty - PV_JobLine.ReturnQty) < 0,
0,
PV_JobLine.OrderedQty - PV_JobLine.ReceivedQty - PV_JobLine.ReturnQty
)/PV_JobLine.OrderedQty, 2
)AS pOFshortfall --to remain precision, not rounded when calculating "Shortfall", and PV_JobLine.OrderedQty cannot be 0, or you have to use another IIF
Thanks for that, but I think I must have done something wrong
This is what I typed in
SELECT
PV_Job.JobCode,
PV_Customer.CustName,
PV_JobLine.ItemCode,
PM_Item.ItemShortDesc,
PV_JobLine.OrderedQty,
(PV_JobLine.ReceivedQty - PV_JobLine.ReturnQty) AS FGQty,
IIF(
(PV_JobLine.OrderedQty - PV_JobLine.ReceivedQty - PV_JobLine.ReturnQty) < 0,
0,
(PV_JobLine.OrderedQty - PV_JobLine.ReceivedQty - PV_JobLine.ReturnQty)
)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)When I test the query I got this error - an ideas?
Can you run the script in SSMS and check the result?
- Sorry, I'm not sure what you mean
I'm fairly new to this & using an editor I've been provided with
