Span Find and Display - 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 2008 Forums

Transact-SQL (2008)

Span Find and Display

Author

Topic

Virusbot
Starting Member

4 Posts

Posted - 2013-09-19 : 17:31:38

I am trying to figure out the best way to display records that are only related to a criteria.

For Example:

Name | Network | Plan | Effective Date | Term Date
John NET1 PLAN1 DATE DATE
John NET2 PLAN2 DATE DATE
John NET3 PLAN3 DATE DATE

So basically, i only want people with just Net1 and Net2

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-19 : 17:44:39

quote:
Originally posted by Virusbot

I am trying to figure out the best way to display records that are only related to a criteria.

For Example:

Name | Network | Plan | Effective Date | Term Date
John NET1 PLAN1 DATE DATE
John NET2 PLAN2 DATE DATE
John NET3 PLAN3 DATE DATE

So basically, i only want people with just Net1 and Net2

Do you mean you want to get the names of the people who have:

a) both Net1 and Net2, and no other plans such as Net3, Net4 etc.

OR

b) both Net1 and Net2, whether or not they have other plans such as Net3, Net4 etc.

OR

c) at least one of Net1 or Net2 but no other plans such as Net3, Net4 etc.

OR

d) at least one of Net1 or Net2 whether or not they have other plans such as Net3, Net4 etc.

sigmas
Posting Yak Master

172 Posts

Posted - 2013-09-19 : 17:53:21

select * from table_name t
where (select count(*) from table_name where network in ('net1','net2') and name = t.name) = 2
and not exists (select * from table_name where network not in ('net1','net2') and name = t.name);

Virusbot
Starting Member

4 Posts

Posted - 2013-09-19 : 18:14:19

Basically the third Net3 wouldn't be populated, so i am just wanting the two rows and not pull any of the rows with three.

sigmas
Posting Yak Master

172 Posts

Posted - 2013-09-19 : 18:29:43

Post more sample data and wanted result set in a table format.

Virusbot
Starting Member

4 Posts

Posted - 2013-09-19 : 18:44:00

Basically this is a normal record. But I don't want to pull these records.

MEMBERID LNAME FNAME EFF DATE TRM DATE TYOE PLAN
01234568 Doe John 2013-01-01 2013-12-31 HMO CHP
01234568 Doe John 2013-01-01 2013-12-31 PDG STD
01234568 Doe John 2013-01-01 2013-12-31 COM COM

This is the record i am looking for.

MEMBERID LNAME FNAME EFF DATE TRM DATE TYOE PLAN
01234569 Doe Jan 2013-01-01 2013-12-31 HMO CHP
01234569 Doe Jan 2013-01-01 2013-12-31 PDG STD

sigmas
Posting Yak Master

172 Posts

Posted - 2013-09-19 : 18:52:18

You mean this?

WHERE Network IN ('Net1', 'Net2')

Virusbot
Starting Member

4 Posts

Posted - 2013-09-19 : 19:36:31

Kinda, because there multiple spans as show at the top, I have to group by memberid but if there only two spans I only want to show that and not the three spans. I almost think doing count might do the job instead of focusing on the actually networks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources