CODEWITHSUNDEEP
sqloracle-database
Matt
Matt

Reputation: 15061

Eliminate only values that SUM to 0 when meeting a condition

I currently have a query which works perfectly, results are all correct, however a modification is needed to remove unused lines with a certain VOT and matching VONO.

Currently the query brings back a result set like this.

Sample 1

PART_NO TRANSACTION YEAR    QUANTITY    INV_COST    PARTSDESCRIPTION    COMPANY VONO            TEXT        YEAR_PERIOD_KEY ACCOUNT ACC         CODE_B  CODE_B_DESC CODE_C  CODE_C_DESC CODE_F  CODE_F_DESC REFERENCE_NUMBER    VOT     VOTD        AMOUNT  SUPPLIER_ID NAME        LINE_NO OBJECT_CLASS    FOCDESCRIPTION  ROW_NO   
10438   Issue       2015    1           12.39       coat                19      2015000314      Issue       201502          5635    Equipment   426     Floor       16      Operations  null    null        2869                MPL     11-FEB-15   12.39   null        null        null    null            null            2    
null    null        2015    null        null        null                19      2015000314      Automotive  201501          5513    Maintenance 426     Floor       16      Operations  80016   Mobile      MI074755            J       07-JAN-15   77.82   52254       Automotive  null    131100          Mobile          2    
null    Reg         2015    1           76.5        hose                19      2015000314      Register    201502          5490    consumable  426     Floor       16      Operations  E80009  Group       P42085              0       03-FEB-15   76.5    null        null        1       132300          Group           8    
null    null        2015    null        null        null                19      2015000314      Automotive  201501          5513    Maintenance 426     Floor       16      Operations  80016   Mobile      MI074755            J       07-JAN-15   -77.82  52254       Automotive  null    131100          Mobile          4    
null    Reg         2015    4           86.53       battery             19      2015000314      Register    201502          5430    Material    426     Floor       16      Operations  E80005  Running     P42092              0       03-FEB-15   346.12  null        null        1       142100          Runner          10
null    null        2015    null        null        null                19      2015000314      Automotive  201501          5513    Maintenance 426     Floor       16      Operations  80016   Mobile      MI074755            J       07-JAN-15   62.44   52254       Automotive  null    131100          Mobile          4

Now what i want to do is only when VOT = J and the VONO is same and the amounts cancel each other out i want them to not be shown (in this case the two values -77.82 and 77.82 cancel as they are VOT = J and they both are one the same VONO).

This would give a desired output like this:

PART_NO TRANSACTION YEAR    QUANTITY    INV_COST    PARTSDESCRIPTION    COMPANY VONO            TEXT        YEAR_PERIOD_KEY ACCOUNT ACC         CODE_B  CODE_B_DESC CODE_C  CODE_C_DESC CODE_F  CODE_F_DESC REFERENCE_NUMBER    VOT     VOTD        AMOUNT  SUPPLIER_ID NAME        LINE_NO OBJECT_CLASS    FOCDESCRIPTION  ROW_NO   
10438   Issue       2015    1           12.39       coat                19      2015000314      Issue       201502          5635    Equipment   426     Floor       16      Operations  null    null        2869                MPL     11-FEB-15   12.39   null        null        null    null            null            2    
null    Reg         2015    1           76.5        hose                19      2015000314      Register    201502          5490    consumable  426     Floor       16      Operations  E80009  Group       P42085              0       03-FEB-15   76.5    null        null        1       132300          Group           8    
null    Reg         2015    4           86.53       battery             19      2015000314      Register    201502          5430    Material    426     Floor       16      Operations  E80005  Running     P42092              0       03-FEB-15   346.12  null        null        1       142100          Runner          10
null    null        2015    null        null        null                19      2015000314      Automotive  201501          5513    Maintenance 426     Floor       16      Operations  80016   Mobile      MI074755            J       07-JAN-15   62.44   52254       Automotive  null    131100          Mobile          4

Currently the query is this and gives the first output.

SELECT DISTINCT  pt.part_no, pt.TRANSACTION, g.year, pt.quantity, 
(g.amount/pt.quantity) as Inv_Cost, 
case when pc.description is null then p.description else pc.description end as partsdescription, 
g.company, g.vono, g.text, g.year_period_key, g.acc, g.code_b, g.code_b_desc, g.code_c,
g.code_c_desc, g.code_f, g.code_f_desc, g.reference_number, g.vot, g.votd, g.amount, 
s.supplier_id, s.NAME, p.line_no, foc.object_class, foc.description AS focdescription, 
g.row_no
FROM gen g
LEFT OUTER JOIN phist pt ON g.accid = pt.accid  
FULL OUTER JOIN partc pc ON pt.part_no = pc.part_no
LEFT OUTER JOIN pola p ON pt.order_no = p.order_no AND pt.rel = p.line_no 
LEFT OUTER JOIN sia s ON g.party_type_id = s.supplier_id
LEFT OUTER JOIN cust c ON g.party_type_id = c.customer_id 
LEFT OUTER JOIN inv d ON g.COMPANY = d.COMPANY AND g.vono = d.vonore AND g.vot = d.VOUCHER_TYPE_REF
LEFT OUTER JOIN III b ON d.company = b.company AND d.invid = b.invid 
LEFT OUTER JOIN ob fo ON g.code_f = fo.object_id
LEFT OUTER JOIN obcl foc ON fo.object_class = foc.object_class
WHERE g.year = '2015' and g.company = '19'
AND (g.acc > '2999' OR g.acc IN ('1401','1450'))
AND g.code_b IN  ('426', '028', '124', '125') 
AND g.vot != 'MPL'

Update (XQbert)

Ok so here is a (BASIC)SQL Fiddle : http://sqlfiddle.com/#!4/79611a/1/0

What is happening is that it is removing all VOT = J's rather than just the ones that total to 0 per VONO.

Sample Data:

PART_NO  TRANSACTION    YEAR        QUANTITY    INV_COST    PARTSDESCRIPTION    COMPANY VONO         TEXT       YEAR_PERIOD_KEY ACCOUNT  ACC            CODE_B  CODE_B_DESC CODE_C  CODE_C_DESC  CODE_F  CODE_F_DESC    REFERENCE_NUMBER    VOT  VOTD       AMOUNT   SUPPLIER_ID    NAME        LINE_NO  OBJECT_CLASS   FOCDESCRIPTION  ROW_NO 
10438    Issue          2015        1           12.39       coat                19      2015000314   Issue      201502          5635     Equipment      426     Floor       16      Operations   null    null           2869                MPL  11-FEB-15  12.39    null           null        null     null           null            2
null     null           2015        null        null        null                19      2015000314   Automotive 201501          5513     Maintenance    426     Floor       16      Operations   80016   Mobile         MI074755            J    07-JAN-15  77.82    52254          Automotive  null     131100         Mobile          2
null     Reg            2015        1           76.5        hose                19      2015000314   Register   201502          5490     consumable     426     Floor       16      Operations   E80009  Group          P42085              0    03-FEB-15  76.5     null           null        1        132300         Group           8
null     null           2015        null        null        null                19      2015000314   Automotive 201501          5513     Maintenance    426     Floor       16      Operations   80016   Mobile         MI074755            J    07-JAN-15  -77.82   52254          Automotive  null     131100         Mobile          4
null     Reg            2015        4           86.53       battery             19      2015000314   Register   201502          5430     Material       426     Floor       16      Operations   E80005  Running        P42092              0    03-FEB-15  346.12   null           null        1        142100         Runner          10
null     null           2015        null        null        null                19      2015000314   Automotive 201501          5513     Maintenance    426     Floor       16      Operations   80016   Mobile         MI074755            J    07-JAN-15  62.44    52254          Automotive  null     131100         Mobile          4
null     null           2015        null        null        null                19      2015000999   Bike       201507          5699     Parts          426     Floor       16      Operations   89999   fridge         MA864654            J    07-JUL-15  899.84   52254          Bike        null     65222          fridge          4

Output:

PART_NO  TRANSACTION    YEAR        QUANTITY    INV_COST    PARTSDESCRIPTION    COMPANY VONO         TEXT       YEAR_PERIOD_KEY ACCOUNT  ACC            CODE_B  CODE_B_DESC CODE_C  CODE_C_DESC  CODE_F  CODE_F_DESC    REFERENCE_NUMBER    VOT  VOTD       AMOUNT   SUPPLIER_ID    NAME        LINE_NO  OBJECT_CLASS   FOCDESCRIPTION  ROW_NO 
10438    Issue          2015        1           12.39       coat                19      2015000314   Issue      201502          5635     Equipment      426     Floor       16      Operations   null    null           2869                MPL  11-FEB-15  12.39    null           null        null     null           null            2
null     Reg            2015        1           76.5        hose                19      2015000314   Register   201502          5490     consumable     426     Floor       16      Operations   E80009  Group          P42085              0    03-FEB-15  76.5     null           null        1        132300         Group           8
null     Reg            2015        4           86.53       battery             19      2015000314   Register   201502          5430     Material       426     Floor       16      Operations   E80005  Running        P42092              0    03-FEB-15  346.12   null           null        1        142100         Runner          10

Expected Output:

PART_NO  TRANSACTION    YEAR        QUANTITY    INV_COST    PARTSDESCRIPTION    COMPANY VONO         TEXT       YEAR_PERIOD_KEY ACCOUNT  ACC            CODE_B  CODE_B_DESC CODE_C  CODE_C_DESC  CODE_F  CODE_F_DESC    REFERENCE_NUMBER    VOT  VOTD       AMOUNT   SUPPLIER_ID    NAME        LINE_NO  OBJECT_CLASS   FOCDESCRIPTION  ROW_NO 
10438    Issue          2015        1           12.39       coat                19      2015000314   Issue      201502          5635     Equipment      426     Floor       16      Operations   null    null           2869                MPL  11-FEB-15  12.39    null           null        null     null           null            2
null     Reg            2015        1           76.5        hose                19      2015000314   Register   201502          5490     consumable     426     Floor       16      Operations   E80009  Group          P42085              0    03-FEB-15  76.5     null           null        1        132300         Group           8
null     Reg            2015        4           86.53       battery             19      2015000314   Register   201502          5430     Material       426     Floor       16      Operations   E80005  Running        P42092              0    03-FEB-15  346.12   null           null        1        142100         Runner          10
null     null           2015        null        null        null                19      2015000314   Automotive 201501          5513     Maintenance    426     Floor       16      Operations   80016   Mobile         MI074755            J    07-JAN-15  62.44    52254          Automotive  null     131100         Mobile          4
null     null           2015        null        null        null                19      2015000999   Bike       201507          5699     Parts          426     Floor       16      Operations   89999   fridge         MA864654            J    07-JUL-15  899.84   52254          Bike        null     65222          fridge          4

Upvotes: 1

Views: 66

Answers (2)

MT0
MT0

Reputation: 168361

SQL Fiddle

Oracle 11g R2 Schema Setup:

I've added 3 rows to your data - two with +100 amount and one with -100 amount to show that this can handle unequal numbers of positive and negative rows. (and also changed the AMOUNT column to NUMBER(10,2)).

CREATE TABLE gen
    ("PART_NO" varchar2(5), "TRANSACTION" varchar2(5), "YEAR" int, "QUANTITY" varchar2(4), "INV_COST" varchar2(5), "PARTSDESCRIPTION" varchar2(7), "COMPANY" int, "VONO" int, "TEXT" varchar2(10), "YEAR_PERIOD_KEY" int, "ACCOUNT" int, "ACC" varchar2(11), "CODE_B" int, "CODE_B_DESC" varchar2(5), "CODE_C" int, "CODE_C_DESC" varchar2(10), "CODE_F" varchar2(6), "CODE_F_DESC" varchar2(7), "REFERENCE_NUMBER" varchar2(8), "VOT" varchar2(3), "VOTD" varchar2(30), "AMOUNT" NUMBER(10,2), "SUPPLIER_ID" varchar2(5), "NAME" varchar2(10), "LINE_NO" varchar2(4), "OBJECT_CLASS" varchar2(6), "FOCDESCRIPTION" varchar2(6), "ROW_NO" int)
;

INSERT ALL 
    INTO gen ("PART_NO", "TRANSACTION", "YEAR", "QUANTITY", "INV_COST", "PARTSDESCRIPTION", "COMPANY", "VONO", "TEXT", "YEAR_PERIOD_KEY", "ACCOUNT", "ACC", "CODE_B", "CODE_B_DESC", "CODE_C", "CODE_C_DESC", "CODE_F", "CODE_F_DESC", "REFERENCE_NUMBER", "VOT", "VOTD", "AMOUNT", "SUPPLIER_ID", "NAME", "LINE_NO", "OBJECT_CLASS", "FOCDESCRIPTION", "ROW_NO")
         VALUES ('10438', 'Issue', 2015, '1', '12.39', 'coat', 19, 2015000314, 'Issue', 201502, 5635, 'Equipment', 426, 'Floor', 16, 'Operations', NULL, NULL, '2869', 'MPL', '11-Feb-2015 12:00:00 AM', 12.39, NULL, NULL, NULL, NULL, NULL, 2)
    INTO gen ("PART_NO", "TRANSACTION", "YEAR", "QUANTITY", "INV_COST", "PARTSDESCRIPTION", "COMPANY", "VONO", "TEXT", "YEAR_PERIOD_KEY", "ACCOUNT", "ACC", "CODE_B", "CODE_B_DESC", "CODE_C", "CODE_C_DESC", "CODE_F", "CODE_F_DESC", "REFERENCE_NUMBER", "VOT", "VOTD", "AMOUNT", "SUPPLIER_ID", "NAME", "LINE_NO", "OBJECT_CLASS", "FOCDESCRIPTION", "ROW_NO")
         VALUES (NULL, NULL, 2015, NULL, NULL, NULL, 19, 2015000314, 'Automotive', 201501, 5513, 'Maintenance', 426, 'Floor', 16, 'Operations', '80016', 'Mobile', 'MI074755', 'J', '07-Jan-2015 12:00:00 AM', 77.82, '52254', 'Automotive', NULL, '131100', 'Mobile', 2)
    INTO gen ("PART_NO", "TRANSACTION", "YEAR", "QUANTITY", "INV_COST", "PARTSDESCRIPTION", "COMPANY", "VONO", "TEXT", "YEAR_PERIOD_KEY", "ACCOUNT", "ACC", "CODE_B", "CODE_B_DESC", "CODE_C", "CODE_C_DESC", "CODE_F", "CODE_F_DESC", "REFERENCE_NUMBER", "VOT", "VOTD", "AMOUNT", "SUPPLIER_ID", "NAME", "LINE_NO", "OBJECT_CLASS", "FOCDESCRIPTION", "ROW_NO")
         VALUES (NULL, 'Reg', 2015, '1', '76.5', 'hose', 19, 2015000314, 'Register', 201502, 5490, 'consumable', 426, 'Floor', 16, 'Operations', 'E80009', 'Group', 'P42085', '0', '03-Feb-2015 12:00:00 AM', 76.5, NULL, NULL, '1', '132300', 'Group', 8)
    INTO gen ("PART_NO", "TRANSACTION", "YEAR", "QUANTITY", "INV_COST", "PARTSDESCRIPTION", "COMPANY", "VONO", "TEXT", "YEAR_PERIOD_KEY", "ACCOUNT", "ACC", "CODE_B", "CODE_B_DESC", "CODE_C", "CODE_C_DESC", "CODE_F", "CODE_F_DESC", "REFERENCE_NUMBER", "VOT", "VOTD", "AMOUNT", "SUPPLIER_ID", "NAME", "LINE_NO", "OBJECT_CLASS", "FOCDESCRIPTION", "ROW_NO")
         VALUES (NULL, NULL, 2015, NULL, NULL, NULL, 19, 2015000314, 'Automotive', 201501, 5513, 'Maintenance', 426, 'Floor', 16, 'Operations', '80016', 'Mobile', 'MI074755', 'J', '07-Jan-2015 12:00:00 AM', -77.82, '52254', 'Automotive', NULL, '131100', 'Mobile', 4)
    INTO gen ("PART_NO", "TRANSACTION", "YEAR", "QUANTITY", "INV_COST", "PARTSDESCRIPTION", "COMPANY", "VONO", "TEXT", "YEAR_PERIOD_KEY", "ACCOUNT", "ACC", "CODE_B", "CODE_B_DESC", "CODE_C", "CODE_C_DESC", "CODE_F", "CODE_F_DESC", "REFERENCE_NUMBER", "VOT", "VOTD", "AMOUNT", "SUPPLIER_ID", "NAME", "LINE_NO", "OBJECT_CLASS", "FOCDESCRIPTION", "ROW_NO")
         VALUES (NULL, 'Reg', 2015, '4', '86.53', 'battery', 19, 2015000314, 'Register', 201502, 5430, 'Material', 426, 'Floor', 16, 'Operations', 'E80005', 'Running', 'P42092', '0', '03-Feb-2015 12:00:00 AM', 346.12, NULL, NULL, '1', '142100', 'Runner', 10)
    INTO gen ("PART_NO", "TRANSACTION", "YEAR", "QUANTITY", "INV_COST", "PARTSDESCRIPTION", "COMPANY", "VONO", "TEXT", "YEAR_PERIOD_KEY", "ACCOUNT", "ACC", "CODE_B", "CODE_B_DESC", "CODE_C", "CODE_C_DESC", "CODE_F", "CODE_F_DESC", "REFERENCE_NUMBER", "VOT", "VOTD", "AMOUNT", "SUPPLIER_ID", "NAME", "LINE_NO", "OBJECT_CLASS", "FOCDESCRIPTION", "ROW_NO")
         VALUES (NULL, NULL, 2015, NULL, NULL, NULL, 19, 2015000314, 'Automotive', 201501, 5513, 'Maintenance', 426, 'Floor', 16, 'Operations', '80016', 'Mobile', 'MI074755', 'J', '07-Jan-2015 12:00:00 AM', 62.44, '52254', 'Automotive', NULL, '131100', 'Mobile', 4)
    INTO gen ("PART_NO", "TRANSACTION", "YEAR", "QUANTITY", "INV_COST", "PARTSDESCRIPTION", "COMPANY", "VONO", "TEXT", "YEAR_PERIOD_KEY", "ACCOUNT", "ACC", "CODE_B", "CODE_B_DESC", "CODE_C", "CODE_C_DESC", "CODE_F", "CODE_F_DESC", "REFERENCE_NUMBER", "VOT", "VOTD", "AMOUNT", "SUPPLIER_ID", "NAME", "LINE_NO", "OBJECT_CLASS", "FOCDESCRIPTION", "ROW_NO")
         VALUES (NULL, NULL, 2015, NULL, NULL, NULL, 19, 2015000999, 'Bike', 201507, 5699, 'Parts', 426, 'Floor', 16, 'Operations', '89999', 'fridge', 'MA864654', 'J', '07-Jul-2015 12:00:00 AM', 899.84, '52254', 'Bike', NULL, '65222', 'fridge', 4)
    INTO gen ("PART_NO", "TRANSACTION", "YEAR", "QUANTITY", "INV_COST", "PARTSDESCRIPTION", "COMPANY", "VONO", "TEXT", "YEAR_PERIOD_KEY", "ACCOUNT", "ACC", "CODE_B", "CODE_B_DESC", "CODE_C", "CODE_C_DESC", "CODE_F", "CODE_F_DESC", "REFERENCE_NUMBER", "VOT", "VOTD", "AMOUNT", "SUPPLIER_ID", "NAME", "LINE_NO", "OBJECT_CLASS", "FOCDESCRIPTION", "ROW_NO")
         VALUES (NULL, NULL, 2015, NULL, NULL, NULL, 19, 2015000314, 'Automotive', 201501, 5513, 'Maintenance', 426, 'Floor', 16, 'Operations', '80016', 'Mobile', 'MI074755', 'J', '07-Jan-2015 12:00:00 AM', 100.00, '52254', 'Automotive', NULL, '131100', 'Mobile', 4)
    INTO gen ("PART_NO", "TRANSACTION", "YEAR", "QUANTITY", "INV_COST", "PARTSDESCRIPTION", "COMPANY", "VONO", "TEXT", "YEAR_PERIOD_KEY", "ACCOUNT", "ACC", "CODE_B", "CODE_B_DESC", "CODE_C", "CODE_C_DESC", "CODE_F", "CODE_F_DESC", "REFERENCE_NUMBER", "VOT", "VOTD", "AMOUNT", "SUPPLIER_ID", "NAME", "LINE_NO", "OBJECT_CLASS", "FOCDESCRIPTION", "ROW_NO")
         VALUES (NULL, NULL, 2015, NULL, NULL, NULL, 19, 2015000314, 'Automotive', 201501, 5513, 'Maintenance', 426, 'Floor', 16, 'Operations', '80016', 'Mobile', 'MI074755', 'J', '07-Jan-2015 12:00:00 AM', 100.00, '52254', 'Automotive', NULL, '131100', 'Mobile', 4)
    INTO gen ("PART_NO", "TRANSACTION", "YEAR", "QUANTITY", "INV_COST", "PARTSDESCRIPTION", "COMPANY", "VONO", "TEXT", "YEAR_PERIOD_KEY", "ACCOUNT", "ACC", "CODE_B", "CODE_B_DESC", "CODE_C", "CODE_C_DESC", "CODE_F", "CODE_F_DESC", "REFERENCE_NUMBER", "VOT", "VOTD", "AMOUNT", "SUPPLIER_ID", "NAME", "LINE_NO", "OBJECT_CLASS", "FOCDESCRIPTION", "ROW_NO")
         VALUES (NULL, NULL, 2015, NULL, NULL, NULL, 19, 2015000314, 'Automotive', 201501, 5513, 'Maintenance', 426, 'Floor', 16, 'Operations', '80016', 'Mobile', 'MI074755', 'J', '07-Jan-2015 12:00:00 AM', -100.00, '52254', 'Automotive', NULL, '131100', 'Mobile', 4)
SELECT * FROM dual
;

Query 1:

With UnindexedValues AS (
  SELECT * FROM gen
),
IndexedValues AS (
  SELECT v.*,
         ROW_NUMBER() OVER ( PARTITION BY VONO, VOT, AMOUNT ORDER BY VOTD ) AS AmtIdx
  FROM   UnindexedValues v
)
SELECT PART_NO, TRANSACTION, YEAR,
       QUANTITY, INV_COST, PARTSDESCRIPTION,
       COMPANY, VONO, TEXT,
       YEAR_PERIOD_KEY, ACCOUNT, ACC,
       CODE_B, CODE_B_DESC, CODE_C,
       CODE_C_DESC, CODE_F, CODE_F_DESC,
       REFERENCE_NUMBER, VOT, VOTD,
       AMOUNT, SUPPLIER_ID, NAME,
       LINE_NO, OBJECT_CLASS, FOCDESCRIPTION,
       ROW_NO
FROM   IndexedValues i
WHERE  NOT EXISTS (
  SELECT 'X'
  FROM   IndexedValues x
  WHERE  i.VoNo   = x.VoNo
  AND    i.Vot    = x.Vot
  AND    i.Vot    = 'J'
  AND    i.Amount = -x.Amount
  AND    i.AmtIdx = x.AmtIdx
)

Results:

| PART_NO | TRANSACTION | YEAR | QUANTITY | INV_COST | PARTSDESCRIPTION | COMPANY |       VONO |       TEXT | YEAR_PERIOD_KEY | ACCOUNT |         ACC | CODE_B | CODE_B_DESC | CODE_C | CODE_C_DESC | CODE_F | CODE_F_DESC | REFERENCE_NUMBER | VOT |                    VOTD | AMOUNT | SUPPLIER_ID |       NAME | LINE_NO | OBJECT_CLASS | FOCDESCRIPTION | ROW_NO |
|---------|-------------|------|----------|----------|------------------|---------|------------|------------|-----------------|---------|-------------|--------|-------------|--------|-------------|--------|-------------|------------------|-----|-------------------------|--------|-------------|------------|---------|--------------|----------------|--------|
|  (null) |         Reg | 2015 |        1 |     76.5 |             hose |      19 | 2015000314 |   Register |          201502 |    5490 |  consumable |    426 |       Floor |     16 |  Operations | E80009 |       Group |           P42085 |   0 | 03-Feb-2015 12:00:00 AM |   76.5 |      (null) |     (null) |       1 |       132300 |          Group |      8 |
|   10438 |       Issue | 2015 |        1 |    12.39 |             coat |      19 | 2015000314 |      Issue |          201502 |    5635 |   Equipment |    426 |       Floor |     16 |  Operations | (null) |      (null) |             2869 | MPL | 11-Feb-2015 12:00:00 AM |  12.39 |      (null) |     (null) |  (null) |       (null) |         (null) |      2 |
|  (null) |      (null) | 2015 |   (null) |   (null) |           (null) |      19 | 2015000999 |       Bike |          201507 |    5699 |       Parts |    426 |       Floor |     16 |  Operations |  89999 |      fridge |         MA864654 |   J | 07-Jul-2015 12:00:00 AM | 899.84 |       52254 |       Bike |  (null) |        65222 |         fridge |      4 |
|  (null) |      (null) | 2015 |   (null) |   (null) |           (null) |      19 | 2015000314 | Automotive |          201501 |    5513 | Maintenance |    426 |       Floor |     16 |  Operations |  80016 |      Mobile |         MI074755 |   J | 07-Jan-2015 12:00:00 AM |  62.44 |       52254 | Automotive |  (null) |       131100 |         Mobile |      4 |
|  (null) |         Reg | 2015 |        4 |    86.53 |          battery |      19 | 2015000314 |   Register |          201502 |    5430 |    Material |    426 |       Floor |     16 |  Operations | E80005 |     Running |           P42092 |   0 | 03-Feb-2015 12:00:00 AM | 346.12 |      (null) |     (null) |       1 |       142100 |         Runner |     10 |
|  (null) |      (null) | 2015 |   (null) |   (null) |           (null) |      19 | 2015000314 | Automotive |          201501 |    5513 | Maintenance |    426 |       Floor |     16 |  Operations |  80016 |      Mobile |         MI074755 |   J | 07-Jan-2015 12:00:00 AM |    100 |       52254 | Automotive |  (null) |       131100 |         Mobile |      4 |

