SELECT status in 3,4 and Not in 5

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

General SQL Server Forums

New to SQL Server Programming

SELECT status in 3,4 and Not in 5

Author

Topic

dhinasql
Posting Yak Master

195 Posts

Posted - 2013-10-08 : 07:19:42

Friends,
I have transaction log table with QueryNumber, StatusID. So every status change of the Record it will be maintained in this table. Example
TableName: QueryLog
Column Name : ID, QueryNumber, StatusID
Sample Value
1001 | 5001 | 1
1002 | 5001 | 2
1003 | 5001 | 3
1004 | 5001 | 4
1005 | 5001 | 5
1006 | 5002 | 1
1007 | 5002 | 2
1008 | 5002 | 3
1009 | 5002 | 4
1010 | 5003 | 1
1011 | 5003 | 2
1012 | 5003 | 3
1013 | 5003 | 4

I need the Query to Return the Records Status Which is in 3 Or 4 and Not in 5.
Expected Result
QueryNumber
-----------
5002
5003

Looking forward to hear

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-10-08 : 07:21:29

select QueryNumber from table group by QueryNumber
having max(case when StatusID=5 then 1 else 0 end)=0

Madhivanan

Failing to plan is Planning to fail

dhinasql
Posting Yak Master

195 Posts

Posted - 2013-10-08 : 07:25:02

Thank you for your Reply.

NOT IN Value '5' is not fixed value always.

NOT IN value will dynamically change, So sometime i need to check Where STATUSID NOT IN 2.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-08 : 07:26:07

[code]
SELECT QueryNumber
from table
GROUP BY QueryNumber
HAVING MAX(StatusID) IN (3,4)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-08 : 07:27:23

quote:
Originally posted by madhivanan

select QueryNumber from table group by QueryNumber
having max(case when StatusID=5 then 1 else 0 end)=0

Madhivanan

Failing to plan is Planning to fail

this wil still return cases which are currently in Status < 3

add this to sample data and see

1014 | 5004 | 1
1015 | 5004 | 2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-08 : 07:28:00

quote:
Originally posted by dhinasql

Thank you for your Reply.

NOT IN Value '5' is not fixed value always.

NOT IN value will dynamically change, So sometime i need to check Where STATUSID NOT IN 2.

so what determines that value?
is it user passed value as a parameter?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

dhinasql
Posting Yak Master

195 Posts

Posted - 2013-10-08 : 07:31:39

Thanks Visakh,
Yes User Will pass it as Parameter to Stored Procedure.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-08 : 07:35:09

If its a range they pass then you can do like


CREATE PROC YourProc
@MinVal int,
@MaxVal int
AS
SELECT QueryNumber
from table 
GROUP BY QueryNumber
HAVING MAX(StatusID) BETWEEN @MinVal AND @MaxVal
GO

if its single max limit value you can get only all records from start till that

like


CREATE PROC YourProc
@MaxVal int
AS
SELECT QueryNumber
from table 
GROUP BY QueryNumber
HAVING MAX(StatusID) < @MaxVal
GO

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

dhinasql
Posting Yak Master

195 Posts

Posted - 2013-10-08 : 07:45:01

Thanks Visakh,

But i am not looking for Min Value and Max value prospective data retrival.

I need to check the Queries with QueryNumber that is available in One StatusID and Not available in Another StatusID.

StatusID Status
1 | New
2 | Processing by Admin
3 | Processing by Contributor
4 | Closed by Admin
5 | Closed by Contributor
6 | Closed by User
7 | Rejected By Admin

So for my Requirement I always have fixed STATUSID for IN Clause , Ex: STATUSID IN (3,4) BUT the NOT IN Clause will dynamically passed from my .Net programming as a Parameter to SP.

For example : Sometime I need to check
QUERYNUMBER Where STATUSID in (3,4) and NOT IN (2)
QUERYNUMBER Where STATUSID in (3,4) and NOT IN (5)
QUERYNUMBER Where STATUSID in (3,4) and NOT IN (7) etc..

Please let me know if you need more details.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-10-08 : 07:51:06

select QUERYNUMBER from table as t1
where STATUSID in (3,4) and not exists (select * from table as t2 where t1.QUERYNUMBER=t2.QUERYNUMBER and STATUSID in (@statusid))

Madhivanan

Failing to plan is Planning to fail

dhinasql
Posting Yak Master

195 Posts

Posted - 2013-10-08 : 08:27:45

Thanks madhi, I got it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-08 : 08:28:44

quote:
Originally posted by dhinasql

Thanks Visakh,

But i am not looking for Min Value and Max value prospective data retrival.

I need to check the Queries with QueryNumber that is available in One StatusID and Not available in Another StatusID.

StatusID Status
1 | New
2 | Processing by Admin
3 | Processing by Contributor
4 | Closed by Admin
5 | Closed by Contributor
6 | Closed by User
7 | Rejected By Admin

So for my Requirement I always have fixed STATUSID for IN Clause , Ex: STATUSID IN (3,4) BUT the NOT IN Clause will dynamically passed from my .Net programming as a Parameter to SP.

For example : Sometime I need to check
QUERYNUMBER Where STATUSID in (3,4) and NOT IN (2)
QUERYNUMBER Where STATUSID in (3,4) and NOT IN (5)
QUERYNUMBER Where STATUSID in (3,4) and NOT IN (7) etc..

Please let me know if you need more details.


SELECT QUERYNUMBER 
FROM Table
GROUP BY QUERYNUMBER
HAVING COUNT(DISTINCT CASE WHEN StatusID IN (3,4) THEN StatusID END) =2
AND SUM(CASE WHEN StatusID = @StatusID THEN 1 ELSE 0 END) = 0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-10-08 : 17:59:07

[code]SELECT QueryNumber
FROM dbo.Table1
WHERE StatusID IN (3, 4, 5)
GROUP BY QueryNumber
HAVING MAX(StatusID) = 4;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources