|
|
Posted by jsfromynr on 10/02/19 11:51
Hi There,
If the following is your requirement.
"
This code is attempting to find records that have a RegJrnID that does
not occur more than one time in the table.
"
Then
Select * from Yourtable where ID in (Select ID from group by ID having
count(1)=1)
-- Give some order by etc as per your requirement
Output:--
9, '20060213', '2130009', 40.01
4, '20060105', '9302221', 612.12
5, '20060105', '0003235', 18.11
7, '20060115', '4234444', 44.52
8, '20060115', '0342222', 95.21
2, '20060101', '5440033', 231.01
With Warm regards
Jatinder Singh
http://jatindersingh.blogspot.com
octangle wrote:
> 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
Navigation:
[Reply to this message]
|