I have two table one table have Signed Symbol and other one have lower limit and upper limit condition.
What i want is if i passed time as 65 it should return Rule 3.
CREATE TABLE #SignMaster (
SignID INT,
SignSymbol CHAR(2)
);
CREATE TABLE #TimeRules (
RuleID INT,
RuleName VARCHAR(50),
LowerLimit INT,
LowerLimitSignID INT,
UpperLimit INT,
UpperLimitSignID INT
);
INSERT INTO #SignMaster (SignID, SignSymbol)
VALUES
(1, '<'),
(2, '>'),
(3, '<='),
(4, '>=');
INSERT INTO #TimeRules (RuleID, RuleName, LowerLimit, LowerLimitSignID, UpperLimit, UpperLimitSignID)
VALUES
(1, 'Rule 1', 0, 1, 30, 3),
(2, 'Rule 2', 31, 1, 60, 4),
(3, 'Rule 3', 61, 1, 90, 2);
DECLARE @timeInMinutes INT = 65;
SELECT tr.*
FROM #TimeRules tr
INNER JOIN #SignMaster lowerSign ON tr.LowerLimitSignID = lowerSign.SignID
INNER JOIN #SignMaster upperSign ON tr.UpperLimitSignID = upperSign.SignID
WHERE
(
(@timeInMinutes < tr.LowerLimit AND lowerSign.SignSymbol = '<')
OR
(@timeInMinutes <= tr.LowerLimit AND lowerSign.SignSymbol = '<=')
)
AND
(
(@timeInMinutes > tr.UpperLimit AND upperSign.SignSymbol = '>')
OR
(@timeInMinutes >= tr.UpperLimit AND upperSign.SignSymbol = '>=')
);