Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Select query to determine unique values


Select query to determine unique values

Author
Message
ollyjolly
ollyjolly
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 27
I am trying to create a query to return certain information, but I am not sure how to get the query to return the information.

I have a table called Prvd which contains information similar to the sample below.

GrpID GrpName PrvID PrvName Spc
1234 Med Associates (blank) (blank) Mixed
1234 Med Associates 6875 Bill Ham Cardiology
1234 Med Associates 3274 Sarah Jones Cardiology
8975 Pulm Associates (blank) (blank) Mixed
5781 Babies Inc. (blank) (blank) Pediatrics
6321 ABC Therapy (blank) (blank) Mixed
6321 ABC Therapy 4925 Tate Silverman Physical Therapy
6321 ABC Therapy 3274 Sarah Jones Occupational Therapy

The column Spc contains either the Specialty of the Group (Grp) if the record has no Provider (Prv), or contains the Specialty of the Provider (Prv).

I am needing the query to do two things

1. Return the Groups (Grp) with a Specialty (Spc) equal to Mixed that have no Providers (Prv)
And
2. Return the Groups (Grp) with a Specialty (Spc) equal to Mixed but where the Providers (Prv) only have one other type of Specialty (Spc).

Here is what the query would return based on the sample data above

GrpID GrpName Spc
1234 Med Associates Cardiology
8975 Pulm Associates Mixed
MyDoggieJessie
MyDoggieJessie
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4911 Visits: 7365
Please post the TSQL that you've come up with thus far

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" ;-)
ollyjolly
ollyjolly
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 27
I do not have anything as I am even sure where to begin. I am a novice with SQL 2008 and this query is more complex.
ollyjolly
ollyjolly
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 27
Can this even be accomplished?

It probably can not be accomplished, but figured it was worth asking.
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5715 Visits: 7660
If you'll take a look at the first link in my signature it'll show you what we'd prefer to be able to help you with coding problems like this. The first step is consumable data and DDL to work from. We're volunteers and usually don't want to rebuild your sample data into something usable.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
ollyjolly
ollyjolly
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 27
Thanks Craig! I will review the information.
ollyjolly
ollyjolly
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 27
Ok I think I did this correct.


--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable

--===== Create the test table with
CREATE TABLE #mytable
(
GrpID INT,
GrpName TEXT,
PrvID INT,
PrvName TEXT,
Spc TEXT,
)

--===== Setup any special required conditions especially where dates are concerned

--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #mytable ON

--===== Insert the test data into the test table
INSERT INTO #mytable
(GrpID, GrpName, PrvID, PrvName, Spc)
SELECT ‘1234’,‘Med Associates’,‘’,‘’,‘Mixed’ UNION ALL
SELECT ‘1234’,‘Med Associates’,‘6875’,‘Bill Ham’,‘Cardiology’ UNION ALL
SELECT ‘1234’,‘Med Associates’,‘3274’,‘Sarah Jones’,‘Cardiology’ UNION ALL
SELECT ‘8975’,‘Pulm Associates’,‘’,‘’,‘Mixed’ UNION ALL
SELECT ‘5781’,‘Babies Inc’,‘’,‘’,‘Pediatrics’ UNION ALL
SELECT ‘6321’,‘ABC Therapy’,‘’,‘’,‘Mixed’ UNION ALL
SELECT ‘6321’,‘ABC Therapy’,‘4925’,‘Tate Silverman’,‘Physical Therapy’ UNION ALL
SELECT ‘6321’,‘ABC Therapy’,‘3274’,‘Sarah Jones’,‘Occupational Therapy’ UNION ALL


--===== Set the identity insert back to normal
SET IDENTITY_INSERT #mytable OFF


lnardozi 61862
lnardozi 61862
SSC-Enthusiastic
SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)

Group: General Forum Members
Points: 136 Visits: 615

GrpID GrpName PrvID PrvName Spc
1234 Med Associates (blank) (blank) Mixed
1234 Med Associates 6875 Bill Ham Cardiology
1234 Med Associates 3274 Sarah Jones Cardiology
8975 Pulm Associates (blank) (blank) Mixed
5781 Babies Inc. (blank) (blank) Pediatrics
6321 ABC Therapy (blank) (blank) Mixed
6321 ABC Therapy 4925 Tate Silverman Physical Therapy
6321 ABC Therapy 3274 Sarah Jones Occupational Therapy

The column Spc contains either the Specialty of the Group (Grp) if the record has no Provider (Prv), or contains the Specialty of the Provider (Prv).

I am needing the query to do two things

1. Return the Groups (Grp) with a Specialty (Spc) equal to Mixed that have no Providers (Prv)
And

select GrpId from #mytable where spc like 'mixed' group by GrpId having max(PrvId) =0


2. Return the Groups (Grp) with a Specialty (Spc) equal to Mixed but where the Providers (Prv) only have one other type of Specialty (Spc).

select GrpId from
(select GrpId,varr=(case when PrvID IS null then 0 when PrvID = '' then 0 else 1 end) from #mytable where spc like 'mixed')a group by GrpId
having sum(varr)=1



ollyjolly
ollyjolly
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 27
Not sure if my last post was correct or not.

I was thinking maybe I could do some sort of Group by and then do a Count of Spc <= 2 to get the data somewhat close to what I need. Any thoughts?
ollyjolly
ollyjolly
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 27
Thank you for the reply.

Do I need to change/replace anything in the code you provided?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search