MS SQL Server Query – Explanation Below

Stuck on how to achieve the following:

We have one Item, with the following properties.

Item = ABC
Buy Quantity = 6
Tolerance Quantity = 4

We are trying to figure out how to calculate the Charged Quantity, the following is how it would work

Ordered Quantity    Charged Quantity
1                   1
1.5                 1.5
3.83                3.83
4                   6
5.54                6
6                   6
7                   7
8.32                8.32
9                   9
10                  12
11.9                12
12                  12

The buy quantity is 6 and the tolerance quantity is 4. This means that when the ordered quantity becomes in range of 4-6 it should take the buy quantity.

If it is not in this range, then it simply takes the Ordered Quantity.

The tricky part is doing this when the Ordered Quantity falls into each “range”:

6(4-6)
12(10-12)
18(16-18)
24(22-24)
and so on...

Each item could have different Buy/Tolerance quantities.

Interested to see your solutions/advice.


Source: sql

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.