Rollup Selection

Today I have a collection of fictitious vegetable companies.

1. Some companies have multiple branches.
2. All companies and branches have clients.
3. Some clients have a specified favorite vegetable as do some of the branches.

I have been tasked with creating a query that will return the customer’s (client) favorite food. If one is not defined then return the Branch’s favorite, and finally if nothing there is found, return the vegetable the company is known for.

I am using SQL Server 2019 so some of the newer TSQL in SQL 2022 is not available for me to use here.

Here is what my sample data looks like.

Here is the code I used to create my sample table.

CREATE TABLE [dbo].[TestCompany](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[CompanyID] int null,
	[BranchID] int null,
	[ClientID] int null,
	[ColumnValue] [varchar](50) NULL
 CONSTRAINT [PK_TestCompany] PRIMARY KEY CLUSTERED 
(	[ID] ASC) ) ON [PRIMARY]
GO

INSERT INTO TestCompany (CompanyID, BranchID, ClientID, ColumnValue)
VALUES
(101,null,null,'Asparagus'),
(101,101,null,'Beetroot'),
(101,101,104,'Broad beans'),
(101,101,107,'Broccoli'),
(101,101,108,'Brussels sprouts'),
(101,109,null,'Carrots'),
(101,109,114,'Cauliflower'),
(101,109,115,'Celery'),
(101,109,116,'Kale'),
(102,null,null,'Mushrooms'),
(102,123,null,'Peas'),
(102,123,137,'Peppers'),
(102,123,138,'Potatoes'),
(102,123,141,'Spinach'),
(102,154,null,'Sweet potatoes'),
(102,155,170,'Onions'),
(102,155,171,'Winter Squash'),
(102,155,182,'Zucchini');

In order to follow the hierarchy instructions above, I have decided to evaluate the customer, branch, and company record IDs in the WHERE clause, and then use DESC (descending) order and then take the TOP(1) row.

select top (1) *    -- Use TOP(10) to view additional eliminated rows
FROM dbo.TestCompany
WHERE ClientID = (CASE WHEN ClientID = @ClientID THEN @ClientID END)
OR BranchID = (CASE WHEN BranchID = @BranchID AND ClientID IS NULL THEN @BranchID END)
OR CompanyID = (CASE WHEN CompanyID = @CompanyID AND BranchID IS NULL AND ClientID IS NULL THEN @CompanyID END)
ORDER BY ClientID DESC, BranchID DESC, CompanyID DESC;

Now let’s test it out with a scenario where there is ONLY a Company preference defined and no records for our branch or client.

DECLARE @ClientID INT = 120;
DECLARE @BranchID INT = 110;
DECLARE @CompanyID INT = 102;

select top (1) *    -- Use TOP(10) to view additional eliminated rows
FROM dbo.TestCompany
WHERE ClientID = (CASE WHEN ClientID = @ClientID THEN @ClientID END)
OR BranchID = (CASE WHEN BranchID = @BranchID AND ClientID IS NULL THEN @BranchID END)
OR CompanyID = (CASE WHEN CompanyID = @CompanyID AND BranchID IS NULL AND ClientID IS NULL THEN @CompanyID END)
ORDER BY ClientID DESC, BranchID DESC, CompanyID DESC;

Here is my result.

This time I want to test using data where there are multiple records for company, branch and a single record for the client.

DECLARE @ClientID INT = 115;
DECLARE @BranchID INT = 109; 
DECLARE @CompanyID INT = 101;

select top (1) *    -- Use TOP(10) to view additional eliminated rows
FROM dbo.TestCompany
WHERE ClientID = (CASE WHEN ClientID = @ClientID THEN @ClientID END)
OR BranchID = (CASE WHEN BranchID = @BranchID AND ClientID IS NULL THEN @BranchID END)
OR CompanyID = (CASE WHEN CompanyID = @CompanyID AND BranchID IS NULL AND ClientID IS NULL THEN @CompanyID END)
ORDER BY ClientID DESC, BranchID DESC, CompanyID DESC;

As expected, I discover that only the client favorite is returned.

That is it for today.