November 28, 2012 at 5:01 pm
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
November 28, 2012 at 5:19 pm
Please post the TSQL that you've come up with thus far
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
November 28, 2012 at 5:34 pm
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.
November 29, 2012 at 1:55 pm
Can this even be accomplished?
It probably can not be accomplished, but figured it was worth asking.
November 29, 2012 at 1:58 pm
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.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 29, 2012 at 2:01 pm
Thanks Craig! I will review the information.
November 29, 2012 at 2:41 pm
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
November 29, 2012 at 8:44 pm
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
November 29, 2012 at 8:44 pm
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?
November 29, 2012 at 8:50 pm
Thank you for the reply.
Do I need to change/replace anything in the code you provided?
November 30, 2012 at 5:18 am
Can these two pieces of code be combined into one query?
November 30, 2012 at 5:19 am
If it produces the results the results you desire, no.
If not, it may require some tweaking.
It's what you asked for, but is it what you wanted? Only you can answer that one.
November 30, 2012 at 10:13 am
Olly, a question before I work this code.
First, you put in 0-length strings, '', instead of NULLS where teh data is missing for PRVID/PrvName. Are these NULL, or actually blanks?
Also, anyone else trying to work the problem, the script provided is a little non-functional due to special quotes usage and no identity fields. Use the following:
--===== 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,
)
--===== Insert the test data into the test table
INSERT INTO #mytable
(GrpID, GrpName, PrvID, PrvName, Spc)
SELECT '1234','Med Associates',NULL,NULL,'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',NULL,NULL,'Mixed' UNION ALL
SELECT '5781','Babies Inc',NULL,NULL,'Pediatrics' UNION ALL
SELECT '6321','ABC Therapy',NULL,NULL,'Mixed' UNION ALL
SELECT '6321','ABC Therapy','4925','Tate Silverman','Physical Therapy' UNION ALL
SELECT '6321','ABC Therapy','3274','Sarah Jones','Occupational Therapy'
SELECT * FROM #mytable
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 30, 2012 at 4:42 pm
Try this...
IF OBJECT_ID('TempDB..#mytable') IS NOT NULL
DROP TABLE #mytable
CREATE TABLE #mytable
(
ID INT IDENTITY(1,1) NOT NULL
,GrpID INT NULL
,GrpName VARCHAR(50) NULL
,PrvID INT NULL
,PrvName VARCHAR(50) NULL
,Spc VARCHAR(50) NULL
,PRIMARY KEY (ID)
)
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'
SELECT DISTINCT
m.GrpID
,m.GrpName
,m.Spc
FROM
#mytable AS m
INNER JOIN
(
SELECT
GrpID
,COUNT(DISTINCT Spc) AS SpcCount
FROM
#mytable AS countspc
WHERE
ID > 0
GROUP BY
GrpID
) Sub1
ON m.GrpID = Sub1.GrpID
WHERE
(Spc = 'Mixed' AND PrvID = 0 AND SpcCount = 1)
OR (Spc <> 'Mixed' AND SpcCount = 2)
November 30, 2012 at 6:58 pm
First off I want to say Thank You. It is my first time posting to a SQL forum and I am still learning, and appreciate everyones patience.
The values contained in the database are blanks.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply