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
Microsoft SQL Server 2005

Avatar of undefined
Last Comment
Ioannis Paraskevopoulos

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Ioannis Paraskevopoulos

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Brendt Hess

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
Ioannis Paraskevopoulos

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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23