Low level inheriting data from top level and its parent levels

  • I have 5 levels 1-5. All settings are in place in the master table with Level 1.

    If an object is at Level 5 and its parents at level 2, 3 or 4 have changed Level 1 values,

    Level 5 will receive its values from its closest parent ( level4) or it can specify its own values.

    "customized values" ( ie overrides at level 2-4) are in the customized table.

    My query does not yield the expected output. What is missing or is incorrect? Thanks

    DECLARE @MasterTable TABLE (

    MastertableID INT,

    SettingID INT,

    SettingName VARCHAR(50),

    DefaultValue VARCHAR(50),

    LEVEL INT

    )

    -- 3 settings at 5 levels

    INSERT INTO @MasterTable

    VALUES (1, 100, 'setting1', 'default1', 1), (2, 100, 'setting1', 'default1', 2), (3, 100, 'setting1', 'default1', 3), (4, 100, 'setting1', 'default1', 4), (5, 100, 'setting1', 'default1', 5), (6, 200, 'setting2', 'default2', 1), (7, 200, 'setting2', 'default2', 2), (8, 200, 'setting2', 'default2', 3), (9, 200, 'setting2', 'default2', 4), (10, 200, 'setting2', 'default2', 5), (11, 300, 'setting3', 'default3', 1), (11, 300, 'setting3', 'default3', 2), (11, 300, 'setting3', 'default3', 3), (11, 300, 'setting3', 'default3', 4), (11, 300, 'setting3', 'default3', 5)

    DECLARE @CustomizedTable TABLE (

    CustomizedID INT,

    SettingID INT,

    Value VARCHAR(50),

    LEVEL INT,

    OBJECT INT

    )

    INSERT INTO @CustomizedTable

    VALUES (1, 100, 'L3 Setting1', 3, 300), --object 300 is at level 3

    (2, 300, 'L3 Setting3', 3, 300), --object 300 is at level 3

    (3, 300, 'L5 Setting3', 5, 1000) --object 1000 is at level 5 (1000 is related to 300)

    ;WITH CTELevel1

    AS (

    SELECT settingId,

    DefaultValue AS value,

    LEVEL

    FROM @Mastertable

    WHERE LEVEL = 5

    ),

    CTELEvel2

    AS (

    SELECT c1.settingid,

    coalesce(c.value, c1.value) AS Value,

    c1.LEVEL,

    c.LEVEL AS CustomizedLevel,

    c.OBJECT

    FROM cteLevel1 c1

    LEFT JOIN @CustomizedTable c ON c.settingId = C1.Settingid

    )

    SELECT *

    FROM CteLevel2

    Expected output

    settingidValueLEVELCustomizedLevelOBJECT

    100 L3 Setting15 3 300

    200 default25 NULL NULL

    300 L5 Setting35 5 1000

  • Thanks for posting CREATE TABLE and INSERT statements and expected output along with the description. That saves a lot of time!

    The code below returns the expected results for your test data, and I believe that it will also do what you need on all other data:

    WITH CustomizedRanked

    AS (SELECT SettingID,

    Value,

    LEVEL,

    OBJECT,

    ROW_NUMBER() OVER (PARTITION BY SettingID

    ORDER BY LEVEL DESC) AS rn

    FROM @CustomizedTable)

    SELECT mt.SettingID,

    COALESCE(c.Value, mt.DefaultValue) AS Value,

    mt.LEVEL,

    c.LEVEL AS CustomizedLevel,

    c.OBJECT

    FROM @MasterTable AS mt

    LEFT JOIN CustomizedRanked AS c

    ON c.SettingID = mt.SettingID

    AND c.rn = 1

    WHERE mt.LEVEL = 5;


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Hugo,

    Thank you very much for the solution which is much compact and easier to read and follow that what I had.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply