Forum Discussion

HelenH2300's avatar
HelenH2300
Copper Contributor
Oct 14, 2024

Data selection formula

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

7 Replies

  • rodgerkong's avatar
    rodgerkong
    Iron Contributor
    Oct 14, 2024

    HelenH2300 

     

    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

     

    • HelenH2300's avatar
      HelenH2300
      Copper Contributor
      Oct 14, 2024

      rodgerkong 

       

      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?

       

Resources