SQL Query Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL Query Help

Author

Topic

IK1972

56 Posts

Posted - 2012-08-13 : 14:58:56

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

52326 Posts

Posted - 2012-08-13 : 15:04:10

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/

IK1972

56 Posts

Posted - 2012-08-13 : 15:17:27

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-13 : 15:22:52

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/

IK1972

56 Posts

Posted - 2012-08-13 : 16:58:43

Thanks

IK1972

56 Posts

Posted - 2012-08-15 : 13:43:55

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources