Bit to Boolean Grouping Function

[Meleagant]

All,

I have a table that holds a RuleId, Priority & Status. Status is a bit field.
[tab] -- If the priorities are the same for each RuleId then the RetStatus comparison should be an AND.
[tab] -- If the priorities are different the RetStats comparison should be an OR.

In the below table:
-- Rule 1 would eval to True (True or True)
-- Rule 2 would eval to False (False and True)
-- Rule 3 would eval to True (False or True)

RuleId Priority RetStatus
1      1        1
1      2        1
2      1        0
2      1        1
3      1        0
3      2        1

Is there any way I can accomplish this logic in a query? I was thinking I could do something like:

Select RuleId, isTrue(RetStatus)
From tmpResults
Group By RuleId

I seem to have painted myself into a corner here. Anyone have any idea on how I could evaluate my bits by Group Id?

If you want to play with some test data:

Create Table tmpResults (
	RuleId smallInt
	, Priority smallInt
	, RetStatus bit
)

Insert Into tmpResults 
Select 1, 1, 1

Insert Into tmpResults 
Select 1, 2, 1

Insert Into tmpResults 
Select 2, 1, 0

Insert Into tmpResults 
Select 3, 1, 1

Insert Into tmpResults 
Select 4, 1, 1

Insert Into tmpResults 
Select 4, 1, 0

Insert Into tmpResults 
Select 5, 1, 1

Insert Into tmpResults 
Select 5, 2, 0

Insert Into tmpResults 
Select 6, 1, 1

Insert Into tmpResults 
Select 6, 1, 1

* Sine scientia ars nihil est
* Respondeat superior

 

[gmmastros]

Based on your sample data, can you post the expected results?


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Emtucifor]

SELECT RuleID, Result = Max(AndResult) FROM (
   SELECT RuleID, Priority, AndResult = Min(CASE RetStatus WHEN 1 THEN 1 ELSE 0 END)
   FROM tmpResults
   GROUP BY RuleID, Priority
) X
GROUP BY RuleID

 

[gmmastros]

If I understand the logic correctly....

Select  RuleId, 
        Case When Count(Distinct Priority) = 1
             Then Case When Count(*) = Count(Case When RetStatus = 1 Then 1 End)
                       Then 1
                       Else 0 End
             Else Case When Count(Case When RetStatus = 1 Then 1 End) >= 1 Then 1 Else 0 End
             End As Result
From    tmpResults
Group By RuleId

You could certainly make that complicated case logic in to a function, but functions don't perform as well as inline code. If you do want to make this a function...

Create Function dbo.IsTrue(@RuleId Int)
Returns Bit
As
Begin
	
Return (
 Select Case When Count(Distinct Priority) = 1
             Then Case When Count(*) = Count(Case When RetStatus = 1 Then 1 End)
                       Then 1
                       Else 0 End
			 Else Case When Count(Case When RetStatus = 1 Then 1 End) >= 1 Then 1 Else 0 End
		End As Result From tmpResults Where RuleId = @RuleId)

End

Your query would then be...

Select RuleId, dbo.IsTrue(RuleId) 
From   tmpResults 
Group By RuleId

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Meleagant]

gmmastros thanks for the post!!

When I ran the function you provided it looked like it worked great! But when I posted my sample table I simplified matters. There is actually a PolicyId that I left off of that table. A policy is made up of multiple rules. The RuleId is just a lookup value to another table and should not affect matters that much. (sample table and data is at then end of this post.)

Same logic applies:
[tab]-- If the priorities are the same for each Policy then the RetStatus comparison should be an AND.
[tab]-- If the priorities are different for each Policy then the RetStats comparison should be an OR.

I changed the proc you sent me by just replacing the RuleId with the PolicyId:

Alter Function dbo.IsTrue(@PolicyId Int) Returns Bit As 
	Begin    
	
		Return (Select 
					Case When Count(Distinct PolicyId) = 1 Then 
						Case When Count(*) = Count(Case When RetStatus = 1 Then 1 End) Then 1 Else 0 End             
					Else 
						Case When Count(Case When RetStatus = 1 and Priority = 1 Then 1 End) >= 1 Then 1 Else 0 End        
				End As Result 
				From tmpResults 
				Where PolicyId = @PolicyId)
	End

When I ran the Sql:

Select PolicyId, dbo.IsTrue(PolicyId) From   tmpResults Group By PolicyId

PolicyId came back as 0 (False), but it should actually be true because the Priorities are different, hence the logic should have been an OR condition.

In Oracle I've been able to compare one record of a result set to the prior with the Connect By statement. SQL doesn't have this keyword, but there are CTEs.

Playing with my first CTE I came up with this:

Declare @PolicyId smallInt
Set @PolicyId = 5

Declare @Proiority smallInt
Set @Proiority = 1;

WITH tstCTE (PolicyId, RuleId, Priority, RetStatus, PrevPrior, PrevStatus) AS (


-- Anchor member definition
    SELECT PolicyId, RuleId, Priority, RetStatus, convert(smallint, -1) as PrevPrior, convert(bit, 0) as PrevStatus
    FROM tmpResults
	Where PolicyId = @PolicyId and Priority = @Proiority 
    
    UNION ALL
-- Recursive member definition
    SELECT t.PolicyId, t.RuleId, t.Priority, t.RetStatus, d.Priority, d.RetStatus 
    FROM tmpResults t
		INNER JOIN tstCTE d ON t.PolicyId = d.PolicyId 
			--and t.RuleId = d.RuleId 
			and t.Priority = (d.Priority + 1)
		
)

-- Statement that executes the CTE
SELECT * 
	, Case When PrevPrior = Priority 
		Then RetStatus & PrevStatus 
		Else RetStatus ^ PrevStatus 
	  End as 'T/F'
FROM tstCTE --Where PolicyId = 5
Where PrevPrior > 0

It's not perfect, but I can compare the previous rows Priority to the current row and the Case will perform the AND/OR operation.

I'm limited now to one Policy with two Rules. I would need to change this somehow be more dynamic, but baby steps.

Any ideas? You think I am going down the right path or should just bail on this line of thought?

Updated Sample table:

Create Table tmpResults (
		PolicyId smallInt
		, RuleId smallInt
		, Priority smallInt
		, RetStatus bit

)

Insert Into tmpResults 
Select 1, 1, 1, 1

Insert Into tmpResults 
Select 1, 2, 2, 1

Insert Into tmpResults 
Select 2, 3, 1, 0

Insert Into tmpResults 
Select 3, 4, 1, 1

Insert Into tmpResults 
Select 4, 5, 1, 1

Insert Into tmpResults 
Select 4, 6, 1, 0

Insert Into tmpResults 
Select 5, 7, 1, 1

Insert Into tmpResults 
Select 5, 8, 2, 0

Insert Into tmpResults 
Select 6, 9, 1, 1

Insert Into tmpResults 
Select 6, 10, 1, 1

* Sine scientia ars nihil est
* Respondeat superior

 

[gmmastros]

I encourage you to scroll up to Emtucifor's post. On the sample data provided, our results were the same. It may be easier to modify his code to better suit your modified needs.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Emtucifor]

It *looks* like if you just replace RuleID with PolicyID in my query that it will work. But I can't really tell unless you also show us the expected results, with enough variety in the data that the rules are clear. If it's too simple, then I can't understand the logic since multiple ways of expressing it could achieve the example results.

It's great that you're providing script to create a sample table. Thank you! So few people do that and it really saves time for the people helping you. But in this case we may also need an expected result set.

 

[Emtucifor]

Meleagant, you changed a lot more than just RuleID to PolicyID in George's function... look at it again.

Example:
Case When Count(Distinct PolicyId) = 1 Then

This was originally Priority, not RuleId.