Help to rewrite the query - 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)

Help to rewrite the query

Author

Topic

baburk
Posting Yak Master

108 Posts

Posted - 2011-01-25 : 00:08:53

declare @Test TABLE(TestID INT, UserID VARCHAR(50), IsCertified BIT, Allowlogin BIT)

INSERT INTO @Test

SELECT 1, 'baburk', 0, 0 UNION ALL
SELECT 1, 'baburk', 0, 0 UNION ALL
SELECT 1, 'baburk', 0, 0 UNION ALL
SELECT 1, 'baburk', 0, 0 UNION ALL

SELECT 2, 'baburk', 0, 0 UNION ALL
SELECT 2, 'baburk', 0, 0 UNION ALL
SELECT 2, 'baburk', 0, 0 UNION ALL
SELECT 2, 'baburk', 1, 1

The below query returns only if the user didn't pass. Is there is any best way to write the query

SELECT Ti.TestID, Ti.userid, Ti.IsCertified, COUNT(*) tots
FROM @Test AS Ti
WHERE Allowlogin = 0
AND userid = 'baburk'
AND Ti.TestID NOT IN (SELECT Ts.TestID FROM @Test Ts
WHERE Ts.TestID = Ti.TestID AND Ts.Allowlogin = 1)
GROUP BY Ti.TestID ,
Ti.userid ,
Ti.IsCertified
HAVING COUNT(*) > 2

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-25 : 03:38:48

select testid,userid,MAX(Iscertified*1) as Iscertified,COUNT(Allowlogin) as tots from @Test
group by testid,userid
having MAX(case when allowlogin=1 then 1 else 0 end)=0

Madhivanan

Failing to plan is Planning to fail

baburk
Posting Yak Master

108 Posts

Posted - 2011-01-25 : 04:02:02

quote:
Originally posted by madhivanan

select testid,userid,MAX(Iscertified*1) as Iscertified,COUNT(Allowlogin) as tots from @Test
group by testid,userid
having MAX(case when allowlogin=1 then 1 else 0 end)=0

Madhivanan

Failing to plan is Planning to fail

Thanks for your reply.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources