troubleshooting Question

modify existing procedure to get correct Price

Avatar of HemlockPrinters
HemlockPrinters asked on
Microsoft SQL Server 2005
3 Comments1 Solution305 ViewsLast Modified:
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
Ioannis Paraskevopoulos

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros