Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1

    Unanswered: Finding the Missing Items

    I am creating a report that will identify the website categories that our items are in. The purpose is to find items that are not in categories that they should be so they can be fixed. Attached is a csv of a subset of the data.

    The data I attached has all of our Baskets by SKU (ItemNoSKU) and the associated web categories that those items are in. For example, we can see that ItemNoSKU AB107 is in web categories 4, 22, and 23.

    What I have already done is get a Total Baskets vs Web Cat Baskets. I know the total baskets is 44:
    Code:
    SELECT MerchCatDesc, MerchSubCatDesc, COUNT(DISTINCT ItemNoSKU)
    FROM myTable 
    GROUP BY MerchCatDesc, MerchSubCatDesc
    And I know the number of baskets in WebCat 23 is 43:
    Code:
    SELECT WebCatCd,MerchCatDesc, MerchSubCatDesc, COUNT(DISTINCT ItemNoSKU)
    FROM myTable
    GROUP BY WebCatCd, MerchCatDesc, MerchSubCatDesc
    So in this instance, I know there is 1 Basket ItemNoSKU that is not in WebCatCd 23. I need to list this "missing" ItemNoSKU along with the WebCatCd it is missing from. I am struggling on how to write the code to accomplish this for my full list of many categories and web categories. Ideas?
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    how do you 'know'?
    there is 1 Basket ItemNoSKU that is not in WebCatCd 23
    and furthermore how do you 'know' which SKU is not allocated to WebCatCd 23?

    right now probably the best you can do is generate two reports
    one identifies what categories each SKU is in
    one identifies what SKU's are in a category

    a refinement may be to add an extra column to your table that defines the webcatCD and put in a temporary column identifying the number of SKU's you expect for that webcatcd, and then only report webcatCD's whose count of SKU's is not equal to the projected amount.

    after completing the process you probably should delete that column as it will cause many a WTF moment over time
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Quote Originally Posted by healdem View Post
    how do you 'know'?

    and furthermore how do you 'know' which SKU is not allocated to WebCatCd 23?
    I know there are a total of 44 baskets with a simple query. i also know that 43 of those are in WebCatCd 23 with a simple query. So i just need to know that 1 item that is not in WebCatCd 23 but is a basket.

    I'm still working on it, and am getting closer. My methods right now is to assign every WebCatCd that is assigned to at least 1 Basket, to every basket SKU. Then I delete out the "real" items for each WebCatCd, leaving me with the ones that are not in each WebCatCd.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    AB206 NAT is the missing itemnosku in WebCatCd 23.

    For those who don't care about the full solution:
    Code:
    DECLARE @t TABLE (
       itemnosku        VARCHAR(20)
    ,  merchcatdesc     VARCHAR(20)
    ,  merchsubcatdesc  VARCHAR(20)
    ,  webcatcd         INT
       )
    
    INSERT INTO @t (itemnosku, merchcatdesc, merchsubcatdesc, webcatcd) VALUES
    
    ---------------------
    --  Mega snip of data
    ---------------------
    
    ; WITH items AS (
       SELECT DISTINCT itemnosku
          FROM @t
    ), cats AS (
       SELECT DISTINCT webcatcd 
          FROM @t
    ), awol AS (
       SELECT items.itemnosku, cats.webcatcd
          FROM items
          CROSS JOIN cats
          LEFT JOIN @t AS inv
             ON (inv.itemnosku = items.itemnosku
             AND inv.webcatcd = cats.webcatcd)
          WHERE  inv.itemnosku IS NULL
    )
       SELECT *
          FROM awol
    --    WHERE  23 = webcatcd
    Un-comment the last line in basket.sql if you really only care about WebCatCd 23, otherwise there will be 1348.

    -PatP
    Attached Files Attached Files
    Last edited by Pat Phelan; 08-25-14 at 20:17. Reason: Added "interesting" code for the lazy.
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Quote Originally Posted by Pat Phelan View Post
    AB206 NAT is the missing itemnosku in WebCatCd 23.

    Un-comment the last line in basket.sql if you really only care about WebCatCd 23, otherwise there will be 1348.

    -PatP
    Thanks Pat. I was almost there, but doing it in a much less elegant way by not using cross join. Thank you.

  6. #6
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Pat, you solution works when my dataset only has baskets, but my full dataset has other merch cats as well, but a SKU will only be in one merchcatdesc/merchsubcatdesc. I updated the .sql you created to insert additional skus from a different merchcat into the table variable so you can see what i mean.
    Attached Files Attached Files

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm slow, but is that good, bad, or indifferent?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Sorry, should have been more clear. Given the amended data I attached in my previous post, the result set should only have one record for webcatcd 23, that being AB206 NAT.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by clawlan View Post
    Sorry, should have been more clear. Given the amended data I attached in my previous post, the result set should only have one record for webcatcd 23, that being AB206 NAT.
    Why? There are many products that are not in WebCatCd 23. How is AB206 NAT different from the other 18?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #10
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Quote Originally Posted by Pat Phelan View Post
    Why? There are many products that are not in WebCatCd 23. How is AB206 NAT different from the other 18?

    -PatP
    Good question. What our web team is ultimately trying to figure out is what items are likely missing from certain web categories based on that item's Merchandise Category (MerchCatDesc + MerchSubCatDesc). So, from my original example, we can see that there are 44 SKUs in the Accessories+Baskets merchandise category. The next step is to determine of those 44, how many are in each of the web categories. A SKU can only belong to one merchandise category, but can but in several different web categories (think of a SKU in the Mirrors merchandise category that would likely be in multiple web categories such as "All Mirrors", "Bathroom Accessories", "Wall Hangings", etc.). Merchandise categories are used behind the scenes for inventory purposes while web categories are for the customers.

    So going back to the example, of the 44 total items that are in merchandise category Accessories+Baskets, 43 of them are in web category 23. So that means there is one Accessories+Baskets SKU that is not in web category 23, so the purpose of this report is to show the ecommerce team this lone Accessories+Baskets SKU that is not in web cat 23.

    This report is organized by merchandise category, so if there are 0 SKUs of a given merchandise category in a given web category, then that web category is ignored for the purpose of this report. I'm always starting with a distinct list of MerchCatDesc+MerchSubCatDesc+WebCatCd so I know the eligible WebCats for each merchandise category (web cats that have at least one item in them for each merchandise category).

    Does that make any more sense? I'll post an image of the report in its current state when I get back to the office in the morning which may help it make more sense.

    EDIT: Wait, I may be an idiot. Your code may do exactly that, and I was looking at it wrong. Unfortunately the vpn is not working so I can't log into my workstation to check. Will reconvene in the morning.
    Last edited by clawlan; 08-26-14 at 00:34.

  11. #11
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Ok, now that I have a clear head this morning, I can see what the "issue" is. Your code is returning more than I expected. My report only shows web categories in which there is at least one item from the merch cat in it. For example, Baskets are only in 38 of the 451 different web categories. There are 0 Basket SKUs in the other 413 web categories, so the code returns all 44 basket SKUs 413 more times. But I don't care about these 413 other web cats because there are no baskets in them to begin with.

    When I ran the code on the full dataset, I am getting several million results because of this. I am working on a way to only return merchcat/webcat results in which there is at least one SKU "not missing".

  12. #12
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    Ideas?
    You have a classic design flaw called attribute splitting. When you go to a library database, is the Dewey Decimal Classification in two or more tables? NO! A category system is one scale so it is in one table, not split into sub-sub-sub category tables.
    Since this is a SQL forum, we do not use spreadsheets or XML, or anything but DDL. Your Zip file was useless and just made extra work if anyone wanted to get the data out of it.

    Since we have no DDL, in violation of Netiquette, I would guess that you should have an inventory table keyed on a SKU and another

    CREATE TABLE Baskets
    (sku CHAR(15) NOT NULL
    REFERENCES Inventory (sku)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
    merchandise_cat CHAR(5) NOT NULL
    REFERENCES Shop_Categories (sku),
    PRIMARY KEY (sku, merchandise_cat));

    CREATE TABLE Shop_Categories
    (merchandise_cat CHAR(5) NOT NULL PRIMARY KEY,
    category_description VARCHAR(25) NOT NULL);

Posting Permissions

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