Posted by octangle on 06/06/06 20:24

This code is attempting to find records that have a RegJrnID that does
not occur more than one time in the table.

The reason that I want to find records with non-duplicated RegJrnID
values is to create "reversal" records for these such that the reversal
record has identical values for every column except the TaxableAmount
which will contain a negative amount. (see: example data below).

/* Set up */

CREATE TABLE t1(RegJrnID INTEGER, InvoiceDate VARCHAR(8), InvoiceNumber
VARCHAR(20), TaxableAmount DECIMAL(32,8))

/* Example data */

INSERT INTO t1 VALUES (1, '20060101', '2321323', 100.00)
INSERT INTO t1 VALUES (9, '20060213', '2130009', 40.01)
INSERT INTO t1 VALUES (3, '20060101', '9402293', 512.44)
INSERT INTO t1 VALUES (1, '20060104', '2321323', -100.00)
INSERT INTO t1 VALUES (4, '20060105', '9302221', 612.12)
INSERT INTO t1 VALUES (5, '20060105', '0003235', 18.11)
INSERT INTO t1 VALUES (6, '20060111', '5953432', 2101.21)
INSERT INTO t1 VALUES (3, '20060111', '9402293', -512.44)
INSERT INTO t1 VALUES (7, '20060115', '4234444', 44.52)
INSERT INTO t1 VALUES (8, '20060115', '0342222', 95.21)
INSERT INTO t1 VALUES (6, '20060119', '5953432', -2101.21)
INSERT INTO t1 VALUES (2, '20060101', '5440033', 231.01)

/* Show what's in the table - just because */

SELECT * FROM t1 ORDER BY RegJrnID, InvoiceDate

/* Query for records to reverse */

SELECT *
FROM t1 a

/* Ignore records that have already been reversed */

WHERE a.RegJrnID != ALL

/* This subselect finds reversed records (i.e. those that have a
duplicate RegJrnID) */

(
SELECT b.RegJrnID
FROM t1 b
GROUP BY b.RegJrnID
HAVING COUNT(*) > 1
)

/* User selection criteria are appended here */

/* AND InvoiceNumber >= '5000000' AND InvoiceNumber <= '7500000' */

/* Make the results look pretty (optional) */

ORDER BY RegJrnID

/* Housekeeping */

DROP TABLE t1

[Back to original message]