Conditional where statement

Question

Post reply

Conditional where statement

jordon.shaw

Hall of Fame

Points: 3571
More actions
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?

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

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

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

jordon.shaw Hall of Fame Points: 3571 More actions · Answer 2

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

jordon.shaw Hall of Fame Points: 3571 More actions · Answer 3

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

;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

VIG SSC-Addicted Points: 488 More actions · Answer 5

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

jordon.shaw Hall of Fame Points: 3571 More actions · Answer 6

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