Results 1 to 6 of 6

Thread: I can't get customized pricing to work

  1. #1
    Join Date
    May 2006
    Posts
    8

    I can't get customized pricing to work

    Hi,
    I don't know much about SQL. So, be gentle with me. . . I have a product table with 350 records. I also have some customers who need customized prices for some of the products in that table.

    I created a customer price table that includes the customer ID, product ID and custom pricing. I hoped to write a SP that would display all 350 products, and custom pricing, if any, for the customer that is logged in.

    Here is my attempt:
    The Product Table is DT_Products. The custom pricing table is DT_CustomerPrices. In this example the customer ID is 4415
    <code>
    SELECT DT_Products.itemcode, DT_Products.price, DT_CustomerPrices.custitemcode, DT_CustomerPrices.custPrice, DT_CustomerPrices.Custnum, DT_Products.active
    FROM DT_Products LEFT JOIN DT_CustomerPrices ON DT_Products.itemcode = DT_CustomerPrices.custitemcode
    WHERE (((DT_CustomerPrices.Custnum)='4415') AND ((DT_Products.active)=1)) OR (((DT_CustomerPrices.Custnum) Is Null) AND ((DT_Products.active)=1))</code>

    The results display only 212 of the 350 products, if the customer has NO custom prices.
    If the customer has any custom prices, the results are increased by the number of custom prices.

    Any help getting my query to display all 350 products with custom prices, would be greatly appreciated.
    Thanks,
    Jim

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Do you have table schema and sample data?

  3. #3
    Join Date
    May 2006
    Posts
    8

    Here is the schema for the two tables in question

    create table dbo.DT_CustomerPrices(
    SalesRep varchar(3) NOT NULL,
    CustID char(10) NOT NULL,
    ID numeric() identity(18) NOT NULL,
    custitemcode nvarchar(20) NOT NULL,
    custPrice varchar(8) NULL,
    DateEntered datetime NULL,
    Custnum varchar(4) NULL
    )

    create table dbo.DT_Products(
    ID numeric() identity(18) NOT NULL,
    itemcode nvarchar(20) NOT NULL,
    price varchar(8) NULL,
    description ntext NULL,
    pic nvarchar(75) NULL,
    productsize nvarchar(25) NULL,
    taxfree int NULL,
    brand nvarchar(25) NULL,
    searchcode nvarchar(3) NULL,
    title nvarchar(50) NULL,
    briefdesc nvarchar(250) NULL

    )

  4. #4
    Join Date
    May 2006
    Posts
    8
    This is some sample data from the DT_customerPrices Table

    SalesRep CustID ID custitemcode custPrice DateEntered Custnum
    TE alldia 97 129G 10.00 4/18/2006 4497
    LD ALLDIA 102 26B-6 50 11/13/2006 4455
    DB ads 130 ACCO51 24.75 4428
    DB ads 131 ACML 9.5 4428
    DB ads 132 ACTMC50 24.95 4428
    DB ads 133 AVMC50 25.43 4428
    DB ads 134 AVMTR 27.5 4428
    DB ads 135 CCHO50 24.25 4428
    DB ads 136 COCS 4.58 4428
    DB ads 137 FLASH 18.9 4428
    DB ads 138 FSINMK 10.56 4428
    DB ads 139 FTMO50 24 4428
    DB ads 140 OTMC50 25.3 4428

    Here is some sample data from the DT_Products table:

    ID itemcode price description pic productsize taxfree brand searchcode title briefdesc
    333 10100N $50.00 3" Walker Wheels images/products/10100N.jpg 1pr/bx dri hom 3" Walker Wheels with Rear Glide Caps, Grey 3" Walker Wheels with Rear Glide Caps, Grey
    334 10100NBL 0 3" Walker Wheels images/products/10100N.jpg 1pr/bx dri hom 3" Walker Wheels with Rear Glide Caps, Blue 3" Walker Wheels with Rear Glide Caps, Blue
    90 10100NG 0 3" Walker Wheels images/products/10100N.jpg 1pr/bx dri hom 3" Walker Wheels with Rear Glide Caps, Green 3" Walker Wheels with Rear Glide Caps, Green
    335 10100NGL 0 3" Walker Wheels images/products/10100N.jpg 1pr/bx dri hom 3" Walker Wheels with Rear Glide Caps, Gold 3" Walker Wheels with Rear Glide Caps, Gold
    336 10100NP 0 3" Walker Wheels images/products/10100N.jpg 1pr/bx dri hom 3" Walker Wheels with Rear Glide Caps, Pink 3" Walker Wheels with Rear Glide Caps, Pink
    337 10101N 0 3" Walker Wheels images/products/10101N.jpg 1pr/bx dri hom 5" Walker Wheels with Rear Glide Caps 5" Walker Wheels with Rear Glide Caps
    Last edited by ddiabetes; 06-30-2008 at 05:31 PM. Reason: too much info

  5. #5
    Join Date
    Jul 2008
    Posts
    3

    Here is modified query

    SELECT DT_Products.itemcode, DT_Products.price, DT_CustomerPrices.custitemcode, DT_CustomerPrices.custPrice, DT_CustomerPrices.Custnum, DT_Products.active
    FROM DT_Products LEFT JOIN DT_CustomerPrices ON DT_Products.itemcode = DT_CustomerPrices.custitemcode
    WHERE DT_CustomerPrices.Custnum='4415' AND DT_Products.active=1

  6. #6
    Join Date
    May 2006
    Posts
    8
    I made one small change. I put an AND statement inside the JOIN <code>
    AND DT_CustomerPrices.Custnum='4415'
    </code> moving it from the WHERE clause.
    <ORIGINAL CODE>
    SELECT DT_Products.itemcode, DT_Products.price, DT_CustomerPrices.custitemcode, DT_CustomerPrices.custPrice, DT_CustomerPrices.Custnum, DT_Products.active
    FROM DT_Products LEFT JOIN DT_CustomerPrices ON DT_Products.itemcode = DT_CustomerPrices.custitemcode
    WHERE DT_CustomerPrices.Custnum='4415' AND DT_Products.active=1
    </ORIGINAL CODE>


    <Adjusted code>
    SELECT DT_Products.itemcode, DT_Products.price, DT_CustomerPrices.custitemcode, DT_CustomerPrices.custPrice, DT_CustomerPrices.Custnum, DT_Products.active
    FROM DT_Products LEFT JOIN DT_CustomerPrices ON DT_Products.itemcode = DT_CustomerPrices.custitemcode AND DT_CustomerPrices.Custnum='4415'
    WHERE DT_Products.active=1
    </Adjusted code>
    That fixed it! the results display all 350 products plus the custom pricing for the logged in customer.
    Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •