Query to compare 2 table results and filter extra records found in a table

Question

I have two queries that I want to compare and get the extra rows found in in the first query that are not in the second plus 1. I need the DocEntryCN to perform some work.

E.g. Query 1

SELECT DocEntry AS "DocEntryCN" , Comments, JrnlMemo, DocTotal
FROM ORIN WHERE DocDate BETWEEN '10-27-2012' AND '10-29-2012' AND Comments IS NOT NULL 
ORDER BY DocTotal ASC, Comments

Query 2

SELECT DocEntry AS "DocEntryINV" , Comments, DocTotal
FROM OINV WHERE DocDate BETWEEN '10-27-2012' AND '10-29-2012' AND Comments IS NOT NULL 
ORDER BY DocTotal ASC, Comments

Sample Results: Query 1

DocEntryCN    Comments    JrnlMemo      DocTotal
67            reverse     kaka-19       500
69            reverse     kaka-19       500
70            reverse     kaka-19       500
71            reverse     kaka-19       500
74            reverse     kaka-19       500
75            reverse     Rob-23        750
77            reverse     Rob-23        750
78            reverse     Rob-23        750
79            reverse     Rob-23        750
84            reverse     Paul-17       1250
86            reverse     Paul-17       1250
87            reverse     Paul-17       1250

Sample Results: Query 2

DocEntryINV   Comments       DocTotal
1             kaka-19        500
3             kaka-19        500
5             kaka-19        500
6             kaka-19        500
7             Rob-23         750
9             Rob-23         750
10            Alex-09        1000
11            Olma-08        1150
12            Paul-17        1250 
13            Paul-17        1250
16            Paul-17        1250
17            Rita-99        1300
19            Rita-99        1300

Desired Results

DocEntryCN    Comments    JrnlMemo      DocTotal
67            reverse     kaka-19       500
69            reverse     kaka-19       500
75            reverse     Rob-23        750
77            reverse     Rob-23        750
78            reverse     Rob-23        750
84            reverse     Paul-17       1250
90            reverse     Rita-99       1300

In the first query, there are 5 duplicates for "kaka-19". In the second query, there are 4 duplicates for the same "kaka-19". I would like my results be as above where (5-4)+1 = 2 for that "kaka-19" and so forth.

NB. Comments in query 2 stores Invoice numbers that should be unique. If any two comments in the OINV table are the same, it means there is a duplicate error. The JrnlMemo field in the ORIN table stores the OINV invoice numbers. For any invoice repetitions no. k, the correct number of credit notes to reverse should be k - 1.

How do I build a query for the desired results?

Answer 1

For my testing, I just created a couple of temporary tables (#CN for ORIN and #INV for OINV). You should be able to replace them with your above queries without too much difficulty.

The following query should be a relatively efficient query to get the results you want:

with cn as
(
    select
        *,
        ROW_NUMBER() over (partition by JrnlMemo order by DocEntryCN desc) as rownum
    from
        #CN
),
inv as
(   
    select
        Comments,
        count(*) as rowstoget
    from
        #INV        
    group by 
        Comments
)
select
    cn.DocEntryCN,
    cn.Comments,
    cn.JrnlMemo,
    cn.DocTotal
from
    cn
    inner join inv on cn.JrnlMemo = inv.Comments
where
    cn.rownum >= inv.rowstoget
order by
    DocEntryCN;

Sample data used:

create table #CN
(
    DocEntryCN int,
    Comments varchar(20),
    JrnlMemo varchar(20),
    DocTotal int
);

insert into #CN(DocEntryCN, Comments, JrnlMemo, DocTotal) values
(67,            'reverse',     'kaka-19',       500),
(69,            'reverse',     'kaka-19',       500),
(70,            'reverse',     'kaka-19',       500),
(71,            'reverse',     'kaka-19',       500),
(74,            'reverse',     'kaka-19',       500),
(75,            'reverse',     'Rob-23',        750),
(77,            'reverse',     'Rob-23',        750),
(78,            'reverse',     'Rob-23',        750),
(79,            'reverse',     'Rob-23',        750),
(84,            'reverse',     'Paul-17',       1250),
(86,            'reverse',     'Paul-17',       1250),
(87,            'reverse',     'Paul-17',       1250)

create table #INV
(
    DocEntryINV int,
    Comments varchar(20),
    DocTotal int
);

insert into #INV(DocEntryINV, Comments, DocTotal) values
(1,             'kaka-19',        500),
(3,             'kaka-19',        500),
(5,             'kaka-19',        500),
(6,             'kaka-19',        500),
(7,             'Rob-23',        750),
(9,             'Rob-23',         750),
(10,            'Alex-09',        1000),
(11,            'Olma-08',        1150),
(12,            'Paul-17',        1250 ),
(13,            'Paul-17',        1250),
(16,            'Paul-17',        1250),
(17,            'Rita-99',        1300),
(19,            'Rita-99',        1300);

I should point out that the results will be slightly different from what you indicated in your Desired Results section because you have a Rita there with a DocEntryCN of 90, but no Rita entries in ORIN. Otherwise, the results are the same.