Skip to main content
Database Administrators

Return to Answer

added 1182 characters in body
Source Link
Paul White ♦
Paul White ♦
  • 90.3k
  • 30
  • 424
  • 663

##Alternative

SELECT
    J.JobNumber,
    RC.RateCategoryID
FROM @Job AS J
CROSS APPLY 
(
    -- Just the highest priority match per job
    SELECT TOP (1)
        Match.RateCategoryID
    FROM
    (
        -- Priority 1: Match Managed, Category, and Customer
        SELECT
            RC.RateCategoryID,
            [Priority] = 1
        FROM @RateCategory AS RC
        WHERE
            RC.Managed = J.Managed
            AND RC.Category = J.Category
            AND RC.Customer = J.Customer

        UNION ALL

        -- Priority 2: Match Managed and Category
        SELECT
            RC.RateCategoryID,
            [Priority] = 2
        FROM @RateCategory AS RC
        WHERE
            RC.Managed = J.Managed
            AND RC.Category = J.Category
            AND RC.Customer IS NULL

        UNION ALL

        -- Priority 3: Match Managed
        SELECT
            RC.RateCategoryID,
            [Priority] = 3
        FROM @RateCategory AS RC
        WHERE
            RC.Managed = J.Managed
            AND RC.Category IS NULL
            AND RC.Customer IS NULL
    ) AS Match
    ORDER BY
        Match.[Priority] ASC
) AS RC;

##Alternative

SELECT
    J.JobNumber,
    RC.RateCategoryID
FROM @Job AS J
CROSS APPLY 
(
    -- Just the highest priority match per job
    SELECT TOP (1)
        Match.RateCategoryID
    FROM
    (
        -- Priority 1: Match Managed, Category, and Customer
        SELECT
            RC.RateCategoryID,
            [Priority] = 1
        FROM @RateCategory AS RC
        WHERE
            RC.Managed = J.Managed
            AND RC.Category = J.Category
            AND RC.Customer = J.Customer

        UNION ALL

        -- Priority 2: Match Managed and Category
        SELECT
            RC.RateCategoryID,
            [Priority] = 2
        FROM @RateCategory AS RC
        WHERE
            RC.Managed = J.Managed
            AND RC.Category = J.Category
            AND RC.Customer IS NULL

        UNION ALL

        -- Priority 3: Match Managed
        SELECT
            RC.RateCategoryID,
            [Priority] = 3
        FROM @RateCategory AS RC
        WHERE
            RC.Managed = J.Managed
            AND RC.Category IS NULL
            AND RC.Customer IS NULL
    ) AS Match
    ORDER BY
        Match.[Priority] ASC
) AS RC;
added 306 characters in body
Source Link
Paul White ♦
Paul White ♦
  • 90.3k
  • 30
  • 424
  • 663

Each step only occurs per job if the previous step did not find a match:. The initial CROSS APPLY ensures a row is only returned if one of the steps found a match.

Stack Exchange Data Explorer

###Execution Plan

Plan

Note the Filter operators have startup predicates. This means a seek is only performed if the previous seek did not find a row.

Each step only occurs per job if the previous step did not find a match:

Stack Exchange Data Explorer

Each step only occurs per job if the previous step did not find a match. The initial CROSS APPLY ensures a row is only returned if one of the steps found a match.

Stack Exchange Data Explorer

###Execution Plan

Plan

Note the Filter operators have startup predicates. This means a seek is only performed if the previous seek did not find a row.

Source Link
Paul White ♦
Paul White ♦
  • 90.3k
  • 30
  • 424
  • 663

###Tables

DECLARE @RateCategory AS table
(
    RateCategoryID integer PRIMARY KEY NONCLUSTERED,
    Managed bit NOT NULL,
    Customer varchar(10) NULL,
    Category char(1) NULL,

    UNIQUE CLUSTERED (Managed, Category, Customer)
);

DECLARE @Job AS table
(
    JobNumber integer PRIMARY KEY,
    Managed bit NOT NULL,
    Customer varchar(10) NOT NULL,
    Category char(1) NOT NULL
);

###Sample Data

INSERT @RateCategory
    (RateCategoryID, Managed, Customer, Category)
VALUES
    (10, 0, NULL, NULL),
    (20, 0, NULL, 'A'),
    (30, 0, 'CustC', 'A'),
    (40, 1, 'CustC', 'A'),
    (50, 1, NULL, 'A'),
    (60, 0, NULL, 'B');

INSERT @Job
    (JobNumber, Managed, Customer, Category)
VALUES
    (1, 0, 'CustA', 'C'),
    (2, 0, 'CustB', 'D'),
    (3, 0, 'CustC', 'A'),
    (4, 1, 'CustC', 'A'),
    (5, 0, 'CustD', 'A'),
    (6, 0, 'CustE', 'B'),
    (7, 1, 'CustF', 'A');

###Solution

The logic here is to find a match on:

  1. Managed, Category, and Customer
  2. Managed and Category
  3. Managed alone

Each step only occurs per job if the previous step did not find a match:

SELECT
    J.JobNumber,
    RC.RateCategoryID
FROM @Job AS J
CROSS APPLY 
(
    SELECT
        RateCategoryID =
            COALESCE
            (
                -- Preferred order of matches
                MatchAll.RateCategoryID,
                Match2.RateCategoryID,
                Match1.RateCategoryID
            )
    FROM 
    (
        SELECT NULL
    ) AS Dummy (NotUsed)
    OUTER APPLY
    (
        -- Complete match
        SELECT TOP (1)
            RC.RateCategoryID
        FROM  @RateCategory AS RC
        WHERE
            -- Conditions for this step
            RC.Managed = J.Managed
            AND RC.Category = J.Category
            AND RC.Customer = J.Customer
    ) AS MatchAll
    OUTER APPLY
    (
        -- Match Managed and Category
        SELECT TOP (1)
            RC.RateCategoryID
        FROM  @RateCategory AS RC
        WHERE
            -- No previous match
            MatchAll.RateCategoryID IS NULL
            -- Conditions for this step
            AND RC.Managed = J.Managed
            AND RC.Category = J.Category
            AND RC.Customer IS NULL
    ) AS Match2
    OUTER APPLY
    (
        -- Match Managed only
        SELECT TOP (1)
            RC.RateCategoryID
        FROM  @RateCategory AS RC
        WHERE
            -- No previous match
            MatchAll.RateCategoryID IS NULL
            AND Match2.RateCategoryID IS NULL
            -- Conditions for this step
            AND RC.Managed = J.Managed
            AND RC.Category IS NULL
            AND RC.Customer IS NULL
    ) AS Match1
) AS RC;

###Results

Results

###Demo

Stack Exchange Data Explorer