Link to home
Start Free TrialLog in
Avatar of HemlockPrinters
HemlockPrinters

asked on

modify existing procedure to get correct Price

The column PriceQTy from the following existing procedure will get  the price that matches Quan.
for example in image 1,
quan = 5000,  which matches quan1, then PriceQTy is price1 which is 35.

if Quan = 12000, which doesn't match quan1,quan2,...quan5 then PriceQty is NULL

I need to alter this procedure, if Quan doesn't match quan1, quan2..quan5, instead of showing Null,  it should   pick closer number from quan1, quan2...quan5.  
now because Quan = 12000, the closer number is quan2 = 6000, so PriceQty should be pricelab2, which is 55 and 38.

please help


ALTER PROCEDURE [dbo].[xx_est_estdetail_ship]
      @Ljob int

AS

SELECT
      aaproces.process,
      bbjthead.ljob,
      bbjthead.quan,
      bbjthead.estno,
      aajthead.quan1,
      aajthead.quan2,
      aajthead.quan3,
      aajthead.quan4,
      aajthead.quan5,
      sumqt.pricelab1,
      sumqt.pricelab2,
      sumqt.pricelab3,
      sumqt.pricelab4,
      sumqt.pricelab5,
      case     when bbjthead.quan =  aajthead.quan1 then sumqt.pricelab1
               when bbjthead.quan =  aajthead.quan2 then sumqt.pricelab2
               when bbjthead.quan =  aajthead.quan3 then sumqt.pricelab3
               when bbjthead.quan =  aajthead.quan4 then sumqt.pricelab4
               when bbjthead.quan =  aajthead.quan5 then sumqt.pricelab5
               Else null end PriceQty
FROM aaproces
      JOIN aajthead ON aaproces.estno = aajthead.estno
      JOIN bbjthead ON aaproces.estno = bbjthead.estno  
      JOIN (SELECT      aaprocqt.estno,
                  aaprocqt.partno,
                  aaprocqt.procgroup,
                  aaprocqt.[lineno],
                  aaprocqt.counter,
                  SUM(CASE aaprocqt.Qty WHEN 1 THEN pricelab ELSE 0 END) AS pricelab1,
                  SUM(CASE aaprocqt.Qty WHEN 2 THEN pricelab ELSE 0 END) AS pricelab2,
                  SUM(CASE aaprocqt.Qty WHEN 3 THEN pricelab ELSE 0 END) AS pricelab3,
                  SUM(CASE aaprocqt.Qty WHEN 4 THEN pricelab ELSE 0 END) AS pricelab4,
                  SUM(CASE aaprocqt.Qty WHEN 5 THEN pricelab ELSE 0 END) AS pricelab5
            FROM AAPROCQT
            INNER JOIN bbjthead ON aaprocqt.estno =bbjthead.estno AND bbjthead.ljob = @Ljob
            WHERE aaprocqt.procgroup = '8'
             --and  bbjthead.ljob = @tcLjob
                   GROUP BY       aaprocqt.estno,
                        aaprocqt.partno,
                        aaprocqt.procgroup,
                        aaprocqt.[lineno],
                        aaprocqt.counter
            ) sumqt ON aaproces.estno = sumqt.estno
                  AND aaproces.partno = sumqt.partno
                  AND aaproces.procgroup = sumqt.procgroup
                  AND aaproces.[lineno] = sumqt.[lineno]
                  AND aaproces.counter = sumqt.counter
WHERE aaproces.estno = bbjthead.estno
      AND aaproces.procgroup = '8'  and bbjthead.ljob = @Ljob
1.bmp
untitled.bmp
ASKER CERTIFIED SOLUTION
Avatar of Ioannis Paraskevopoulos
Ioannis Paraskevopoulos
Flag of Greece image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
An alternate CASE form would be to start at Quan5 and work backwards.
CASE
    WHEN when bbjthead.quan >=  aajthead.quan5 AND aajthead.quan5 != 0
        THEN PriceLab5
    WHEN when bbjthead.quan >=  aajthead.quan4 AND aajthead.quan4 != 0
        THEN PriceLab4
    WHEN when bbjthead.quan >=  aajthead.quan3 AND aajthead.quan3 != 0
        THEN PriceLab3
    WHEN when bbjthead.quan >=  aajthead.quan2 AND aajthead.quan2 != 0
        THEN PriceLab2
    WHEN when bbjthead.quan > 0
        THEN PriceLab1
    ELSE 0 -- Error, no bbhthead.quan value
Hi,

If i may comment on the code above, though i like the working backwards style, it won't cover the request that if the requested quantity is between two defined quantities, it should get the quantity of the nearest value.

Filling this request made my code looking this awfully ugly... :)

Giannis