Post reply

Conditional where statement

  • November 4, 2014 at 5:57 pm

    #291642

    I'm trying to write a query where ownership equals full or part. I want all of the ownerships that equals full except when there is a ownership that equals part with the same description as the ownership that equals full. I only need to see the record with the ownership part and not the record with the ownership full that has the same description. Any suggestions?

  • November 4, 2014 at 6:10 pm

    #1756643

    Please provide a sample temp table with data that covers all logic cases and your expected output.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • November 4, 2014 at 6:38 pm

    #1756646

    Sorry, I was on my phone when I posted that. This is what the table and data looks like:

    [ID],[Description],[Ownership],[Total]

    '1','Fred','Full','100'

    '2','George','Full','100'

    '3','George','Part','50',

    '4','Bob','Full','100'

    So, when I query this data, I want to pull all Ownership with Full; however, if there is a matching Description with a ownership of Part, I only want the Part record. What I'm currently getting:

    SELECT ID, Description, Ownership, Total

    From Table

    WHERE Ownership = 'Full' or Ownership = 'Part'

    Group by Description

    Results:

    '1','Fred','Full','100'

    '2','George','Full','100'

    '3','George','Part','50',

    '4','Bob','Full','100'

    What I want my results to look like:

    '1','Fred','Full','100'

    '3','George','Part','50',

    '4','Bob','Full','100'

    Please let me know if this doesn't make sense.

    Thanks,

    Jordon

  • November 4, 2014 at 10:30 pm

    #1756653

    Okay, here is a more real life example of what I'm trying to accomplish. Here is my query:

    SELECT C.BASEDPTH, C.OWN, C.SEGLEN, C.SURFWID, C.UNITID, S.DESCRIPT, C.STKEY

    FROM ASSETMANAGEMENT_STREET.COMPSEG C

    INNER JOIN ASSETMANAGEMENT_STREET.STREET S

    ON C.STKEY = S.STKEY

    WHERE C.SEGLEN > '1' AND C.OWN IN ('COF', 'PART') AND C.UNITTYPE NOT IN ('PRIVAT', 'ALLEY')

    AND S.DESCRIPT = 'BOYD MILL PKE' OR S.DESCRIPT = 'YORKTOWN DR'

    From that, I got the following results:

    Notice how Yorktown Dr is all OWN = COF? Well, in that case, I need to add the 3 SEGLEN together to get a total. Looking at the BOYD MILL PKE, notice has it has both OWN = COF and OWN = Part? In the cases where there is a OWN = PART, then I need to add what is in BASEDPTH with what is in SEGLEN for the record that has the same DESCRIPT and SURFWID. So in this example, I would add 2146 to 38.61 for Boyd Mill PKE with an SURFWID = 17 and then add 4332 to 833.89 for Boyd Mill PKE with an SURFWID = 16.

    Is there anyway to do this? I'm also going to put this in an SSRS report, so I don't think that temp tables are an option. Please help!!!

    Jordon

  • November 5, 2014 at 2:36 am

    #1756682

    ;WITH SampleData (BASEDPTH, OWN, SEGLEN, SURFWID, UNITID, DESCRIPT, STKEY) AS (

    SELECT 0, 'COF', 710.14, 30, 'YORKTOWNDR', 'YORKTOWN DR', 4774 UNION ALL

    SELECT 0, 'COF', 349.23, 30, 'YORKTOWNDR', 'YORKTOWN DR', 4774 UNION ALL

    SELECT 0, 'COF', 846.09, 30, 'YORKTOWNDR', 'YORKTOWN DR', 4774 UNION ALL

    SELECT 2146, 'PART', 2165.24, 17, 'BOYDMILLPKE', 'BOYD MILL PKE', 5792 UNION ALL

    SELECT 0, 'COF', 38.61, 17, 'BOYDMILLPKE', 'BOYD MILL PKE', 5792 UNION ALL

    SELECT 4332, 'PART', 11368.82, 16, 'BOYDMILLPKE', 'BOYD MILL PKE', 5792 UNION ALL

    SELECT 0, 'COF', 833.89, 16, 'BOYDMILLPKE', 'BOYD MILL PKE', 5792)

    SELECT

    [NewValue] = SUM(CASE WHEN OWN = 'PART' THEN BASEDPTH ELSE 0 END) + SUM(CASE WHEN OWN = 'COF' THEN seglen ELSE 0 END),

    SURFWID, UNITID, DESCRIPT, STKEY

    FROM SampleData

    GROUP BY UNITID, DESCRIPT, STKEY, SURFWID

    ORDER BY STKEY, SURFWID DESC

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • November 5, 2014 at 6:11 am

    #1756730

    2 ChrisM@Work

    or so

    SELECT

    [NewValue] = SUM(CASE WHEN OWN = 'PART' THEN BASEDPTH

    when OWN = 'COF' then seglen END) ,

    SURFWID, UNITID, DESCRIPT, STKEY

    FROM SampleData

    GROUP BY UNITID, DESCRIPT, STKEY, SURFWID

    ORDER BY STKEY, SURFWID DESC

  • November 5, 2014 at 7:54 am

    #1756760

    Thank you both so much! I greatly appreciate your help!!!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply