need help with case statements with overlapping rules

Article
02/17/2016

Question

_{Wednesday, February 17, 2016 9:56 PM}

I need to assign some description to a set of numbers as follows:

if number between 1 and 5 then set description ='set 1'

if (number between 6 and 9 ) or (number = 3) then then set description ='set 2'

If (number between 11 and 15 ) or (number between 2 and 3) then description = 'set 3'

In the above example number 3 falls in all three sets. How do I do this using sql? I know how to do case statement with non overlapping rules but I am not sure how to make number 3 show up for all three sets and 2 show up for sets 1 and 3

Thanks for you help in advance.

All replies (13)

_{Monday, February 22, 2016 6:32 PM ✅Answered}

It's no less dynamic than the case statements :)

If you created the table (represented by @rules in my solution) you could add rows to it to account for "new" ranges.

For example:

DECLARE @numbers TABLE (number INT)
SET NOCOUNT ON
WHILE (SELECT COUNT(*) FROM @numbers) < 20
BEGIN
  INSERT INTO @numbers (number) VALUES ((SELECT COUNT(*)+1 FROM @numbers))
END

DECLARE @rules TABLE (ruleID INT IDENTITY, ruleLowerBound INT, ruleUpperBound INT, result VARCHAR(10))
INSERT INTO @rules (ruleLowerBound, ruleUpperBound, result) VALUES
(1,  5,  'Set 1'),
(6,  9,  'Set 2'),
(11, 15, 'Set 3'),
(3,  3,  'Set 2'),
(2,  3,  'Set 3')

SET NOCOUNT OFF

;WITH base AS (
SELECT n.number AS inputNumber, ROW_NUMBER() OVER (ORDER BY n.number) AS seq
  FROM @numbers n
    LEFT OUTER JOIN @rules r
      ON n.number BETWEEN r.ruleLowerBound AND r.ruleUpperBound
 WHERE r.result IS NULL
), buildSets AS (
SELECT b.inputNumber, b2.inputNumber AS nextNumber, b3.inputNumber AS previousNumber
  FROM base b
    LEFT OUTER JOIN base b2
      ON b.seq + 1 = b2.seq 
      AND b.inputNumber + 1 = b2.inputNumber
    LEFT OUTER JOIN base b3
      ON b.seq - 1 = b3.seq 
      AND b.inputNumber - 1 = b3.inputNumber
), rCTE AS (
SELECT a.inputNumber, a.nextNumber, a.previousNumber, ROW_NUMBER() OVER (ORDER BY a.inputNumber) AS setOffset, a.inputNumber AS startNumber, a.inputNumber AS endNumber
  FROM buildSets a
 WHERE previousNumber IS NULL
UNION ALL
SELECT a.inputNumber, a.nextNumber, a.previousNumber, r.setOffset, r.startNumber, a.inputNumber
  FROM rCTE r
    INNER JOIN buildSets a
      ON r.nextNumber = a.inputNumber
)

INSERT INTO @rules (ruleLowerBound, ruleUpperBound, result) 
SELECT startNumber AS ruleLowerBound, endNumber AS ruleUpperBound, 'Set ' + CAST((SELECT MAX(CAST(RIGHT(result, LEN(result)-CHARINDEX(' ',result)) AS INT)) FROM @rules)+r.setOffset AS VARCHAR) AS result
  FROM rCTE r
 WHERE inputNumber = (SELECT MAX(inputNumber) FROM rCTE WHERE setOffset = r.setOffset)


SELECT n.number AS inputNumber, r.result AS outputDescription
  FROM @numbers n
    LEFT OUTER JOIN @rules r
      ON n.number BETWEEN r.ruleLowerBound AND r.ruleUpperBound

Now, prior to returning the results, we update the table to make sure it has rules.

We're using fairly simply logic to create the rules here, and you may need to tailor it to exact;y your needs but:

For each set of consecutive numbers, we're going to add a rule which will have the next sequential set name.

For 10 (which was skipped in your deffinition) it creates the rule between 10 and 10, and will have the result Set 4.

For 16-20 it's creating a rule with the result Set 5, for numbers between 16 and 20.

How's that grab you?

Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
Really enjoyed it? See my profile!
My Tech Net Articles.

_{Monday, February 22, 2016 7:29 PM ✅Answered}

The other option then, is to handle this on the DB side. Add a trigger to your data table that fires the 'new rule' script. On insert, if a number is added, a new rule would be generated, and it just becomes a lookup from the cube side.

_{Wednesday, February 17, 2016 10:01 PM}

If multiple WHENs apply, the first one will be used.

DECLARE @table TABLE (number INT)
INSERT INTO @table (number) VALUES
(1),(2),(3),(4),(5),(6),(7),(8),(9)

SELECT *, CASE WHEN number BETWEEN 1 AND 3 THEN 'One'
              WHEN number BETWEEN 4 AND 6 OR number = 3 THEN 'Two'
              WHEN number BETWEEN 7 AND 9 OR number = 3 THEN 'Three'
          END
  FROM @table

_{Wednesday, February 17, 2016 10:12 PM}

Need clarity on what should be returned if the number is 3 and also what if the number is 2?so that we can write the case statements.

Regards

BhanuPrasad M

_{Wednesday, February 17, 2016 10:24 PM}

Please post your input and desired output. It's not clear what exactly do you want to return. Perhaps you want 3 separate case statements and use + statements to get the whole description.

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

My TechNet articles

_{Thursday, February 18, 2016 5:23 AM}

Hi kvd123,

As Naomi N mentioned, please check if the script below meets your requirement.

DECLARE @table TABLE (number INT)
INSERT INTO @table (number) VALUES
(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),
(11),(12),(13),(14),(15),(16)

;with cte as
(
    SELECT number,
        CASE
            WHEN number between 1 and 5 THEN '1'
        END AS [set 1],
        CASE
            WHEN (number between 6 and 9 ) or (number = 3) THEN '2'
        END AS [set 2],
        CASE
            WHEN (number between 11 and 15 ) or (number between 2 and 3) THEN '3'
        END AS [set 3]
    FROM @table
)
SELECT number, STUFF(ISNULL(',' + [set 1], '') + ISNULL(',' + [set 2], '') + ISNULL(',' + [set 3], ''), 1, 1, '') AS [description]
FROM cte

Sam Zha
TechNet Community Support

_{Thursday, February 18, 2016 6:43 AM}

I need to assign some description to a set of numbers as follows:

if number between 1 and 5 then set description ='set 1'

if (number between 6 and 9 ) or (number = 3) then then set description ='set 2'

If (number between 11 and 15 ) or (number between 2 and 3) then description = 'set 3'

In the above example number 3 falls in all three sets. How do I do this using sql? I know how to do case statement with non overlapping rules but I am not sure how to make number 3 show up for all three sets and 2 show up for sets 1 and 3

Thanks for you help in advance.

So what would be output in case of overlap? You just wanted to show all of sets info?

ie like this?

SELECT STUFF(IIF(Number >= 1 AND Number <=5, ',set 1','') + 
IIF(Number >= 6 AND Number <=9 OR Number = 3, ',set 2','') +
IIF(Number >= 11 AND Number <=15 OR Number IN (2,3), ',set 3',''),1,1,'') AS Column,
...

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh

My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

_{Monday, February 22, 2016 2:21 PM}

There will be hundreds of rules like this with numbers overlapping between descriptions. Here is a small example: My input will be numbers from 1 to 15 and output will be as follows:

INPUT NUMBERS OUTPUT DESCRIPTION
1 SET 1
2 SET 1
3 SET 1
4 SET 1
5 SET 1
6 SET 2
7 SET 2
8 SET 2
9 SET 2
3 SET 2
11 SET 3
12 SET 3
13 SET 3
14 SET 3
15 SET 3
2 SET 3
3 SET 3

_{Monday, February 22, 2016 3:09 PM}

If I'm understanding correctly, you want a row for each time it matches a rule?

Then this is not a case, it's a join:

DECLARE @numbers TABLE (number INT)
SET NOCOUNT ON
WHILE (SELECT COUNT(*) FROM @numbers) < 15
BEGIN
  INSERT INTO @numbers (number) VALUES ((SELECT COUNT(*)+1 FROM @numbers))
END

DECLARE @rules TABLE (ruleID INT, ruleLowerBound INT, ruleUpperBound INT, result VARCHAR(10))
INSERT INTO @rules (ruleID, ruleLowerBound, ruleUpperBound, result) VALUES
(1, 1,  5,  'Set 1'),
(2, 6,  9,  'Set 2'),
(3, 11, 15, 'Set 3'),
(4, 3,  3,  'Set 2'),
(5, 2,  3,  'Set 3')

SET NOCOUNT OFF



SELECT n.number AS inputNumber, r.result AS outputDescription
  FROM @numbers n
    INNER JOIN @rules r
      ON n.number BETWEEN r.ruleLowerBound AND r.ruleUpperBound

_{Monday, February 22, 2016 4:27 PM}

Thanks, but this is not a dynamic solution. Some of the rules use number ranges example between 16 and 26 then set 9, but we may currently not have all numbers between 16 and 26 currently in the db. I have to create a a view such that if a new number is introduced from the range, then it should be assigned as per rule in the view. I was writing the view with case statements. My problem is with overlapping rules. so if first case statement puts number 2 and 3 in set 1 and a later case statement puts those in set 3, the numbers 2 and 3 only appear for set 3 not set 1 (the last case statement re-assigns the description assigned in prior case statements. How do I make them appear in both, keeping a dynamic view.

Thanks again for your help.

_{Monday, February 22, 2016 7:10 PM}

This view will work a dimension table for an analysis services cube in (ROLAP mode) and I am not able to see how the "Insert into @Table" would work with that. If the cube measure and dimension were in MOLAP mode, I could have re populated the dimension table before processing the cube, but in my case, it would not involve loading the table and then processing the cube /dimension. So that was my reason for the view rather then the table and defining the rules on the fly.

_{Monday, February 22, 2016 7:16 PM}

So why wouldn't you just do this as part of the cube ETL instead?

When the cube is processed this data will already be handled and available to it.

_{Monday, February 22, 2016 7:21 PM}

There is no ETL. The cuve works on views built on a transnational db, so it can pickup transactions live. No ETL or cube processing is involved.

Connect, code, and grow

Share via

Question

All replies (13)

Share via

need help with case statements with overlapping rules

Question

All replies (13)

Additional resources