Solving Query

[CassidyHunt]

I have an accounting need that requires a query that will filter accounting results by a date range then add up every combination of accounts in search of a combination that equals a supplied total.

I haven't started on my own yet. I have an idea of doing a cartesian join on the journal table to any tables rownums for the number of combinations I want. I don't know if that is the best way.

Please advise.

Thanks in advance for the help.

Cassidy

 

[SantaMufasa]

Cassidy,

Is it true that your query needs to check for:

Does Acct1 = X?
Does Acct1+Acct2 = X?
Does Acct1+Acct2+Acct3 = X?
Does Acct1+Acct2+Acct3...etc...+AcctN = X?
Does Acct2 = X?
Does Acct2+Acct3 = X?
Does Acct2_Acct3+Acct4 = X?
Does Acct2_Acct3+Acct4...etc...+AcctN = X?

That is actually way more than a Cartesian Product (it's more like an Artesian Product...i.e. an Olympia Beer "drunken brawl" executionally speaking <grin>).


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[SantaMufasa]

...And I didn't mean to be flippant or dismissive about your need...I just wanted to confirm your specs first.

(BTW, great to see proof of [Cassidy's] life...I haven't heard from you since we had lunch in Salt Lake at Red Iguana a few months back...Hope all is well.)

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[CassidyHunt]

Yeah. Haven't been in Gillette hardly at all or working on Oracle. That is exactly true on the Query above. Your dissmisive. Basically Accounting constantly runs into issues where recievables and payables don't balance and its multiple accounts that make up the issue. when its acct1 = X that is easy. When its acct1 - acct20 = X that is much more challanging.

Hopefully things have been well for you. I still have a few projects I need to talk to you about.

Cassidy

 

[SantaMufasa]

Cassidy,

This is more than a syntax issue...it's more of a logic-efficiency issue.

First of all, how many account amounts are in the "pool" of possible offending values?

You can optimize the query by creating an index on the account amounts, then doing searches where:

1) a single account amount = X,
2) then Acct1 amount < X, and other AcctN = (X - Acct1 amount)
3) then Acct1 + Acct2 <X, and other AcctN = (x - sum(Acct1 + Acct2))
4) et cetera

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[CassidyHunt]

Exactly. Then Basically return the list of account that make up the possible solutions to the problem.

 

[Turkbear]

Hi,
As Santa has pointed out, the scope of this could be huge ( especially if 3 or more account's amounts make up the disparity - the # of combinations would be vast).

I am not sure any single query could handle all the possibilities and even a Stored Proc with mutiple correlated tests could be impossible to make efficient -

It may be time to look into some kind of accounting or auditing software.



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[CassidyHunt]

The way I was thinking about solving that is by limiting the number of accounts by date range and also place a limit of the depth the additions can go. Say the limit would be 5 accounts deep. Most of these issues come from the auditing software that require the investigation. The query I am thinking about is simply try to provide some logical starting points if the descripency found by the auditing software is more difficult that a=b.

 

[CassidyHunt]

keep in mind this example is brute force and ignorance. There has to be better ways of handling this. Let me know your thoughts on it:

Created a view called clh_temp2 that looks like this:

create or replace view clh_temp2 as
  select * from
(select a.account_id col1,  null col2,null col3, null col4, null col5 from 
(select distinct account_id from journal where acct_year = 2009 and acct_period = 3) a
union all
select a.*,  b.*, null, null, null from 
(select distinct account_id from journal where acct_year = 2009 and acct_period = 3) a,
(select distinct account_id from journal where acct_year = 2009 and acct_period = 3) b
union all
select a.*,  b.*, c.*, null, null from 
(select distinct account_id from journal where acct_year = 2009 and acct_period = 3) a,
(select distinct account_id from journal where acct_year = 2009 and acct_period = 3) b,
(select distinct account_id from journal where acct_year = 2009 and acct_period = 3) c
union all
select a.*,  b.*, c.*, d.*, null from 
(select distinct account_id from journal where acct_year = 2009 and acct_period = 3) a,
(select distinct account_id from journal where acct_year = 2009 and acct_period = 3) b,
(select distinct account_id from journal where acct_year = 2009 and acct_period = 3) c,
(select distinct account_id from journal where acct_year = 2009 and acct_period = 3) d
union all
select a.*,  b.*, c.*, d.*, e.* from 
(select distinct account_id from journal where acct_year = 2009 and acct_period = 3) a,
(select distinct account_id from journal where acct_year = 2009 and acct_period = 3) b,
(select distinct account_id from journal where acct_year = 2009 and acct_period = 3) c,
(select distinct account_id from journal where acct_year = 2009 and acct_period = 3) d,
(select distinct account_id from journal where acct_year = 2009 and acct_period = 3) e)

Then I created the code to handle the combinations and sums:

select combo, total  from 
(select distinct a.COL1 as combo,
                (select sum(b.debit_amount + b.credit_amount)
                   from journal b
                  where b.acct_year = 2009
                    and b.acct_period = 3
                    and b.account_id = a.COL1) as total
  from clh_temp2 a
union
select distinct a.COL1 || ',' || a.COL2,
                (select sum(b.debit_amount + b.credit_amount)
                   from journal b
                  where b.acct_year = 2009
                    and b.acct_period = 3
                    and b.account_id = a.COL1) + 
                    (select sum(b.debit_amount + b.credit_amount)
                   from journal b
                  where b.acct_year = 2009
                    and b.acct_period = 3
                    and b.account_id = a.COL2)
  from clh_temp2 a
union
select distinct a.COL1 || ',' || a.COL2 || ',' || a.COL3,
                (select sum(b.debit_amount + b.credit_amount)
                   from journal b
                  where b.acct_year = 2009
                    and b.acct_period = 3
                    and b.account_id = a.COL1) + 
                    (select sum(b.debit_amount + b.credit_amount)
                   from journal b
                  where b.acct_year = 2009
                    and b.acct_period = 3
                    and b.account_id = a.COL2) + 
                    (select sum(b.debit_amount + b.credit_amount)
                   from journal b
                  where b.acct_year = 2009
                    and b.acct_period = 3
                    and b.account_id = a.COL3)
  from clh_temp2 a
union
select distinct a.COL1 || ',' || a.COL2 || ',' || a.COL3 || ',' || a.COL4,
                (select sum(b.debit_amount + b.credit_amount)
                   from journal b
                  where b.acct_year = 2009
                    and b.acct_period = 3
                    and b.account_id = a.COL1) + 
                    (select sum(b.debit_amount + b.credit_amount)
                   from journal b
                  where b.acct_year = 2009
                    and b.acct_period = 3
                    and b.account_id = a.COL2) + 
                    (select sum(b.debit_amount + b.credit_amount)
                   from journal b
                  where b.acct_year = 2009
                    and b.acct_period = 3
                    and b.account_id = a.COL3) + 
                    (select sum(b.debit_amount + b.credit_amount)
                   from journal b
                  where b.acct_year = 2009
                    and b.acct_period = 3
                    and b.account_id = a.COL4)
  from clh_temp2 a
union
select distinct a.COL1 || ',' || a.COL2 || ',' || a.COL3 || ',' || a.COL4 || ',' || a.COL5,
                (select sum(b.debit_amount + b.credit_amount)
                   from journal b
                  where b.acct_year = 2009
                    and b.acct_period = 3
                    and b.account_id = a.COL1) + 
                    (select sum(b.debit_amount + b.credit_amount)
                   from journal b
                  where b.acct_year = 2009
                    and b.acct_period = 3
                    and b.account_id = a.COL2) + 
                    (select sum(b.debit_amount + b.credit_amount)
                   from journal b
                  where b.acct_year = 2009
                    and b.acct_period = 3
                    and b.account_id = a.COL3) + 
                    (select sum(b.debit_amount + b.credit_amount)
                   from journal b
                  where b.acct_year = 2009
                    and b.acct_period = 3
                    and b.account_id = a.COL4) + 
                    (select sum(b.debit_amount + b.credit_amount)
                   from journal b
                  where b.acct_year = 2009
                    and b.acct_period = 3
                    and b.account_id = a.COL5)
  from clh_temp2 a)
  where round(total,0) = &value

No I didn't run it because I know it would be very intense. But I think it illustrates the concept.

Let me know.

Thanks

Cassidy

 

[hoinz]

Hello Cassidy,

a lot of mathematicians already spent hours or years about your problem.
they call it the subset sum problem, a special case of the notorious knapsack problem.

Not going to discourage you; but maybe you will get a few ideas from there:

http://en.wikipedia.org/wiki/Subset_sum_problem

http://en.wikipedia.org/wiki/Knapsack_problem

regards

 

[CassidyHunt]

very interesting. That is exactly the issue. At least I have something to go back to my board to see if we can come up with a few more constraints.

Thanks

Cassidy