Select query to determine unique values

  • 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

  • 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

  • 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.

  • Can this even be accomplished?

    It probably can not be accomplished, but figured it was worth asking.

  • 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[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Thanks Craig! I will review the information.

  • 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

  • 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

  • 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?

  • Thank you for the reply.

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

  • Can these two pieces of code be combined into one query?

  • 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.

  • 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


    - 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[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • 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)

     

  • 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