2 out of 3 selection - 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 2012 Forums

Transact-SQL (2012)

2 out of 3 selection

Author

Topic

tswiss
Starting Member

7 Posts

Posted - 2014-09-03 : 14:41:23

I want to select customers that were present for at least 2 of 3 days (Monday, Tuesday, Wednesday).

I don't care what combination, just that the customers where present at least twice. Below is 3 of 3. How do I modify for any 2 of 3?

WHERE
database.date = Monday
and database.date = Tuesday
and database.date = Wednesday

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-09-03 : 15:44:40

What is the data type of database.date?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tswiss
Starting Member

7 Posts

Posted - 2014-09-03 : 16:27:27

Actual dates like '2014-02-25'

tswiss
Starting Member

7 Posts

Posted - 2014-09-03 : 21:48:57

Actual dates like '2014-02-25'

tswiss
Starting Member

7 Posts

Posted - 2014-09-03 : 22:20:53

datetime

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-09-04 : 12:45:41

This is difficult since we have no idea what your table looks like...

[SoapBox]Stop using reserved words for column names![/SoapBox][CODE]declare @CustomerAttendance table (
CustId int,
date date
)

insert into @CustomerAttendance
values
(1, '20140901'), -- 1 = All 3
(1, '20140902'),
(1, '20140903'),

(2, '20140901'), -- 2 = 2/3
(2, '20140902'),

(3, '20140901'), -- 3 = 1/3
(3, '20140901'), -- Multiple entry of same date

(4, '20140904'), -- 4 = Outside the range
(4, '20140905'),
(4, '20140906')

--/**/select * from @CustomerAttendance

;with UQ_Attendance
as (
select distinct *
from @CustomerAttendance
where [date] between '20140901' and '20140903'
)
select CustID
from UQ_Attendance
group by
CustID
having
count(*) >= 2[/CODE]

Too often we enjoy the comfort of opinion without the discomfort of thought. - John F. Kennedy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources