SQL Query based on results of preceding query

Question

I am bashing my head against how to solve this puzzle. Is there any super SQL expert out there who can lend some help

I have a database with the following structure.

adjlevel | scheme | holder | client | companyID | Rate

A rate can be held in this table based on either of the following keys

level + companyId + scheme + holder + client 

OR

level + companyId + scheme + holder

OR

level + companyId + scheme 

OR

level + companyId

There will always be one record per level. Therefore I need to write a query that looks at all the above criteria starting with the most filters first and eliminating one criteria at a time until I find the required record.

I have written a function to do the above but it is very slow.

ALTER FUNCTION [dbo].[Hourly_Rate]
(
-- Add the parameters for the function here
@ncompanyid numeric(2),
@client varchar(100),
@scheme varchar(100),
@holder varchar(100),
@nadjlevel numeric(3),
@date_done datetime
)
RETURNS Numeric(5)
AS
BEGIN
DECLARE @cmode varchar(1),
@level numeric(1),
@counter numeric(1),
@retval Numeric(5)
set @cmode = 'T'
set @level = 1 -- @level controls which filter to apply 
set @counter = 1 -- @counter = loop counter
WHILE @counter <= 9
BEGIN
SELECT @retval = bhrlyrate FROM [MYDATABASE].dbo.inv_hrrate WHERE 
adjlevel = @nadjlevel 
and cmode = @cmode
and companyid = @ncompanyid 
and client = case when @level <= 3 THEN @client ELSE '' END
and scheme = case when @level <= 2 THEN @scheme ELSE '' END
and holder = case when @level <= 1 THEN @holder ELSE '' END
and dworkedfrom <= case when @date_done = '30 december 1899' then dworkedfrom ELSE @date_done END
and dworkedto >= case when @date_done = '30 december 1899' then dworkedto ELSE @date_done END
IF @@rowcount > 0 -- Break if record found 
BREAK;
IF @level = 4 
-- T search mode unsuccessful change to A mode,
-- Reassign a few variables 
--- and continue searching
BEGIN 
SET @cmode = 'A'
SET @level = 0
set @date_done = '30 december 1899'
END
S ET @counter = @counter + 1
SET @level = @level + 1
END 
-- If record is not in hourly rate table then pick default rate from company 
IF @counter = 8 and @retval = 0
SELECT @retval = CO.hrly_rate FROM company CO WHERE companyid = 
@ncompanyid;
RETURN @retval
END

• MECU: Define: "super slow".

Answer 1

I would pull all the possible rates and just sort by the most specific.

SELECT @retval = bhrlyrate FROM [MYDATABASE].dbo.inv_hrrate WHERE 
adjlevel = @nadjlevel 
and cmode = @cmode
and companyid = @ncompanyid 
and (client = @client or client = '')
and (holder = @holder OR (holder = '' AND client = ''))
and (scheme = @scheme or (scheme = '' AND holder = '' AND client = ''))
and dworkedfrom <= case when @date_done = '30 december 1899' then dworkedfrom ELSE @date_done END
and dworkedto >= case when @date_done = '30 december 1899' then dworkedto ELSE @date_done END
ORDER BY scheme DESC, holder DESC, client DESC 

That would eliminate some of the looping and requerying you are doing.

• user1768809: Just one more thing. Is there any way you can select the top record from the resulting set if multiple rows are returned.
• nickles80: Yes. Use the TOP specifier. SELECT TOP 1 ... msdn.microsoft.com/en-us/library/ms189463.aspx