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
level + companyId + scheme + holder
level + companyId + scheme
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