Richard Richard - 5 months ago 9
SQL Question

SQL Showing Cheapest Supplier

I am creating a query to find the cheapest supplier for specific products.

The user will be able to select up to 4 suppliers to compare.

My Full code

declare @Sup as varchar(20) set @Sup = 'WESTF'
declare @Sup2 as varchar(20) set @Sup2 = 'NAVIG'
declare @Sup3 as varchar(20) set @Sup3 = 'PRIMF'
declare @Sup4 as varchar(20) set @Sup4 = ''

select 'Product' as ProductCode, 'Description' as Description,
@Sup as Cost, @Sup as SupplierDate,
@Sup2 as Cost2, @Sup2 as SupplierDate2,
@Sup3 as Cost3, @Sup3 as SupplierDate3,
@Sup4 as Cost4, @Sup4 as SupplierDate4, 0 as Cheapest

union all

select p.ProductCode,p.Description,
'£' + cast(psp1.Cost as varchar) + ' Per ' + Cast(psp1.Per as varchar)as Cost1,psp1.SupplierDate,
'£' + cast(psp2.Cost as varchar) + ' Per ' + Cast(psp2.Per as varchar)as Cost2,psp2.SupplierDate,
'£' + cast(psp3.Cost as varchar) + ' Per ' + Cast(psp3.Per as varchar)as Cost3,psp3.SupplierDate,
'£' + cast(psp4.Cost as varchar) + ' Per ' + Cast(psp4.Per as varchar)as Cost4,psp4.SupplierDate,


MinValue as Cheapest
from
product as p
left join
(
select psp.productid,psp.StandardBuy as Cost ,Per.Name as Per,
per.ConversionToBase,s.SupplierCode,
cast(convert(Date, psp.StandardBuyDateTime, 103) as varchar) as SupplierDate
from ProductSupplierPrice as psp
left join Supplier as s on s.SupplierID = psp.SupplierID
left join Per on Per.PerID = psp.BuyPerID
where s.SupplierCode = @Sup
) as psp1 on psp1.ProductID = p.ProductId

left join
(
select psp.productid,psp.StandardBuy as Cost ,Per.Name as Per,
per.ConversionToBase,s.SupplierCode,
cast(convert(Date, psp.StandardBuyDateTime, 103) as varchar) as SupplierDate
from ProductSupplierPrice as psp
left join Supplier as s on s.SupplierID = psp.SupplierID
left join Per on Per.PerID = psp.BuyPerID
where s.SupplierCode = @Sup2
) as psp2 on psp2.ProductID = p.ProductId

left join
(
select psp.productid,psp.StandardBuy as Cost ,Per.Name as Per,
per.ConversionToBase,s.SupplierCode,
cast(convert(Date, psp.StandardBuyDateTime, 103) as varchar) as SupplierDate
from ProductSupplierPrice as psp
left join Supplier as s on s.SupplierID = psp.SupplierID
left join Per on Per.PerID = psp.BuyPerID
where s.SupplierCode = @Sup3
) as psp3 on psp3.ProductID = p.ProductId

left join
(
select psp.productid,psp.StandardBuy as Cost ,Per.Name as Per,
per.ConversionToBase,s.SupplierCode,
cast(convert(Date, psp.StandardBuyDateTime, 103) as varchar) as SupplierDate
from ProductSupplierPrice as psp
left join Supplier as s on s.SupplierID = psp.SupplierID
left join Per on Per.PerID = psp.BuyPerID
where s.SupplierCode = @Sup4
) as psp4 on psp4.ProductID = p.ProductId

CROSS APPLY (SELECT MIN(d) MinValue FROM (VALUES
(psp1.Cost/psp1.ConversionToBase),
(psp2.Cost/psp2.ConversionToBase),
(psp3.Cost/psp3.ConversionToBase),
(psp4.Cost/psp4.ConversionToBase)) AS a(d)) A

where p.Deleted = 0 and p.Description like '%endfeed%'


however the important bit is:

CROSS APPLY (SELECT MIN(d) MinValue FROM (VALUES
(psp1.Cost/psp1.ConversionToBase),
(psp2.Cost/psp2.ConversionToBase),
(psp3.Cost/psp3.ConversionToBase),
(psp4.Cost/psp4.ConversionToBase)) AS a(d)) A


This finds the cheapest price but I don't want to display the cheapest price I want to know the supplier code related to the cheapest price.

I'm not sure of the best way to do this any help would be appreciated.

Answer

Replace it with

CROSS APPLY (
 SELECT top(1) d,supplierId
 FROM (VALUES 
   (psp1.Cost/psp1.ConversionToBase, psp1.<supplierID here>), 
   (psp2.Cost/psp2.ConversionToBase, psp2.<supplierID here>), 
   (psp3.Cost/psp3.ConversionToBase, psp3.<supplierID here>), 
   (psp4.Cost/psp4.ConversionToBase, psp4.<supplierID here>)
   ) AS a(d,supplierId)
 ORDER BY d ASC) A
Comments