Relating 2 tables with possible wildcards

0

Please login or register to vote for this query.

(click on this box to dismiss)

Database Administrators

Q&A for database professionals who wish to improve their database skills and learn from others in the community

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
);

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');

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;

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results