Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer
    
Thanks a lot Kevin Feasel. This will work fine. I will modify the query to check for results in query 1 that are not in query 2. –  Kinyanjui Kamau Nov 12 '12 at 13:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.