Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Select query to determine unique values Expand / Collapse
Author
Message
Posted Wednesday, November 28, 2012 5:01 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 05, 2012 7:14 AM
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





Post #1390205
Posted Wednesday, November 28, 2012 5:19 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 2:53 PM
Points: 3,733, Visits: 7,072
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"
Post #1390209
Posted Wednesday, November 28, 2012 5:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 05, 2012 7:14 AM
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.
Post #1390213
Posted Thursday, November 29, 2012 1:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 05, 2012 7:14 AM
Points: 9, Visits: 27
Can this even be accomplished?

It probably can not be accomplished, but figured it was worth asking.
Post #1390843
Posted Thursday, November 29, 2012 1:58 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 17, 2014 3:16 PM
Points: 5,986, Visits: 6,931
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
Post #1390848
Posted Thursday, November 29, 2012 2:01 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 05, 2012 7:14 AM
Points: 9, Visits: 27
Thanks Craig! I will review the information.
Post #1390850
Posted Thursday, November 29, 2012 2:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 05, 2012 7:14 AM
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

Post #1390868
Posted Thursday, November 29, 2012 8:44 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 10:53 AM
Points: 100, Visits: 487

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

Post #1390983
Posted Thursday, November 29, 2012 8:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 05, 2012 7:14 AM
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?
Post #1390984
Posted Thursday, November 29, 2012 8:50 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 05, 2012 7:14 AM
Points: 9, Visits: 27
Thank you for the reply.

Do I need to change/replace anything in the code you provided?
Post #1390985
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse