SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SQL Query Help		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

IK1972
Starting Member

41 Posts
Posted - 08/13/2012 :  14:58:56  Show Profile  Reply with Quote

Hi

I have dataset mentioned below. Operator represent for condition like
for ID 2 Operator 2 means (OR condition) and ID 2 Operator (1, 2, 3) check with AND Condition so my expected result for this data set is ID 1 and 2.

ID Operator Result
1 0 TRUE
2 1 TRUE
2 2 TRUE
2 2 FALSE
2 3 TRUE
2 3 FALSE
3 1 FALSE
3 2 TRUE
3 2 FALSE
3 3 TRUE
3 3 FALSE

Thanks

visakh16
Very Important crosS Applying yaK Herder

India
47173 Posts
Posted - 08/13/2012 :  15:04:10  Show Profile  Reply with Quote
sorry you rule is not clear. can you elaborate on how you will get 1 and 2 in output?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

IK1972
Starting Member

41 Posts
Posted - 08/13/2012 :  15:17:27  Show Profile  Reply with Quote

Ok I can try to explain more.

For ID 1 we have Operator 0 and Result is "True" so this is Qulify for output.

For ID 2 Operator 1 is True
For ID 2 Operator 2 is True and False so when we have same operator ID then we can use (OR) so (True or False) = True
For ID 2 Operator 3 is also True and False so again (True or False) = True

so now we have ID 2 Operator 1 is True and Operator 2 is True and Operator 3 is true so (True AND True AND True) = True so ID 2 is qualify for output.


Now we can check same thing for ID 3.

ID 3 Operator 1 is False
ID 3 Operator 2 is (True OR False) = True
ID 3 Operator 3 is (True OR False) = True

but when now we can check combine 1, 2 and 3 so its (False AND True AND True) so its False so 3 will not qualify for output.

Thanks
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47173 Posts
Posted - 08/13/2012 :  15:22:52  Show Profile  Reply with Quote
ok. here you go

SELECT ID
INTO #Temp
FROM
(
SELECT ID,Operator,
SUM(CASE WHEN Result='TRUE' THEN 1 ELSE -1 END) AS Total
FROM table
GROUP BY ID,Operator
)t
GROUP BY ID
HAVING SUM(CASE WHEN Total<0 THEN 1 ELSE 0 END) =0

SELECT t.*
FROM YourTable t
INNER JOIN #Temp tmp
ON tmp.ID = t.ID


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

IK1972
Starting Member

41 Posts
Posted - 08/13/2012 :  16:58:43  Show Profile  Reply with Quote

Thanks
Go to Top of Page

IK1972
Starting Member

41 Posts
Posted - 08/15/2012 :  13:43:55  Show Profile  Reply with Quote

One Issue with this query. with below data set my expected result is 1,2 and 4 but its only return 1 and 2.

ID Operator Result
1 0 TRUE
2 1 TRUE
2 2 TRUE
2 2 FALSE
2 3 TRUE
2 3 FALSE
3 1 FALSE
3 2 TRUE
3 2 FALSE
3 3 TRUE
3 3 FALSE
4 1 True
4 2 True
4 2 False
4 2 False

Thanks
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000