CODEWITHSUNDEEP
sql
Lan Cui
Lan Cui

Reputation: 137

calculate the discount

I have a customer discount table like these: enter image description here

i have a deceared varialbe named @Total to store the total Amount

declare @Total numeric (12,2)

set @Total = (select Sum(LaborAmt) from #Data
              group by Co)

I need write a query to get the corrected discountRate according to the @Total. That is mean it should return 2 if my total amount is 3500000

SELECT     dbo.budCustomerDisc.Customer, dbo.budCustDiscRate.DiscountRate
FROM         dbo.budCustDiscRate INNER JOIN
                  dbo.budCustomerDisc ON dbo.budCustDiscRate.DiscountID = dbo.budCustomerDisc.DiscountId AND dbo.budCustDiscRate.JBCo = dbo.budCustomerDisc.JBCo
WHERE     (dbo.budCustomerDisc.Customer = 165) .........

sorry i just do not know have to write it

Upvotes: 1

Views: 204

Answers (2)

wax eagle
wax eagle

Reputation: 541

The key thing here is that you are looking for it to return a value when @Total falls between BegBillAmt and EndBillAmt. You want it to return the correct DiscountRate. To do this we need either a BETWEEN statement, or two statements, one that checks BegBillAmt and one that checks EndBill Amt. I'll illustrate both:

First using BETWEEN:

 SELECT     dbo.budCustomerDisc.Customer, dbo.budCustDiscRate.DiscountRate
 FROM         dbo.budCustDiscRate INNER JOIN
              dbo.budCustomerDisc ON dbo.budCustDiscRate.DiscountID = dbo.budCustomerDisc.DiscountId AND dbo.budCustDiscRate.JBCo = dbo.budCustomerDisc.JBCo

 WHERE (dbo.budCustomerDisc.Customer = 165) AND @TOTAL BETWEEN BegBillAmt AND EndBillAmt

Sometimes BETWEEN is unclear (heck I'm not sure it's supported outside SQL Server, or in a case like there where they are database fields rather than static variables). So here it is with the two checks:

 SELECT     dbo.budCustomerDisc.Customer, dbo.budCustDiscRate.DiscountRate
 FROM         dbo.budCustDiscRate INNER JOIN
              dbo.budCustomerDisc ON dbo.budCustDiscRate.DiscountID = dbo.budCustomerDisc.DiscountId AND dbo.budCustDiscRate.JBCo = dbo.budCustomerDisc.JBCo


 WHERE  (dbo.budCustomerDisc.Customer = 165) AND BegBillAmt >= @Total AND EndBillAmt <= @Total

Hope this helps clarify things for you.

Upvotes: 1

Kyra
Kyra

Reputation: 5427

SELECT    budCustomerDisc.Customer, budCustDiscRate.DiscountRate
FROM      budCustDiscRate 
INNER JOIN dbo.budCustomerDisc ON dbo.budCustDiscRate.DiscountID = dbo.budCustomerDisc.DiscountId AND dbo.budCustDiscRate.JBCo = dbo.budCustomerDisc.JBCo
WHERE     budCustomerDisc.Customer = 165 
             And BegBillAmt <= @Total
             And EndBillAmt >= @Total

Upvotes: 2

Related Questions