SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Create Table tbl(Price DECIMAL(9,2), CompanyId INT, EmployeeId INT, TaskId INT);

Insert Into tbl Values
(100,7199, 1,1),
(200,7199, 1, null),
(220,7199, null,1),
(50, 7199, null, null),
(120, null, null, null);
5 rows affected

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


Price
CompanyId
EmployeeId
TaskId


50.00
7199
null
null


/*
  if we delete the first matched row (where price=50)
*/
delete from tbl where price = 50;

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


Price
CompanyId
EmployeeId
TaskId


120.00
null
null
null


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

select coalesce(
            (select Price from tbl where isnull(CompanyId, 0) = isnull(@CompanyId,0) AND isnull(EmployeeId,0) = isnull(@EmployeeId,0) AND isnull(TaskId,0) = isnull(@TaskId,0)),
            (select Price from tbl where isnull(CompanyId, 0) = isnull(@CompanyId,0)  AND isnull(EmployeeId,0) = isnull(@EmployeeId,0) AND TaskId IS NULL),
            (select Price from tbl where isnull(CompanyId, 0) = isnull(@CompanyId,0) AND EmployeeId IS NULL AND isnull(TaskId,0) = isnull(@TaskId,0)),
            (select Price from tbl where isnull(CompanyId, 0) = isnull(@CompanyId,0) AND EmployeeId IS NULL AND TaskId IS NULL),
            (select Price from tbl where CompanyId IS NULL AND EmployeeId IS NULL AND TaskId IS NULL),
            @DefaultPrice
        )


(No column name)


120.00