Get value from query based on hierarchy logic

Question

I have a table which has a logic with a specific priority for example:

Now based on client, employee and task, I need to find the correct Price using the Priority (this column is not part of the table, I just put it there to describe the logic)

I need to create a query that for example if I send 1, 1, 1, then return Price 100 (which is my first priority), if not found then I need to search with the second priority using 1, 1, null, if found then return Price 200 and so on until priority 5 which is the last one and the Price will be 120. Once I found the Price, I should return the value. I was thinking to use CTE but not sure how fast will be.

I was thinking this but I'm not sure about performance

declare @CompanyId INT = 7199
declare @EmployeeId INT = NULL
declare @TaskId INT = NULL
declare @DefaultPrice DECIMAL (9, 2) = 2

select coalesce(
            (select Price from dbo.MyTable where CompanyId = @CompanyId AND EmployeeId = @EmployeeId AND TaskTypeId = @TaskId),
            (select Price from dbo.MyTable where CompanyId = @CompanyId AND EmployeeId = @EmployeeId AND TaskTypeId IS NULL),
            (select Price from dbo.MyTable where CompanyId = @CompanyId AND EmployeeId IS NULL AND TaskTypeId = @TaskId),
            (select Price from dbo.MyTable where CompanyId = @CompanyId AND EmployeeId IS NULL AND TaskTypeId IS NULL),
            (select Price from dbo.MyTable where CompanyId IS NULL AND EmployeeId IS NULL AND TaskTypeId IS NULL),
            @DefaultPrice
        )

As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. — Dale K, May 06 '23 at 02:10
Please actually provide us with your expected results based on the sample data provided (as formatted text - not images) — Dale K, May 06 '23 at 02:10
Performance is only known when you inspect the execution plan. In general don't worry about trying to optimise for performance until you have a performance issue to solve. SQL doesn't instruct the engine on how to solve an issue, just on the results you want. So just based on your query alone you don't know how it will perform. That said you probably don't want a sub-query per column when you are querying the same table. — Dale K, May 06 '23 at 02:12

abolfazl sadeghi · Answer 1 · 2023-05-05T23:38:33.283

Way 1: you can create Priority and where clause to other paramater

declare @CompanyId INT = 1
declare @EmployeeId INT = NULL
declare @TaskId INT = NULL
declare @DefaultPrice DECIMAL (9, 2) = 2
select *
    from (
    select *,ROW_NUMBER() over( order by  client desc,employee desc,task desc) as rw
    from sh a
)a
where a.rw=1 
and (@CompanyId is null or a.client=@CompanyId)
and (@EmployeeId is null or a.employee=@EmployeeId)
and (@TaskId is null or a.task=@TaskId)

Way 2: First, they check the condition, if it is correct, then they choose one based on the priority

declare @CompanyId INT = 1
declare @EmployeeId INT = NULL
declare @TaskId INT = NULL
declare @DefaultPrice DECIMAL (9, 2) = 2
select *
    from (
    select *,ROW_NUMBER() over( order by  client desc,employee desc,task desc) as rw
    from sh a
    where  (ISNULL( a.client,-1)=ISNULL( @CompanyId ,-1) ) 
       and (ISNULL( a.employee,-1)=ISNULL( @EmployeeId ,-1) )
  and  (ISNULL( a.task,-1)=ISNULL( @TaskId ,-1) ) 
)a 

where rw=1

base data

create table sh( Price int,client int,employee int,task  int)
insert into sh(Price,client,employee,task) values(100,1,1,1)
,( 200,1,1,null),  (220,1,null,1),( 50,1,null,null), (120,null,null,null)

According to the above explanation(your post), it should return information in order of priority. First 100, then 200 and... If it is based on the condition, then what are the priorities used? — abolfazl sadeghi, May 05 '23 at 22:57
yes but the priority should depends on the parameters, if you send 1, null, null then match the price of 50, not 100 — carlosm, May 05 '23 at 23:01
i edited my code,i hope that this is code help you(added Part 2) — abolfazl sadeghi, May 05 '23 at 23:06

score 0 · Accepted Answer · answered May 06 '23 at 06:12

Your query seems to work, but there is a problem with it; you are making a direct comparison between nullable values! and that is not correct i.e. null = null is not true. You may have a look at this post to see how to fix this.

The following is another approach, you may compare its performance with yours, an explanation is included with query comments.

declare @CompanyId INT = 7199;
declare @EmployeeId INT = null;
declare @TaskId INT = null;
declare @DefaultPrice DECIMAL (9, 2) = 2.00;

WITH priorities AS -- define priorities values 
(
  SELECT  @CompanyId c1, 1 c2, 1 c3, 1 priority UNION ALL
  SELECT  @CompanyId, 1, NULL, 2 UNION ALL
  SELECT  @CompanyId, NULL, 1, 3 UNION ALL
  SELECT  @CompanyId, NULL, NULL, 4 UNION ALL
  SELECT NULL, NULL, NULL, 5
),
/* do a self-join to the priorities CTE to get each priority joined with other priorities greater than or equal to it.
i.e. 1 will be joined with (1,2,3,4,5) and 2 with (2,3,4,5) and so on. */
priorities_hierarchy AS 
(
  SELECT p1.c1, p1.c2, p1.c3, 
         p2.c1 Company, p2.c2 Employee, p2.c3 Task, p2.priority
  FROM priorities p1 JOIN priorities p2
  ON p1.priority <= p2.priority
),
/* using a join to the table, we could find all the matched rows from the priorities_hierarchy CTE and the table.
  Then we use row_number to pick only the first matched row based on the first available priority. */
first_matched_price AS
(
  SELECT TB.Price, TB.CompanyId, TB.TaskId, TB.EmployeeId,
    ROW_NUMBER() OVER (PARTITION BY ISNULL(PH.c1, 0), ISNULL(PH.c2, 0), ISNULL(PH.c3, 0) ORDER BY PH.priority) rn
  FROM priorities_hierarchy PH JOIN tbl TB
  ON ISNULL(TB.CompanyId, 0) = ISNULL(PH.Company, 0) AND
     ISNULL(TB.EmployeeId, 0) = ISNULL(PH.Employee, 0) AND
     ISNULL(TB.TaskId, 0) = ISNULL(PH.Task, 0)
  WHERE 
    ISNULL(PH.c1, 0) = ISNULL(@CompanyId, 0) AND
    ISNULL(PH.c2, 0) =  ISNULL(@EmployeeId, 0) AND
    ISNULL(PH.c3, 0) = ISNULL(@TaskId, 0)  
)
SELECT Price, CompanyId, EmployeeId, TaskId
FROM first_matched_price
WHERE rn = 1

See a demo

thank you Ahmed for that, you are right, I forgot and thank you everybody to help me on that! — carlosm, May 06 '23 at 15:54

Get value from query based on hierarchy logic

2 Answers2