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
