SQL Server joined tables doesn't filter records as expected-CodePudding

I have 2 SQL Server tables which I'm trying to join and get this expected output but not working quite. The table OECMTHST_SQL contains multiple rows which share the same real_ord_no number also in each of those rows the ord_no number column varies.

OECMTHST_SQL table.

Now the second table OEHDRHST_SQL has ord_no column which can be multiple entries with unique invoice_no column. Note: this invoice_no column is same as the other table's (OECMTHST_SQL) ord_no column.

OEHDRHST_SQL table

Now as you can see in the OECMTHST_SQL table the real_ord_no = 200022 has repeated 7 times but out of that 4 rows share ord_no = 214736 and rest of them share ord_no = 214738.

Now my goal is to get a query that filer out by ord_no that does not have a cmt = PA_INV:SUCCESSFULLY SENT. So as per this example the correct output would be only 214738 and 214739.

Note: PA_INV:SUCCESSFULLY SENT will always be in the cmt_seq_no = 4 as that's the pattern.

Here is the sample dbfiddle for the tables : dbfiddle

Here's what I've tried

Query #1

SELECT
    O.ord_no
FROM
    OECMTHST_SQL O
INNER JOIN
    oehdrhst_sql H ON LTRIM(RTRIM(O.real_ord_no)) = LTRIM(RTRIM(H.ord_no))
WHERE
    O.cmt NOT LIKE 'PA INV%%'
    AND H.ord_type = 'O'
    AND O.cmt LIKE 'Inv_Email%%'
    AND H.entered_dt > '2022-09-29 00:00:00.000'
GROUP BY
    o.ord_no
HAVING
    SUM(cmt_seq_no) < 10
ORDER BY
    O.ord_no

Query #2

SELECT
    O.ord_no,
    SUM(O.cmt_seq_no)
FROM
    OECMTHST_SQL O 
INNER JOIN
    oehdrhst_sql H ON LTRIM(RTRIM(O.real_ord_no)) = LTRIM(RTRIM(H.ord_no))
WHERE
    H.ord_type = 'O'
    AND H.entered_dt > CONVERT(VARCHAR(10), GETDATE()-1, 101)
GROUP BY
    O.ord_no
HAVING
    SUM(O.cmt_seq_no) < 10

None of those queries return my expected output which are 214738 and 214739. Not sure what is wrong in those queries or if it's incomplete. I appreciate all your help and guidance.

CodePudding user response：

Try something like

SELECT
O.ord_no
FROM
    OECMTHST_SQL O
    inner join oehdrhst_sql H ON
        LTRIM(RTRIM(O.real_ord_no)) = LTRIM(RTRIM(H.ord_no))

WHERE
    O.ord_no NOT IN (SELECT ord_no from OECMTHST_SQL WHERE cmt LIKE 'PA_INV%')
    AND
    H.ord_type = 'O'
    AND
    O.cmt LIKE 'Inv_Email%'
    AND
    H.entered_dt >= '2022-09-29 00:00:00.000'

group by
    o.ord_no

having
    sum(cmt_seq_no) < 10

order by
    O.ord_no

I see 2 issues with your approach.

First, you want to filter out rows with the same ord_no as a row where cmt starts with 'PA_INV', not rows where that criteria is true.

Second, all of the dates in your sample data are = '2022-09-29 00:00:00.000', so you need a >= comparison if you want to match them.

Also, the having and group by clauses aren't needed for this query, but I left them in case you need them for your real use case.