January 31, 2016 at 10:48 am
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
January 31, 2016 at 1:47 pm
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;
January 31, 2016 at 4:32 pm
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