"Ron Hinds" wrote:

> I'm trying to update my Inventory table with the Vendor ID of the vendor
> that has given us the lowest quote on each partnumber. I created this View
> to get the appropriate data from the VendorQuote table:
>
> CREATE VIEW vVendorQuoteTemp AS
> SELECT TOP 100 PERCENT VendorQuote.ItemID, VendorQuote.VendorID,
> VendorQuote.EstimatedCost FROM VendorQuote
> WHERE VendorQuote.Price <> 0.01 AND VendorQuote.QualityOK=1 AND
> VendorQuote.[Date] > getDate() - 750 AND VendorQuote.VendorID <> '1'
> ORDER BY VendorQuote.ItemID, tblVendorQuote.EstimatedCost ASC
>
> Then I'm using this statement to update the SugVendor column in the
> Inventory table:
>
> UPDATE Inventory SET Inventory.SugVendor = vVendorQuoteTemp.VendorID FROM
> Inventory INNER JOIN vVendorQuoteTemp ON Inventory.ItemID =
> vVendorQuoteTemp.ItemID
>
> This code was ported from Microsoft Access (don't laugh ;-) where the View
> was actually a local temp table. Access would always select only the first
> row for each ItemID (that being the lowest EstimatedCost). In SQL Server,
> however, it seems to randomly select which row it inserts. I've been trying
> various different ways of accomplishing this but with no luck. Does anyone
> have any suggestions?
>
>
>
>

"Ravi" <ravishank***@hotmail.com> wrote in message
news:1693FB0D-C5F8-4BAB-AF31-CF71FCE8E036@microsoft.com...
> Try this.
>
> CREATE VIEW vVendorQuoteTemp AS
> SELECT VendorQuote.ItemID, VendorQuote.VendorID,
> MIN(VendorQuote.EstimatedCost) AS  EstimatedCost FROM VendorQuote
> WHERE VendorQuote.Price <> 0.01 AND VendorQuote.QualityOK=1 AND
> VendorQuote.[Date] > getDate() - 750 AND VendorQuote.VendorID <> '1'
> GROUP BY VendorQuote.ItemID, VendorQuote.VendorID
> ORDER BY VendorQuote.ItemID--
> Thanks
> Ravi
>
>
> "Ron Hinds" wrote:
>
> > I'm trying to update my Inventory table with the Vendor ID of the vendor
> > that has given us the lowest quote on each partnumber. I created this
View
> > to get the appropriate data from the VendorQuote table:
> >
> > CREATE VIEW vVendorQuoteTemp AS
> > SELECT TOP 100 PERCENT VendorQuote.ItemID, VendorQuote.VendorID,
> > VendorQuote.EstimatedCost FROM VendorQuote
> > WHERE VendorQuote.Price <> 0.01 AND VendorQuote.QualityOK=1 AND
> > VendorQuote.[Date] > getDate() - 750 AND VendorQuote.VendorID <> '1'
> > ORDER BY VendorQuote.ItemID, tblVendorQuote.EstimatedCost ASC
> >
> > Then I'm using this statement to update the SugVendor column in the
> > Inventory table:
> >
> > UPDATE Inventory SET Inventory.SugVendor = vVendorQuoteTemp.VendorID
FROM
> > Inventory INNER JOIN vVendorQuoteTemp ON Inventory.ItemID =
> > vVendorQuoteTemp.ItemID
> >
> > This code was ported from Microsoft Access (don't laugh ;-) where the
View
> > was actually a local temp table. Access would always select only the
first
> > row for each ItemID (that being the lowest EstimatedCost). In SQL
Server,
> > however, it seems to randomly select which row it inserts. I've been
trying
> > various different ways of accomplishing this but with no luck. Does
anyone
> > have any suggestions?
> >
> >
> >
> >

"Ron Hinds" wrote:

> Thanks Ravi - but when I SELECT * from the view I still get every row for
> each ItemID (the test ItemID has 3 vendor quotes - all 3 are returned). I
> would like the View to contain only one row for each ItemID - that being the
> one with the minimum EstimatedCost.
>
> "Ravi" <ravishank***@hotmail.com> wrote in message
> news:1693FB0D-C5F8-4BAB-AF31-CF71FCE8E036@microsoft.com...
> > Try this.
> >
> > CREATE VIEW vVendorQuoteTemp AS
> > SELECT VendorQuote.ItemID, VendorQuote.VendorID,
> > MIN(VendorQuote.EstimatedCost) AS  EstimatedCost FROM VendorQuote
> > WHERE VendorQuote.Price <> 0.01 AND VendorQuote.QualityOK=1 AND
> > VendorQuote.[Date] > getDate() - 750 AND VendorQuote.VendorID <> '1'
> > GROUP BY VendorQuote.ItemID, VendorQuote.VendorID
> > ORDER BY VendorQuote.ItemID--
> > Thanks
> > Ravi
> >
> >
> > "Ron Hinds" wrote:
> >
> > > I'm trying to update my Inventory table with the Vendor ID of the vendor
> > > that has given us the lowest quote on each partnumber. I created this
> View
> > > to get the appropriate data from the VendorQuote table:
> > >
> > > CREATE VIEW vVendorQuoteTemp AS
> > > SELECT TOP 100 PERCENT VendorQuote.ItemID, VendorQuote.VendorID,
> > > VendorQuote.EstimatedCost FROM VendorQuote
> > > WHERE VendorQuote.Price <> 0.01 AND VendorQuote.QualityOK=1 AND
> > > VendorQuote.[Date] > getDate() - 750 AND VendorQuote.VendorID <> '1'
> > > ORDER BY VendorQuote.ItemID, tblVendorQuote.EstimatedCost ASC
> > >
> > > Then I'm using this statement to update the SugVendor column in the
> > > Inventory table:
> > >
> > > UPDATE Inventory SET Inventory.SugVendor = vVendorQuoteTemp.VendorID
> FROM
> > > Inventory INNER JOIN vVendorQuoteTemp ON Inventory.ItemID =
> > > vVendorQuoteTemp.ItemID
> > >
> > > This code was ported from Microsoft Access (don't laugh ;-) where the
> View
> > > was actually a local temp table. Access would always select only the
> first
> > > row for each ItemID (that being the lowest EstimatedCost). In SQL
> Server,
> > > however, it seems to randomly select which row it inserts. I've been
> trying
> > > various different ways of accomplishing this but with no luck. Does
> anyone
> > > have any suggestions?
> > >
> > >
> > >
> > >
>
>
>

"Ravi" <ravishank***@hotmail.com> wrote in message
news:6E1817F8-D8D2-4631-9B1A-F2633EE777AF@microsoft.com...
> create table vendor
> (vendorid int,
> itemid int,
> cost int)
>
> vendorid    itemid      cost
> ----------- ----------- -----------
> 1           1           200
> 2           1           100
> 3           1           400
>
>
> SELECT V.*
> FROM Vendor V JOIN (SELECT ItemID,MIN(Cost)AS MinCost FROM vendor GROUP BY
> ItemID) T
> ON V.ItemID=T.ItemID AND Cost=MinCost
>
>
> vendorid    itemid      cost
> ----------- ----------- -----------
> 2           1           100
>
> See this work for you
>
>
> --
> Thanks
> Ravi
>
>
> "Ron Hinds" wrote:
>
> > Thanks Ravi - but when I SELECT * from the view I still get every row
for
> > each ItemID (the test ItemID has 3 vendor quotes - all 3 are returned).
I
> > would like the View to contain only one row for each ItemID - that being
the
> > one with the minimum EstimatedCost.
> >
> > "Ravi" <ravishank***@hotmail.com> wrote in message
> > news:1693FB0D-C5F8-4BAB-AF31-CF71FCE8E036@microsoft.com...
> > > Try this.
> > >
> > > CREATE VIEW vVendorQuoteTemp AS
> > > SELECT VendorQuote.ItemID, VendorQuote.VendorID,
> > > MIN(VendorQuote.EstimatedCost) AS  EstimatedCost FROM VendorQuote
> > > WHERE VendorQuote.Price <> 0.01 AND VendorQuote.QualityOK=1 AND
> > > VendorQuote.[Date] > getDate() - 750 AND VendorQuote.VendorID <> '1'
> > > GROUP BY VendorQuote.ItemID, VendorQuote.VendorID
> > > ORDER BY VendorQuote.ItemID--
> > > Thanks
> > > Ravi
> > >
> > >
> > > "Ron Hinds" wrote:
> > >
> > > > I'm trying to update my Inventory table with the Vendor ID of the
vendor
> > > > that has given us the lowest quote on each partnumber. I created
this
> > View
> > > > to get the appropriate data from the VendorQuote table:
> > > >
> > > > CREATE VIEW vVendorQuoteTemp AS
> > > > SELECT TOP 100 PERCENT VendorQuote.ItemID, VendorQuote.VendorID,
> > > > VendorQuote.EstimatedCost FROM VendorQuote
> > > > WHERE VendorQuote.Price <> 0.01 AND VendorQuote.QualityOK=1 AND
> > > > VendorQuote.[Date] > getDate() - 750 AND VendorQuote.VendorID <> '1'
> > > > ORDER BY VendorQuote.ItemID, tblVendorQuote.EstimatedCost ASC
> > > >
> > > > Then I'm using this statement to update the SugVendor column in the
> > > > Inventory table:
> > > >
> > > > UPDATE Inventory SET Inventory.SugVendor = vVendorQuoteTemp.VendorID
> > FROM
> > > > Inventory INNER JOIN vVendorQuoteTemp ON Inventory.ItemID =
> > > > vVendorQuoteTemp.ItemID
> > > >
> > > > This code was ported from Microsoft Access (don't laugh ;-) where
the
> > View
> > > > was actually a local temp table. Access would always select only the
> > first
> > > > row for each ItemID (that being the lowest EstimatedCost). In SQL
> > Server,
> > > > however, it seems to randomly select which row it inserts. I've been
> > trying
> > > > various different ways of accomplishing this but with no luck. Does
> > anyone
> > > > have any suggestions?
> > > >
> > > >
> > > >
> > > >
> >
> >
> >