Results 1 to 14 of 14
  1. #1
    Join Date
    Mar 2013
    Posts
    7

    Unanswered: Create Duplicate Rows with one column changed

    I am writing a db conversion for a retail grocery chain. This chain uses pricing zones to designate what stores get a certain price

    Example:
    Cheetos
    Zone A: $2.79
    Zone B: $2.89

    The pricing data in the tables is listed by zone. However, the new product uses pricing by store.

    Zone A contains stores 1,2,4,6,7....
    Zone B contains stores 10,11,12,14.....

    I need to be able to duplicate the rows in a manner that I can take the row containing a price for Zone A and duplicate it for each store in the zone. I have a table of stores with corresponding zones.

    So I'm looking to go from:

    Zone UPC Price
    A 1234500000 2.79
    B 1234500000 2.89

    To:
    Store UPC Price
    1 1234500000 2.79
    2 1234500000 2.79
    10 1234500000 2.89
    11 1234500000 2.89

    Anyone have any thoughts on how I might achieve this solution?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Something like this..?
    Code:
    SELECT stores.zone
         , prices.upc
         , prices.price
    FROM   dbo.stores
     INNER
      JOIN dbo.prices
        ON prices.store = stores.store
    This has made some assumptions about your database design as you have not provided it.
    George
    Home | Blog

  3. #3
    Join Date
    Mar 2013
    Posts
    7
    Without getting too deep into structure (initial tables are actually data pulled from indexed text files that are read with an odbc connector) here would be the three tables involved

    Zone- Zone, Store
    PriceIn - UPC, Zone, Price
    PriceOut - UPC, Store, Price

    So I would need to duplicate each line in PriceIn to contain a line for each store in the zone in order to create the data for the PriceOut table.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Minor tweak to previously posted query should give you the resultset you are looking for.
    Code:
    SELECT zone.zone
         , pricein.upc
         , pricein.price
    FROM   dbo.zone
     INNER
      JOIN dbo.princein
        ON pricein.store = stores.store
    Give it a test and report back
    George
    Home | Blog

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by mg42 View Post
    Without getting too deep into structure (initial tables are actually data pulled from indexed text files that are read with an odbc connector) here would be the three tables involved

    Zone- Zone, Store
    PriceIn - UPC, Zone, Price
    PriceOut - UPC, Store, Price

    So I would need to duplicate each line in PriceIn to contain a line for each store in the zone in order to create the data for the PriceOut table.
    why would you need to duplicate data?
    if the company prices according to store location codes then presumably 'all' you need is the zone prices not a price file for each store. even if each store was allowed to vary prices on a few items you could have an override price table if there is a value in the override table use that, otherwise use the zone price.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I assumed that this was to give each store a "base" price to begin with. Then each store can [optionally] change their prices.

    Obviously a base price can be calculated based on the PriceIn, but hey, then you're expecting your front-end bods to make things clever




    P.S. Hi Healdem! How's things? soon?
    George
    Home | Blog

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by gvee View Post
    How's things? soon?
    ..as you know, just like my avatar I never touch the stuff.....
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Mar 2013
    Posts
    7
    Quote Originally Posted by healdem View Post
    why would you need to duplicate data?
    if the company prices according to store location codes then presumably 'all' you need is the zone prices not a price file for each store. even if each store was allowed to vary prices on a few items you could have an override price table if there is a value in the override table use that, otherwise use the zone price.
    In the GUI the new product allows for zone pricing. However, on import from the old system it only allows by store pricing. So I have to convert the pricing from zone to individual store for import. There is an override table as well but only works after import.

  9. #9
    Join Date
    Mar 2013
    Posts
    7
    Quote Originally Posted by gvee View Post
    Minor tweak to previously posted query should give you the resultset you are looking for.
    Code:
    SELECT zone.zone
         , pricein.upc
         , pricein.price
    FROM   dbo.zone
     INNER
      JOIN dbo.princein
        ON pricein.store = stores.store
    Give it a test and report back
    Thanks for the input. This gave me the results by zone but not by each individual store within the zone. The result I am looking for would write a row for each store within the zone.

    So instead of

    A 1234500000 2.79
    B 1234500000 2.89

    I would see

    1 123450000 2.79
    2 123450000 2.79
    10 123450000 2.89
    11 123450000 2.89

    Thanks!!!

  10. #10
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    I think you have an attribute splitting design flaw. Think of the (zone_code, store_nbr) as a co-ordinate system that locates a store in a space

    Now use DRI to assure the store is in one and only one Zone. Load this table first from your raw data.

    CREATE TABLE Zone_Assignments
    (zone_code CHAR(2) NOT NULL,
    store_nbr INTEGER NOT NULL UNIQUE
    PRIMARY KEY (zone_code, store_nbr));

    Make the second pass and load the price list:

    CREATE TABLE Price_List
    (upc CHAR(15) NOT NULL --- extra space for GTIN?
    REFERENCES Inventory(upc)
    zone_code CHAR(2) NOT NULL,
    store_nbr INTEGER NOT NULL,
    FOREIGN KEY (zone_code, store_nbr)
    REFERENCES Zone_Assignments (zone_code, store_nbr)
    ON UPDATE CASCADE
    ON DELETE CASCADE,
    unit_price DECIMAL(10,4) NOT NULL
    CHECK (unit_price >= 0.00));

  11. #11
    Join Date
    Mar 2013
    Posts
    7
    Quote Originally Posted by Celko View Post
    I think you have an attribute splitting design flaw. Think of the (zone_code, store_nbr) as a co-ordinate system that locates a store in a space

    Now use DRI to assure the store is in one and only one Zone. Load this table first from your raw data.

    CREATE TABLE Zone_Assignments
    (zone_code CHAR(2) NOT NULL,
    store_nbr INTEGER NOT NULL UNIQUE
    PRIMARY KEY (zone_code, store_nbr));

    Make the second pass and load the price list:

    CREATE TABLE Price_List
    (upc CHAR(15) NOT NULL --- extra space for GTIN?
    REFERENCES Inventory(upc)
    zone_code CHAR(2) NOT NULL,
    store_nbr INTEGER NOT NULL,
    FOREIGN KEY (zone_code, store_nbr)
    REFERENCES Zone_Assignments (zone_code, store_nbr)
    ON UPDATE CASCADE
    ON DELETE CASCADE,
    unit_price DECIMAL(10,4) NOT NULL
    CHECK (unit_price >= 0.00));
    Ok, I see where you are going with this. On my initial post I should have stated that I already have the stores assigned to a single zone in a table named store

    store zone
    1 A
    2 A
    10 B
    11 B

    I'll try your second statement and see if that gives the desired results. Thanks!!!

  12. #12
    Join Date
    Mar 2013
    Posts
    7
    Thanks to everyone for the input. Greatly appreciated!!!

    I ended up taking a different approach:

    declare @cnt int
    declare @store varchar(3)
    declare @zone varchar(3)
    set @cnt=1
    set @store=(select store from store_zone where Store_Zone='1' and store_id=@cnt)
    set @zone=' A'
    while @cnt<(SELECT COUNT(*) FROM store_zone where Store_Zone='1')
    begin
    insert into sis_price_zone
    (change_num,location,store,item,initial_date,final _date,margin,previous_price,retail_quantity,
    sales_price,price_type,currency,provider_store,cha nge_status,reason_change,base_unit_price,div_retai l)
    Select
    change_num,location,@store,item,initial_date,final _date,margin,previous_price,retail_quantity,
    sales_price,price_type,currency,provider_store,cha nge_status,reason_change,base_unit_price,div_retai l
    from sis_price_zone
    where sis_price_zone.store=@zone
    set @cnt=@cnt+1
    End


    However, now I'm wonder how to make the loop do the same for zone B without having to add another matching statement substituting B for A.

  13. #13
    Join Date
    Mar 2013
    Posts
    7
    Also @store is returning a NULL value if the store number is greater than 9. This one has me really confused. The column is an int.

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Blurgh, silly mistake in my previous query. Fixed:

    Code:
    SELECT zone.store
         , pricein.upc
         , pricein.price
    FROM   pricein
     INNER
      JOIN zone
        ON zone.zone = pricein.zone
    In reference to your last question: if the store number column is of integer type, why is your @store variable a varchar(3)?
    George
    Home | Blog

Posting Permissions

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