Simple Query Question - 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

General SQL Server Forums

New to SQL Server Programming

Simple Query Question

Author

Topic

funk.phenomena
Posting Yak Master

121 Posts

Posted - 2013-11-05 : 15:15:11

Hi All !!! I have the following Sales Table

[CODE]
TEL_NUMBER ACTS DEACTS

4165551234 1 0
4165551234 0 -1
4165551234 0 0

4165551235 1 0
4165551235 0 0
4165551235 0 0

[/CODE]
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 ....

[CODE]
TEL_NUMBER ACTS DEACTS

4165551235 1 0
[/CODE]
I'm not quite sure how to do this, Any ideas? Thanks!

[CODE]
SELECT TEL_NUMBER, ACTS, DEACTS FROM SALES T1
WHERE ACTS=1
[/CODE]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-11-05 : 15:29:29

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))

sigmas
Posting Yak Master

172 Posts

Posted - 2013-11-06 : 15:50:41

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;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-07 : 02:54:18

[code]
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
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources