Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unanswered: Things, suppliers, components and defaults

    Have yet to find an elegant solution to the following problem. 3rd-party system, no control over design. I hope the below code illustrates the problem sufficiently but if not, ask any questions you might have!
    Code:
    DECLARE @suppliers table (
       supplier char(3)
    );
    
    DECLARE @things table (
       thing_id int
     , supplier char(3)
    );
    
    DECLARE @components table (
       thing_id  int
     , supplier  char(3)
     , component char(11)
     , price     money
    );
    
    INSERT INTO @suppliers (supplier)
      VALUES ('Me')
           , ('You')
    ;
    
    INSERT INTO @things (thing_id, supplier)
      VALUES (9, 'Me' )
           , (9, 'You')
           , (3, 'Me' )
           , (3, 'You')
           , (7, 'Me' )
           , (7, 'You')
    ;
    
    -- Create default components for 'You'... yes, the system sets the item_id to zero.
    INSERT INTO @components (thing_id, supplier, component, price)
      VALUES (0, 'You', 'Widget', 3)
           , (0, 'You', 'Doodad', 1)
    ;
    
    -- Specific components
    INSERT INTO @components (thing_id, supplier, component, price)
      VALUES (9, 'You', 'Thingamabob', 8)
           , (3, 'You', 'Thingamabob', 9)
           , (3, 'You', 'Widget'     , 5)
           , (3, 'Me' , 'Widget'     , 4)
           , (3, 'Me' , 'Doodad'     , 8)
           , (7, 'You', 'Doodad'     , 4)
           , (7, 'You', 'Thingamabob', 4)
           , (7, 'Me' , 'Widget'     , 4)
           , (7, 'Me' , 'Thingamabob', 7)
    ;
    
    /*
      -- Rules --
      The idea is to list all of the applicable components by thing, by supplier.
      All things inherit supplier default components (components where thing_id = 0)
      They also get their specific components (matching on thing_id).
      Specific components "override" default components.
    
      -- Desired Output --
      +----------+----------+-------------+-------+
      | thing_id | supplier | component   | price |
      =============================================
      | 3        | Me       | Doodad      | 8     |
      | 3        | Me       | Widget      | 4     |
      +----------+----------+-------------+-------+
      | 3        | You      | Doodad      | 1     |
      | 3        | You      | Thingamabob | 9     |
      | 3        | You      | Widget      | 5     |
      +----------+----------+-------------+-------+
      | 7        | Me       | Thingamabob | 7     |
      | 7        | Me       | Widget      | 4     |
      +----------+----------+-------------+-------+
      | 7        | You      | Doodad      | 4     |
      | 7        | You      | Thingamabob | 4     |
      | 7        | You      | Widget      | 3     |
      +----------+----------+-------------+-------+
      | 9        | You      | Doodad      | 1     |
      | 9        | You      | Thingamabob | 8     |
      | 9        | You      | Widget      | 3     |
      +----------+----------+-------------+-------+
    */
    Any ideas?

    Cheers
    Last edited by gvee; 04-23-14 at 06:14. Reason: Ordering of desired resultset
    George
    Home | Blog

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Here's my best attempt so far but it feels so clunky...
    The use of DISTINCT just feels... wrong?
    The OR clause isn't very optimized either.
    Code:
    SELECT things.thing_id
         , things.supplier
         , CASE WHEN specific_components.component IS NULL THEN default_components.component ELSE specific_components.component END As component
         , CASE WHEN specific_components.component IS NULL THEN default_components.price     ELSE specific_components.price     END As price
    FROM   @things As things
     CROSS
     APPLY (
            SELECT DISTINCT
                   component
            FROM   @components
            WHERE  ( supplier = things.supplier
                    AND thing_id = things.thing_id )
                OR ( supplier = things.supplier
                    AND thing_id = 0 )
           ) As all_components
     LEFT
      JOIN @components As specific_components
        ON specific_components.thing_id  = things.thing_id
       AND specific_components.supplier  = things.supplier
       AND specific_components.component = all_components.component
     LEFT
      JOIN @components As default_components
        ON default_components.thing_id  = 0
       AND default_components.supplier  = things.supplier
       AND default_components.component = all_components.component
    ORDER
        BY things.thing_id
         , things.supplier
         , component
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Revised with what I think is a better join methodology
    Code:
    SELECT things.thing_id
         , things.supplier
         , CASE WHEN specific_components.component IS NULL THEN default_components.component ELSE specific_components.component END As component
         , CASE WHEN specific_components.component IS NULL THEN default_components.price     ELSE specific_components.price     END As price
    FROM   @things As things
     CROSS
     APPLY (
            SELECT supplier
                 , component
                 , Max(thing_id) As thing_id
            FROM   @components
            WHERE  ( supplier = things.supplier
                    AND thing_id = things.thing_id )
                OR ( supplier = things.supplier
                    AND thing_id = 0 )
            GROUP
                BY supplier
                 , component
           ) As all_components
     LEFT
      JOIN @components As specific_components
        ON specific_components.thing_id   > 0
       AND specific_components.thing_id  = all_components.thing_id
       AND specific_components.supplier  = all_components.supplier
       AND specific_components.component = all_components.component
     LEFT
      JOIN @components As default_components
        ON default_components.thing_id  = 0
       AND default_components.supplier  = all_components.supplier
       AND default_components.component = all_components.component
    ORDER
        BY things.thing_id
         , things.supplier
         , component
    George
    Home | Blog

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What tells you that any given component can/should be part of a thing? A dahoogie can be part of a whopper if it is amphibious, but there's no way to put a dahoogie into a wazzle. On the other hand, a wazzle won't work without three dinguses (which any doofus knows). I get lost in the details, so I need a thing-component list of some kind!

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

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you can resolve the thing-component problem, then something like:
    Code:
    SELECT things.thing_id
    ,  things.supplier
    ,  components.component
    ,  components.price
       FROM  @things AS things
       INNER JOIN @components AS components
          ON (components.supplier = things.supplier
    	  AND components.thing_id = (SELECT Coalesce(z.thing_id, 0)
    	     FROM @components AS z
    		 WHERE  z.supplier = things.supplier
    		    AND z.thing_id = things.thing_id))
       ORDER BY
           things.thing_id
         , things.supplier
         , component
    ...might work.

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

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    A bill of materials "doesn't exist". Seriously.

    The components that build up a thing can vary. Apparently.

    A supplier can provide certain components at a pretty fixed price. These are set up as "default" components.

    The thing composition is a culmination of the default components and the specific items for that thing.

    If we can get a better price from the supplier outside of the default agreement we can create a thing-supplier specific request that overrides the default.

    E.G. I am building a shelf.
    The supplier I am using on this occasion is "Nuts N Bolts Ltd"
    There's an agreement in place that says that NNB Ltd will supply (you guessed it) nuts and bolts at a fixed price for all things ordered from them. 0.05 per nut and 0.10 per bolt.
    To build this shelf I also need to order some wood from NNB Ltd and have made a separate agreement that I can get the nuts for just 0.02 per unit for my shelves.

    Default supplier components:
    nuts: 0.05
    bolts: 0.10

    Shelf specific components
    nuts: 0.02
    wood: 0.50

    Components to be "ordered" in this instance:
    nuts: 0.02
    bolts: 0.10
    wood: 0.50


    I know I am guilty of obfuscating the problem by using these false examples (consider it data protection) but the data is relevant/equivalent to the problem I am trying to solve.
    George
    Home | Blog

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by Pat Phelan View Post
    If you can resolve the thing-component problem...
    The "all_components" subquery in my example is attempting to do this.

    Thanks for the query - I will have a play with it

    EDIT: "Subquery returned more than 1 value"
    George
    Home | Blog

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ended up rolling with my 2nd option and it seems to perform okay at present. The results are accurate which is what matters most for this particular piece of work.

    Pushed out to testing late last week and nobody has found fault yet!
    George
    Home | Blog

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ...just wait till you start having to worry about revision/build numbers. ferinstance for build 123 you have to use the older versions of sub assembly X, which uses some/many different parts (and assemblies) from build 124..... instread the designers should have made build 124 sub assemblies backwards compatable with build 123 and earlier
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about this?
    Are there any exceptional case(sample data) which doesn't produce expected results by this query?

    Code:
    SELECT thing_id
         , supplier
         , component
         , price
     FROM  (SELECT COALESCE(t.thing_id , c.thing_id) AS thing_id
                 , c.supplier
                 , c.component
                 , c.price
                 , ROW_NUMBER()
                      OVER( PARTITION BY COALESCE(t.thing_id , c.thing_id)
                                       , c.supplier
                                       , c.component
                                ORDER BY CASE
                                         WHEN t.thing_id IS NULL THEN
                                              1
                                         ELSE 2
                                         END
                          ) AS priority
             FROM  @components AS c
             LEFT  OUTER JOIN
                   @things     AS t
              ON   c.thing_id = 0
              AND  t.supplier = c.supplier
           )
     WHERE priority = 1
     ORDER BY
           thing_id
         , supplier
         , component
    ;

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Looks good Tonkuma!
    I have rewritten it a touch whilst working out how it comes together... basically I just split out the logic in to two steps.
    Code:
    ; WITH all_components AS (
      SELECT Coalesce(things.thing_id , components.thing_id) As thing_id
           , CASE WHEN things.thing_id IS NULL THEN 1 ELSE 2 END As sequence
           , components.supplier
           , components.component
           , components.price
      FROM   @components As components
       LEFT
        JOIN @things As things
          ON components.thing_id = 0
         AND things.supplier = components.supplier
    )
    , prioritised_components AS (
      SELECT thing_id
           , supplier
           , component
           , price
           , Row_Number() OVER (PARTITION BY thing_id, supplier, component ORDER BY sequence) As priority
      FROM   all_components
    )
    SELECT thing_id
         , supplier
         , component
         , price
    FROM   prioritised_components
    WHERE  priority = 1
     ORDER BY
           thing_id
         , supplier
         , component
    Tests so far show that it is producing the correct results. Will try perf test it against the previous solutions when I can.

    I like the way this works things out.
    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
  •