Calculated Columns

• FastRider30

  SSC Veteran

  Points: 293

  More actions

  December 23, 2009 at 9:15 am

  #94229

  Hi guys,

  I need to build an order system that work in Batches based on the quantity on hand. I need a table that looks like this:

  Part | Qty | Batch1 | Batch2 | Batch3

  where Batch1/Batch2/Batch3 are calculated based on the Qty.

  The tricky part is I cannot order just one Batch, I may need to order 2 or even 3 Batches...

  Here is an example:

  If Part A has a Qty of 2 and my ideal stock value is at least 30 I will order 10+10+10 (batch1+batch2+batch3)

  Part | Qty | Batch1 | Batch2 | Batch3

  A | 3 | 10 | 10 | 10

  If Part A has a Qty of 12 and my ideal stock value is at least 30 I will order 10+10 (batch1+batch2)

  Part | Qty | Batch1 | Batch2 | Batch3

  A | 12 | 10 | 10 | 0

  If Part A has a Qty of 26 and my ideal stock value is at least 30 I will order 10 (batch1)

  Part | Qty | Batch1 | Batch2 | Batch3

  A | 26 | 10 | 0 | 0

  Thank you all for your help.

• FastRider30

  SSC Veteran

  Points: 293

  More actions

  December 23, 2009 at 10:15 am

  #1096031

  I got it.

  For someone looking for the same:

  SELECT PART, QTY, BATCH1 = CASE WHEN PART='A' AND QTY < 10 THEN 10 ELSE 0 END, BATCH2 = ..........