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
 General SQL Server Forums
 New to SQL Server Programming
 SELECT status in 3,4 and Not in 5
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dhinasql
Posting Yak Master

194 Posts

Posted - 10/08/2013 :  07:19:42  Show Profile  Reply with Quote
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

India
22753 Posts

Posted - 10/08/2013 :  07:21:29  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote

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

Edited by - madhivanan on 10/08/2013 07:26:26
Go to Top of Page

dhinasql
Posting Yak Master

194 Posts

Posted - 10/08/2013 :  07:25:02  Show Profile  Reply with Quote
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.

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/08/2013 :  07:26:07  Show Profile  Reply with Quote

SELECT QueryNumber
from table 
GROUP BY QueryNumber
HAVING MAX(StatusID) IN (3,4)


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

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/08/2013 :  07:27:23  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/08/2013 :  07:28:00  Show Profile  Reply with Quote
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
Go to Top of Page

dhinasql
Posting Yak Master

194 Posts

Posted - 10/08/2013 :  07:31:39  Show Profile  Reply with Quote
Thanks Visakh,
Yes User Will pass it as Parameter to Stored Procedure.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/08/2013 :  07:35:09  Show Profile  Reply with Quote
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
Go to Top of Page

dhinasql
Posting Yak Master

194 Posts

Posted - 10/08/2013 :  07:45:01  Show Profile  Reply with Quote
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.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22753 Posts

Posted - 10/08/2013 :  07:51:06  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote

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
Go to Top of Page

dhinasql
Posting Yak Master

194 Posts

Posted - 10/08/2013 :  08:27:45  Show Profile  Reply with Quote
Thanks madhi, I got it.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/08/2013 :  08:28:44  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30176 Posts

Posted - 10/08/2013 :  17:59:07  Show Profile  Visit SwePeso's Homepage  Reply with Quote
SELECT		QueryNumber
FROM		dbo.Table1
WHERE		StatusID IN (3, 4, 5)
GROUP BY	QueryNumber
HAVING		MAX(StatusID) = 4;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
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.09 seconds. Powered By: Snitz Forums 2000