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
 Simple Query Question
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

funk.phenomena
Posting Yak Master

Canada
120 Posts

Posted - 11/05/2013 :  15:15:11  Show Profile  Reply with Quote
Hi All !!! I have the following Sales Table


TEL_NUMBER ACTS DEACTS

4165551234  1     0
4165551234  0     -1
4165551234  0     0

4165551235  1     0
4165551235  0     0
4165551235  0     0



AS you can see, there's multiple telephone numbers for each record. I need the query to output results ONLY if there's a "1" but no "-1" for ANY occurrence for that telephone number.

For example, the query out should exclude 4165551234 but include 4165551235 ....


TEL_NUMBER ACTS DEACTS

4165551235  1     0

I'm not quite sure how to do this, Any ideas? Thanks!


SELECT TEL_NUMBER, ACTS, DEACTS FROM SALES T1
WHERE ACTS=1

Edited by - funk.phenomena on 11/05/2013 15:25:46

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 11/05/2013 :  15:29:29  Show Profile  Reply with Quote
Here is one way:
DECLARE @Foo TABLE (TEL_NUMBER BIGINT, ACTS INT, DEACTS INT)
INSERT @Foo VALUES

(4165551234, 1, 0 ),
(4165551234, 0, -1),
(4165551234, 0, 0 ),

(4165551235, 1, 0 ),
(4165551235, 0, 0 ),
(4165551235, 0, 0 )


SELECT 
	TEL_NUMBER
FROM
	@Foo
GROUP BY
	TEL_NUMBER
HAVING COUNT(*) = COUNT(NULLIF(Deacts, -1))

Edited by - Lamprey on 11/05/2013 15:30:40
Go to Top of Page

sigmas
Posting Yak Master

Belarus
172 Posts

Posted - 11/06/2013 :  15:50:41  Show Profile  Reply with Quote
Based on sample data:

SELECT TEL_NUMBER, 1 AS ACTS, 0 AS DEACTS
FROM SALES
GROUP BY TEL_NUMBER
HAVING MAX(ACTS) = 1
AND MIN(DEACTS) = 0;
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/07/2013 :  02:54:18  Show Profile  Reply with Quote

SELECT TEL_NUMBER,ACTS,DEACTS
FROM
(
SELECT 
	MIN(DEACTS) OVER (PARTITION BY TEL_NUMBER ) AS MinDeacts,MAX(ACTS) OVER (PARTITION BY TEL_NUMBER ) MaxActs,*
FROM
	@Foo
)t
WHERE MinDeacts = 0
AND MaxActs = 1


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
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.06 seconds. Powered By: Snitz Forums 2000