Select Minimum Amount

[jsccorps]

Have 2 vendors. VendorA and VendorB. Want to use the lowest remit. Unless,
if VendorB is not active, then use VendorA remit. Example:

Date VendorA RemitA ActiveA VendorB RemitB ActiveB
6/1/09 11 4.5 Yes 22 6.3 Yes
6/2/09 11 9.0 No 22 8.6 Yes
6/3/09 11 6.2 Yes 22 5.00 No
6/4/09 11 4.5 Yes 22 3.8 Yes
6/5/09 11 8.5 Yes 22 7.5 No

1) Use minimum of RemitA and RemitB, except
2) If ActiveB= No, then use RemitA (even though VendorB might have the
lowest remit)

Desired result:

Date Vendor Remit
6/1/09 11 4.5
6/2/09 22 8.6
6/3/09 11 6.2
6/4/09 22 3.8
6/5/09 11 8.5

 

[John W. Vinson]

Have 2 vendors. VendorA and VendorB. Want to use the lowest remit. Unless,
if VendorB is not active, then use VendorA remit. Example:

Date VendorA RemitA ActiveA VendorB RemitB ActiveB
6/1/09 11 4.5 Yes 22 6.3 Yes
6/2/09 11 9.0 No 22 8.6 Yes
6/3/09 11 6.2 Yes 22 5.00 No
6/4/09 11 4.5 Yes 22 3.8 Yes
6/5/09 11 8.5 Yes 22 7.5 No

1) Use minimum of RemitA and RemitB, except
2) If ActiveB= No, then use RemitA (even though VendorB might have the
lowest remit)

Desired result:

Date Vendor Remit
6/1/09 11 4.5
6/2/09 22 8.6
6/3/09 11 6.2
6/4/09 22 3.8
6/5/09 11 8.5

What will you do when (not if!) you want to compare three vendors? or four? or
six?

Your table design is appropriate for a spreadsheet but you're just making your
job much harder by not using a properly normalized table, with one record per
remit/vendor pair. A very simple Totals query would let you answer this
question with such a design.

As it is, you'll need a complex nest of IIF statements, selecting the remit
only if Active is Yes and comparing the remits, using NZ() to get null values.
Your example (but not your narrative) suggests that VendorA might be inactive
(in which case you would use VendorB); what if BOTH are inactive?

I'd really suggest correcting your design rather than messing with this
calculation!

 

[jsccorps]

Thanks John. Is the following table format ok?

Date Vendor Remit Active
6/1/2009 22 6.3 Y
6/1/2009 11 4.5 Y
6/2/2009 22 8.6 Y
6/2/2009 11 9 N
6/3/2009 22 5 N
6/3/2009 11 6.2 Y
6/4/2009 22 3.8 Y
6/4/2009 11 4.5 Y
6/5/2009 22 7.5 N
6/5/2009 11 8.5 Y

For a particular date, want to pull the vendor with lowest remit (and
active). Vendor 11 is the default if the remits are equal. Also, Vendor 11
is the default, if both Vendors are not active (Active=N).

jsccorps

 

[Graham Mandeno]

Yes, that table structure is *much* better

I suggest you change the name of the date field to RemitDate, as Date is a
reserved word and can cause problems.

This query should do the trick:

SELECT RemitDate, Vendor, Remit, Active
FROM Remits
WHERE Vendor In (Select top 1 Vendor from Remits as X
where X.RemitDate=Remits.RemitDate order by Active, Remit, Vendor=11);