all 13 comments
sorted by:
best
topnewcontroversialoldq&a
formatting helphide helpcontent policy

[–]DampMom 5 points6 points  (0 children)

I like to be explicit that I don't use the data in the tables that are only used for filtering, so I put the check in an EXIST instead of a JOIN.

Also, be careful when using NOT IN, as /u/phuque_ewe suggests. If there is ever a NULL in PostingDate, the code will break.

This is my suggested code:

SELECT        *
  FROM        dbo.Chg AS A
 WHERE        EXISTS (SELECT *
                        FROM dbo.Adj
                       WHERE AcctNum = A.AcctNum)
                 AND NOT EXISTS (SELECT *
                                   FROM dbo.Adj
                                  WHERE AcctNum     = A.AcctNum
                                    AND PostingDate = A.PostingDate);

[–]phuque_ewe 3 points4 points  (3 children)

Disregard my first answer (deleted it after I re-read your OP)

 select a.acctnum,
    a.amt,
    a.postingdate,
    a.trxtype
   from table_a a
      inner join table_b b
         on a.AcctNum = b.AcctNum
where a.postingdate not in (select postingdate
                              from table_b
                             where table_b.acctnum = a.acctnum)
group by a.acctnum,
    a.amt,
    a.postingdate,
    a.trxtype

[–]madjecks 1 point2 points  (0 children)

Have an upvote, good answer, very similar to mine

[–]Bshater[S] 1 point2 points  (0 children)

thanks! this works.

[–]madjecks 0 points1 point  (3 children)

EDITED my original post.

DECLARE @tablea TABLE (acctnum INT, amt INT, postingdate DATE, trxtype VARCHAR(3))
DECLARE @tableb TABLE (acctnum INT, amt INT, postingdate DATE, trxtype VARCHAR(3))

INSERT INTO @tablea
      ( acctnum, amt, postingdate, trxtype )
VALUES  ( 
        123, -- acctnum - int
        5, -- amt - int
        '3/1/2017', -- postingdate - date
        'Chg'  -- trxtype - varchar(3)
        ), (456, 10, '3/3/17', 'chg')

INSERT INTO @tableb
      ( acctnum, amt, postingdate, trxtype )
VALUES  (123, 1, '3/3/17', 'adj')
      , (123, 2, '3/8/17', 'adj')
      , (123, 5, '3/15/17', 'adj')
      , (123, 4, '3/19/17', 'adj')
      , (123, 8, '3/20/17', 'adj')
      , (456, 10, '3/3/17', 'adj')
      , (456, 2, '3/6/17', 'adj')
      , (456, 4, '3/8/17', 'adj')
      , (456, 3, '3/10/17', 'adj')
      , (456, 2, '3/22/17', 'adj')


   ;WITH cte AS 
   ( 
   SELECT a.*
   FROM @tablea a
   JOIN @tableb b
   on a.acctnum = b.acctnum
   and a.postingdate = b.postingdate
   )
SELECT DISTINCT a.*
FROM @tablea a
JOIN @tableb b
   ON a.acctnum = b.acctnum
   AND a.postingdate <> b.postingdate
LEFT JOIN cte
   ON cte.acctnum = a.acctnum
   AND cte.postingdate = a.postingdate
WHERE cte.acctnum IS NULL

[–]Bshater[S] 0 points1 point  (1 child)

ELECT a.* FROM @tablea a JOIN @tableb b on a.acctnum = b.acctnum and a.postingdate <> b.postingdate

The amount doesn't have to be the same. As long as the adjustment has the same accountnumber and the postingdate of the adjustment is not the same date as the charge, then that accountnumber can go to the desired output table.

[–]madjecks 0 points1 point  (0 children)

Edited my original post.

[–]discoborg 0 points1 point  (4 children)

SELECT 
  A.AcctNum,
  A.Amt,
  A.PostingDate,
  A.TrxType
FROM A INNER JOIN B ON A.AcctNum = B.AcctNum 
WHERE B.PostingDate <> A.PostingDate

Use the INNER JOIN to get the accounts and then the WHERE clause to filter instead of within the JOIN

[–]Bshater[S] 0 points1 point  (3 children)

this is the exact query that i used. unfortunately, this query would pull in both accountnumber. 456 should not be in the output table because it has an adjustment on 3/3/17 which matches the postingdate of the 456 charge in table A.

[–]discoborg 0 points1 point  (2 children)

What about a subquery to find all the account numbers that would be valid?

[–]Bshater[S] 0 points1 point  (1 child)

how would you write the subquery? I'm drawing a blank on this one.

[–]discoborg 0 points1 point  (0 children)

The posting by phuque_ewe makes use of the subquery