Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2013
    Posts
    19

    Unanswered: Guys I need help its urgent

    If a patient has phone number with PrimaryPhone flag set to 1, then this phone number should be returned, otherwise first available phone, if patient doesn't have phone number return null.

    There are some other conditions to be met, based on the conditions I executed the query successfully and now I need the above condition to be included in this query...

    Query:
    SELECT [enh].[PatientPersonID],
    [enh].[MemberNumber],
    [enh].[PromotionGroupID],
    [enh].[EnrollmentID],
    [ph].[AreaCode],
    [ph].[Number],
    [enh].[EnrollmentStartDate],
    [enh].[EnrollmentEndDate],
    [enh].[UpdateDate],
    [enh].[UpdateBy]
    FROM
    [TCPLP].[dbo].[EnrollmentHistory] AS [enh]
    JOIN [TCPLP].[dbo].[Person] AS [p] ON ([p].[PersonID]=[enh].[PatientPersonID])
    JOIN [TCPLP].[dbo].[PhoneNumber] AS [ph] ON ([ph].[PersonId]=[p].[PersonID])
    WHERE (([EnrollmentStatusID] IN ('12', '28', '77')) ) AND [EnrollmentStartDate] BETWEEN ('01-01-2012 00:00:00') AND ('01-08-2013 23:59:59')


    Plzz Can anyone tell me what should be included for this?


    Thanks
    SqlTcard

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ... otherwise first available phone, ...
    How to know "first available"?

  3. #3
    Join Date
    Jan 2013
    Posts
    19

    Available PhoneNumber...

    In fact I too don't know exactly what the available phone number is
    I got the ticket when I have stepped out of the office so couldn't able to contact him regarding that... I didn't find any other phone number in the table apart from the "HOME PHONE" which is combination of two columns "AREA" and "NUMBER".

    Thanks

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Try a subquery to extract either the primary telephone number, or whichever one the database engine decides is "first".
    Code:
    SELECT TOP 1
        dbo.PhoneNumber.PersonID
    ,   dbo.PhoneNumber.Area
    ,   dbo.PhoneNumber.Number
    FROM
        dbo.PhoneNumber
    WHERE
        dbo.PhoneNumber.PrimaryPhone = 1
    OR
        dbo.PhoneNumber.PrimaryPhone IS NULL
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Jan 2013
    Posts
    19

    ...Available Phone...

    There is a column called PrimaryPhoneNumber in the PhoneNumber table which is of bit type '1' n '0' 's. So if the primaryphonenumber is set 1 then get that phone number. If the primaryphonenumber is set 0 then return null

  6. #6
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Please learn to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats (you did not). Code should be in Standard SQL as much as possible and not local dialect.

    If a patient has phone number with “primary_phone_flag” set to 1, then this phone number should be returned, otherwise first available phone, if patient doesn't have phone number return NULL.
    80-95% of the work in SQL is done in the DDL. Let's fix your schema then the query will run better and be easier to write. Since you did not follow Netiquette, we have to guess.

    1. We do not use flags in SQL; that was assembly language.
    2. We do not put audit data in the table begin audited; “update_date” and “update_by” will fail any audit; do you HIPPA? Use third party or MS audit tools.
    3. Phone numbers have an international standard; you can break out the country codes, area codes, etc with computed columns or views. Think international and not local dialect when you build a database.
    4. Table names are plural or collective nouns because they model sets. Particular sets, not generic things like “Person” (only one?? that is what you said!); that is OO thinking in class hierarchies, not RDBMS.
    5. There is a standard pattern for History tables; did you follow it?
    6. There is no such thing as a “<something>_status_id”; it can be a “<something>_status” or “<something>_id”, but not that mutant hybrid. Those are two totally different attribute properties. Do you have a “blood_type_id_value”? Silly when you think about it. Get a book on basic data modeling.


    SELECT ENH.patient_id, ENH.member_number,
    ENH.promotion_group_id, ENH.enrollment_id,
    PH.phone_nbr,
    ENH.enrollment_start_date, ENH.enrollment_end_date
    FROM EnrollmentHistory AS ENH
    LEFT OUTER JOIN
    -- this is the tricky part!
    (SELECT P1.patient_id, P1.phone_priority
    FROM (SELECT patient_id, phone_priority,
    MIN(phone_priority) OVER (PARTITION BY patient_id)
    AS primary_phone_priority
    FROM PhoneNumbers) AS P1
    WHERE P1.phone_priority = P1.primary_phone_priority)
    AS PH(patient_id, phone_priority)

    ON PH.patient_id = ENH.patient_id
    WHERE ENH.enrollment_status IN ('12', '28', '77')
    AND ENH.enrollment_start_date BETWEEN '2012-01-01' AND '2013-01-08';

    The PH derived table returns the patient phone number with the lowest priority number. This means I do not have to set and re-set flags all the time; the DDL does that for me.

    CREATE TABLE PhoneNumbers
    (patient_id CHAR(10) NOT NULL,
    phone_priority SMALLINT DEFAULT 0 NOT NULL
    CHECK (phone_priority >= 0),
    PRIMARY KEY (patient_id, phone_priority),
    phone_nbr CHAR(17) NOT NULL
    CHECK (phone_nbr LIKE '[0-9].. [0-9]'));

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •