How do I join two group by queries to find an amount difference

[tpbjr]

Hi All,
This one is going to be lengthy but it is necessary to have you fully understand my problem. I have a table with a debit/credit (C/D)indicator and an amount, along with other key fields. I am trying to have a query give back as few records as possible where the the C/D records are different within a given key. Here is some sample data on the table:

TRANS_NO PLA JO TRANS_DT FISCAL_YR FISCAL_PERIOD WEEK_NUMBER D PRIME_NO SUB_NO DEPT_NO POST_AMT OCD_TRANS_NO P MODI
---------- --- -- -------- ---------- ------------- ----------- - ---------- ---------- ---------- ---------- ------------ - ----
LAST_MOD
--------
1144266 001 FT 05/30/05 2005 6 1 D 1370 100 0 4954.6 5055700 N SPCA
08/15/05

1144267 292 FT 05/30/05 2005 6 1 C 1370 100 0 4954.55 5055700 N SPCA
08/15/05

1144268 001 FT 05/30/05 2005 6 1 C 5497 100 60000 .04 5055700 N SPCA
08/15/05

Please forgive the wrapping. Notice the two credits added up is one penny off from the debit record for a given group of records.

I created two separate group by queries that give me what I need separately but keep in mind this example is only 3 records. In production I have thousands so I cannot eye-ball what group is out of balance.

I am thinking that if somehow I can join these two queries together I will get the result that I am looking for or at least a smaller set of records to eye-ball. Please help!

select journal_type, trans_dt, dr_cr_ind,
week_number, ocd_trans_no, sum(post_amt)
from other_journal
where fiscal_yr = 2005
and fiscal_period = 6
and week_number = 1
and dr_cr_ind = 'C'
and ocd_trans_no = 5055700
group by journal_type, trans_dt,
week_number, ocd_trans_no, dr_cr_ind

JO TRANS_DT D WEEK_NUMBER OCD_TRANS_NO SUM(POST_AMT)
-- -------- - ----------- ------------ -------------
FT 05/30/05 C 1 5055700 4954.59



select journal_type, trans_dt, dr_cr_ind,
week_number, ocd_trans_no, sum(post_amt)
from other_journal
where fiscal_yr = 2005
and fiscal_period = 6
and week_number = 1
and dr_cr_ind = 'D'
and ocd_trans_no = 5055700
group by journal_type, trans_dt,
week_number, ocd_trans_no, dr_cr_ind

JO TRANS_DT D WEEK_NUMBER OCD_TRANS_NO SUM(POST_AMT)
-- -------- - ----------- ------------ -------------
FT 05/30/05 D 1 5055700 4954.6

See the penny difference?
Am I correct in thinking that I have to somehow join these two queries together? How can I get the result that I need?
I really appreciate some help.

Thank you for all your help

http://www.besware.com

Tom

 

[tpbjr]

SORRY!
I forgot to mention that in a real situation the OCD_TRANS_NO will not be in the WHERE clause because I will not know what records are out of balance. Thanks again.

Thank you for all your help

http://www.besware.com

Tom

 

[PHV]

Something like this ?
SELECT journal_type, trans_dt, week_number, ocd_trans_no
, SUM(CASE WHEN dr_cr_ind = 'C' THEN post_amt ELSE 0 END) Credit
, SUM(CASE WHEN dr_cr_ind = 'D' THEN 0 ELSE post_amt END) Debit
FROM other_journal
WHERE fiscal_yr = 2005 AND fiscal_period = 6 AND week_number = 1
GROUP BY journal_type, trans_dt, week_number, ocd_trans_no
HAVING SUM(CASE WHEN dr_cr_ind = 'C' THEN post_amt ELSE 0 END) <> SUM(CASE WHEN dr_cr_ind = 'D' THEN 0 ELSE post_amt END)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886