Re: compare

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Louis Davidson (dr_dontspamme_sql_at_hotmail.com)
Date: 12/03/04 

Date: Thu, 2 Dec 2004 23:03:51 -0600

Well, first. Gross. Beyond the question of why you have numbers at the end
of these stores, this is really not a good design. The following would be
the best approach:

Part
===
PartNumber (pkey)
Description

PartPrice
======
PartNumber
Store
Price
(Pkey partNumber, Store)

PartNumber Description
------------ ------------
123 Dunno
CDE Something Else

PartNumber Store Price
------------ ------ ------
123 WalMart 10
123 Target 12
123 HomeDepot 11
CDE WalMart 5
CDE Target 4.5
CDE HomeDepot 5.2
CDE Different 12

Then you could just use Max to get this job done.

However, in the meantime, you can make your solution work:

SELECT PartNumber, WalMart9, Target8, HomeDepot7,
                case when WalMart9 <= Target8 and WalMart9 <= HomeDepot7
then WalMart9
                        when Target8 <= WalMart9 and Target8 <= HomeDepot7
then Target8
                        else HomeDepot7
                 end as lowestPrice

I hope for your sake you don't have Walgreens128 :) 

-- 
----------------------------------------------------------------------------
Louis Davidson - drsql@hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design - 
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in 
consulting services.  All other replies may be ignored :)
"Ed" <Ed@discussions.microsoft.com> wrote in message 
news:A9D0D592-4DCC-42E0-AC85-C9B95A1FB46E@microsoft.com...
> Hi
>  if i have a table like
> PartNumber  WalMart9    Target8    HomeDepot7
> 123                 $10            $12            $11
>
> CDE                  $5               $4.5            $5.2
>
>
> is there anyway I could create a colmn called lowest pick the lowest price
> for each column...  result
> PartNumber  WalMart9    Target8    HomeDepot7          LowestPrice
> 123                 $10            $12            $11
>   $10
> CDE                  $5               $4.5            $5.2
>     $4.5
>
> Thanks
> Ed
>
>

Relevant Pages

  • Re: ONS - loyal or not?
    ... Our needlecraft shops surely in most cases don't have what I need, ... We have wonderful needlecraft stores online. ... more of our shopping online. ... Design page http://www.KarenMCampbell.com/designs.html ...
    (rec.crafts.textiles.needlework)
  • Re: error message that says too many fields
    ... Another that stores information about Companies. ... A flat design very often leads to duplicate data in your tables. ... For most payments, only a few of those fields ... If the receipt only pays rent, you'll have a record in each table. ...
    (microsoft.public.access.gettingstarted)
  • modelling a contact database
    ... tblCompanies (stores companies' main addresses) ... ChildID (stores RecordID of their employee) ... tblTypes contains three records: Home/Personal, Work, Other. ... I feel like I'm on the right track though, that this table design is smarter ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Halo 3 UK street date broken (seriously)
    ... select few stores (including Shipley and Leeds Crown Point Retail Park if ... you're anywhere near me) are showing as in stock and ready to collect now. ... Reserve online and pick up in store. ... Web Design | Logo Design | Corporate Identity Design ...
    (uk.games.video.xbox)
  • Re: Can I map "PartNumber" in SW to "Number" in PDMWorks?
    ... PartNumber as assigned via ... the configuration properties (User Specified Name selected OR ... $partnumber in a design table) is not a custom property. ...
    (comp.cad.solidworks)