Databases
StackOverflow2013
Postsdbo
POQuery to compare 2 table results and filter out certain records
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POQuery to compare 2 table results and filter out certain records
    primarykey
    data
    text
    <p>I need to perform a query whereby I can extract certain document entry numbers for deletion. (SQL Server 2008) The first query gives me the results from a table OINV.</p> <pre><code>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 </code></pre> <p>The second from a table ORIN</p> <pre><code>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 </code></pre> <p><strong>OINV sample results</strong></p> <pre><code>DocEntryINV JrnlMemo 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 </code></pre> <p><strong>ORIN sample results</strong></p> <pre><code>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 </code></pre> <p>For the table OINV, the duplicates arose from a point of sale software that pushed duplicates of invoices to an ERP database table, OINV. A program meant to correct the situation by creating credit notes to nullify Invoices didn't work well, sometimes creating more credit notes than were invoices in the first place. (Invoices number 45,000+ in total). Sometimes credit notes were not created where needed for invoice duplicates.</p> <p>How can I create a query using both tables where I can be able to reverse the extra entries on the ORIN table, if they exist? I need the query to return the results of the DocEntryCN numbers to be reversed such as the following results for the above scenario.</p> <pre><code>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 </code></pre> <p>NB. Comments stores Invoice numbers that are 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.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USKinyanjui Kamau
    1. USKinyanjui Kamau
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POQuery to compare 2 table results and filter out certain records
      1. USKinyanjui Kamau
      1. VTBountyStart
    2. VO
      singulars
      1. POQuery to compare 2 table results and filter out certain records
      1. This table or related slice is empty.
      1. VTUpMod
    1. COseems that you are missing 3 * Rita-99s in the ORIN sample
      singulars
      1. POQuery to compare 2 table results and filter out certain records
      1. USt-clausen.dk
    2. CO@t-clausen.dk I did that just in case the original program correction did not produce any credit notes to correct the original duplication for Rita-99
      singulars
      1. POQuery to compare 2 table results and filter out certain records
      1. USKinyanjui Kamau
    3. COare you sure the column names are correct in OINV sample result ?
      singulars
      1. POQuery to compare 2 table results and filter out certain records
      1. USt-clausen.dk
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload