CODEWITHSUNDEEP
sqlsql-serverwhere-clause
jderekc
jderekc

Reputation: 115

Complex filtering SQL Server query with multiple conditions

I have a SQL Server query in which I am joining several tables together and we are using SQL Server 2012. I have a customer number, customer name, item type, a field for document number (which can be one document number in either of the two tables I will mention; even though there are two fields mentioned later, I do want to combine this into one field using a CASE statement), a document amount, document date, due date, amount remaining to be paid, and a description.

The problem is that for my item type, one table (RM20101), doesn't recognize an item type associated with it because no item types are in that table. The other table (Custom_RecData), recognizes the correct item type for all pertinent document numbers but that tables only has a record of document numbers associated with items--which not all document numbers in the system have.

So here's an example of the trimmed down version:

CUSTNMBR    |  ItemType    |   DocumentNumberRM |  DocNumCUSTOM | DocAmount
------------+--------------+--------------------+---------------+-----------
12345ABC    |  NULL        |   PYMNT01234567    |  NULL         |  - 28.50
12345ABC    |  TOYS        |   9010456778       |  9010456778   |   300.00
12345ABC    |  NULL        |   9010456778       |  NULL         |   300.00
12345ABC    |  NULL        |   9019888878       |  NULL         |    47.90
12345ABC    |  CRAFTS      |   9502345671       |  9502345671   |   145.25
12345ABC    |  NULL        |   9502345671       |  NULL         |   145.25

I named the columns in this example for document number as "RM" to come out of the RM20101 table and "CUSTOM" to come out of the Custom_RecDate table.

So I've tried lots of ways, from CASE statements, to my WHERE clause, to a HAVING clause, to subqueries... I can't figure it out. Here's what I'd like to see:

CUSTNMBR    |  ItemType    |   DocumentNumberRM |  DocNumCUSTOM | DocAmount
------------+--------------+--------------------+---------------+-----------
12345ABC    |  NULL        |   PYMNT01234567    |  NULL         |  - 28.50
12345ABC    |  TOYS        |   9010456778       |  9010456778   |   300.00
12345ABC    |  NULL        |   9019888878       |  NULL         |    47.90
12345ABC    |  CRAFTS      |   9502345671       |  9502345671   |   145.25

So why did I call this multiple conditions? Well, if you look at the table, I am cutting out items based on the following:

  1. If both the ItemType And DocNumCUSTOM fields are NULL then show it.
  2. If the ItemType IS NOT NULL and both document number fields are NOT NULL, then show it.
  3. If the ItemType IS NULL and DocNumCUSTOM IS NULL, but we've already seen the document number in the RM20101 DocumentNumberRM field (which I suspect would be in a HAVING clause for a COUNT or something), then don't show it a second time.

If I don't find a way to do step 3, I will get the duplicates as shown in my initial example.

I basically do not want duplication. I want to show an item type whether it be NULL (non-existent for that document number in either table) or show it only once if existent in the Custom_RecDate table, which is the only table that has the item type information in it.

Does this make sense? I know it sounds complicated as heck, but hopefully someone can make sense of it all. :)

Thanks!

By the way, here's the important part (my query, albeit very trimmed down for the example):

SELECT DISTINCT
    R1.CUSTNMBR,
    I.ITMCLSCD AS [ItemType],
    R1.DOCNUMBR AS [DocumentNumberRM],
    I.DOCNUMBR AS [DocNumCUSTOM],
    R1.ORTRXAMT AS [DocAmount]
FROM
    RM20101 R1
JOIN 
    RM40401 R2 ON R2.RMDTYPAL = R1.RMDTYPAL
JOIN 
    RM00401 R3 ON R3.DOCNUMBR = R1.DOCNUMBR
JOIN 
    RM00101 R4 ON R4.CUSTNMBR = R1.CUSTNMBR
LEFT OUTER JOIN 
    SR_ITCMCD C ON C.CUSTNMBR = R1.CUSTNMBR
LEFT OUTER JOIN 
    AR_Description D ON D.SOPNUM = R1.DOCNUMBR
LEFT OUTER JOIN 
    Custom_RecData I ON I.ITEMNMBR = C.ITEMNMBR 
                     AND I.DOCNUMBR = R1.DOCNUMBR

Again, I've tried using CASE statements and putting various conditions in my WHERE clause, I just can't figure it out.

Upvotes: 1

Views: 833

Answers (1)

Aaron Dietz
Aaron Dietz

Reputation: 10277

Purely based on the sample data, MAX() should do the trick:

SELECT
    R1.CUSTNMBR,
    MAX(I.ITMCLSCD) AS [ItemType],
    R1.DOCNUMBR AS [DocumentNumberRM],
    MAX(I.DOCNUMBR) AS [DocNumCUSTOM],
    R1.ORTRXAMT AS [DocAmount]
FROM RM20101 R1
JOIN RM40401 R2 ON R2.RMDTYPAL = R1.RMDTYPAL
JOIN RM00401 R3 ON R3.DOCNUMBR = R1.DOCNUMBR
JOIN RM00101 R4 ON R4.CUSTNMBR = R1.CUSTNMBR
LEFT OUTER JOIN SR_ITCMCD C ON C.CUSTNMBR = R1.CUSTNMBR
LEFT OUTER JOIN AR_Description D ON D.SOPNUM = R1.DOCNUMBR
LEFT OUTER JOIN Custom_RecData I ON I.ITEMNMBR = C.ITEMNMBR 
                                AND I.DOCNUMBR = R1.DOCNUMBR
GROUP BY 
    R1.CUSTNMBR,
    R1.DOCNUMBR,
    R1.ORTRXAMT

Removed the DISTINCT since GROUP BY will accomplish the same and is needed for MAX()

Upvotes: 1

Related Questions