Upvotes: 2

xQbert
xQbert

Reputation: 35343

Untested as sample data would be too complex...

This simply builds on what you've done. There likely is a simple more elegant way.

What this does is generate a common table expression (CTE) and join join one CTE to another, on VoNo and ensuring both tables have a Vot of J and then negates one of the amounts from a table compairing to the other table if they match, it eliminates them. This would present a problem however if you have more than two amounts with the same VoNo such as an additional amount of -75. It too would be excluded... perhaps not as desired. But if data can't occur this way... no issue.

Initial Response:

With CTE AS (
SELECT DISTINCT  pt.part_no, pt.TRANSACTION, g.year, pt.quantity, 
(g.amount/pt.quantity) as Inv_Cost, 
case when pc.description is null then p.description else pc.description end as partsdescription, 
g.company, g.vono, g.text, g.year_period_key, g.acc, g.code_b, g.code_b_desc, g.code_c,
g.code_c_desc, g.code_f, g.code_f_desc, g.reference_number, g.vot, g.votd, g.amount, 
s.supplier_id, s.NAME, p.line_no, foc.object_class, foc.description AS focdescription, 
g.row_no
FROM gen g
LEFT OUTER JOIN phist pt ON g.accid = pt.accid  
FULL OUTER JOIN partc pc ON pt.part_no = pc.part_no
LEFT OUTER JOIN pola p ON pt.order_no = p.order_no AND pt.rel = p.line_no 
LEFT OUTER JOIN sia s ON g.party_type_id = s.supplier_id
LEFT OUTER JOIN cust c ON g.party_type_id = c.customer_id 
LEFT OUTER JOIN inv d ON g.COMPANY = d.COMPANY AND g.vono = d.vonore AND g.vot = d.VOUCHER_TYPE_REF
LEFT OUTER JOIN III b ON d.company = b.company AND d.invid = b.invid 
LEFT OUTER JOIN ob fo ON g.code_f = fo.object_id
LEFT OUTER JOIN obcl foc ON fo.object_class = foc.object_class
WHERE g.year = '2015' and g.company = '19'
AND (g.acc > '2999' OR g.acc IN ('1401','1450'))
AND g.code_b IN  ('426', '028', '124', '125') 
AND g.vot != 'MPL')

SELECT  * from CTE MINUS
SELECT A.* 
FROM CTE A
INNER JOIN CTE B
 on A.VoNo=B.VoNo
WHERE A.Amount+B.Amount = 0 
and A.Vot='J' and B.Vot='J'

With added union (Putting union then cte union results will not work. CTE has to be first and UNION has to be after the generated CTE but it could be before the 1st select or after the last.

With CTE AS (
SELECT DISTINCT  pt.part_no, pt.TRANSACTION, g.year, pt.quantity, 
(g.amount/pt.quantity) as Inv_Cost, 
case when pc.description is null then p.description else pc.description end as partsdescription, 
g.company, g.vono, g.text, g.year_period_key, g.acc, g.code_b, g.code_b_desc, g.code_c,
g.code_c_desc, g.code_f, g.code_f_desc, g.reference_number, g.vot, g.votd, g.amount, 
s.supplier_id, s.NAME, p.line_no, foc.object_class, foc.description AS focdescription, 
g.row_no
FROM gen g
LEFT OUTER JOIN phist pt ON g.accid = pt.accid  
FULL OUTER JOIN partc pc ON pt.part_no = pc.part_no
LEFT OUTER JOIN pola p ON pt.order_no = p.order_no AND pt.rel = p.line_no 
LEFT OUTER JOIN sia s ON g.party_type_id = s.supplier_id
LEFT OUTER JOIN cust c ON g.party_type_id = c.customer_id 
LEFT OUTER JOIN inv d ON g.COMPANY = d.COMPANY AND g.vono = d.vonore AND g.vot = d.VOUCHER_TYPE_REF
LEFT OUTER JOIN III b ON d.company = b.company AND d.invid = b.invid 
LEFT OUTER JOIN ob fo ON g.code_f = fo.object_id
LEFT OUTER JOIN obcl foc ON fo.object_class = foc.object_class
WHERE g.year = '2015' and g.company = '19'
AND (g.acc > '2999' OR g.acc IN ('1401','1450'))
AND g.code_b IN  ('426', '028', '124', '125') 
AND g.vot != 'MPL')
--Could put your SELECT here
--UNION ALL
SELECT  * from CTE MINUS
SELECT A.* 
FROM CTE A
INNER JOIN CTE B
 on A.VoNo=B.VoNo
WHERE A.Amount+B.Amount = 0 
and A.Vot='J' and B.Vot='J'
--UNION ALL or here...
--YOUR UNION

Upvotes: 2

Related Questions