2
\$\begingroup\$

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
\$\endgroup\$
1
  • \$\begingroup\$ Define: "super slow". \$\endgroup\$ – MECU Apr 3 '13 at 14:51
1
\$\begingroup\$

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.

\$\endgroup\$
2
  • \$\begingroup\$ Just one more thing. Is there any way you can select the top record from the resulting set if multiple rows are returned. \$\endgroup\$ – user1768809 Apr 4 '13 at 8:03
  • \$\begingroup\$ Yes. Use the TOP specifier. SELECT TOP 1 ... msdn.microsoft.com/en-us/library/ms189463.aspx \$\endgroup\$ – nickles80 Apr 4 '13 at 19:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.