ACCOUNT RECEIVABLE HISTORY DATA

Hi All,

I have a AR data like below :

1. InvNo
2. InvDate
3. InvAmount
4. PayNo
5. PayDate
6. AllocationAmount
7. PaidAmount

Sample Data :

Normal Allocation (no problem):

InvNo  InvDate        InvAmount  PayNo   PayDate          AllocationAmount   PaidAmount
A        01/05/2010   100            A          01/05/2010    0                        0
A        01/05/2010   0               AL01     01/10/2010     100                      0
A        01/05/2010   0               BR01     01/10/2010    -100                    100

Late Allocation:

InvNo  InvDate        InvAmount  PayNo   PayDate          AllocationAmount   PaidAmount
A        01/05/2010   100            A          01/05/2010    0                        0
A        01/05/2010   0               BR01     01/10/2010    -100                    100
A        01/05/2010   0               AL01     02/01/2010     100                      0

In plain English said :

We have received money from customer in January, but we don't know which customer.
In February, we know it, then we allocate the invoice.
When allocation (AL01) we also write BR01 Payment Data to the table.
We call it late allocation and we don't want the AR change when we print history of AR.

In report :

In January our AR still 100, but in Feb our AR will became 0, why, because base on Allocation date.

AR Jan = SUM(InvAmount) - SUM(AllocationAmount) - SUM(PaidAmount)

How to query it ?

Thank you.



LVL 1
emi_sastraAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johanntagleCommented:
It's possible to come up with fairly complex SQL that would give you an output like:

Month       Year      AR
January     2011    100
February   2011     0

Or
January-2011   February-2011
100                  0

However, instead of coming up with that, may I suggest you come up with another table to store the AR Summaries for the month?  It will look something like the first table above.

Why do I suggest this?  Because to come up with AR for a month, you will need to compute using all the transactions from the very first transaction (which could be years old) up to the end of that month.  That will be very slow once you have months (years?) of transactions.  If you come up with another table for AR Summaries, then you will just take the AR figure for the previous month, compute the transactions for the current month, then add the two values.

Let's say the first-ever recorded transaction was January 2010.  To populate the new AR_SUMMARIES table:
insert into AR_SUMMARIES 
select 'January', '2010', 
   SUM(InvAmount) - SUM(AllocationAmount) - SUM(PaidAmount) 
from AR_DATA where PayDate < to_date('01-Feb-2010','dd-Mon-YYYY')

Open in new window

(I'm not too familiar with what's the equivalent of to_date in SQL Server so please just adjust the above)

Then for the next months, you do:
insert into AR_SUMMARIES 
select 'January', '2010', 
   (SUM(InvAmount) - SUM(AllocationAmount) - SUM(PaidAmount) + 
     (select AR from AR_SUMMARIES where year='2010' and month='January')
   )
from AR_DATA where PayDate between to_date('01-Feb-2010','dd-Mon-YYYY') and to_date('01-Mar-2010','dd-Mon-YYYY')

Open in new window


Of course,  if you're talking many years of data you should do this in an script that automatically adjusts the months and dates.

After the initial population maybe you can plan on having a trigger that will update AR_SUMMARIES for every insert/update/delete in AR_DATA.

Then just select from the AR_SUMMARIES table for your historical AR.

Hope this helps.
emi_sastraAuthor Commented:
Hi johanntagle,

Let's say we keep the table that way, how to generate the right output ?

InvNo  InvDate        InvAmount  PayNo   PayDate          AllocationAmount   PaidAmount
A        01/05/2010   100            A          01/05/2010    0                        0
A        01/05/2010   0               BR01     01/10/2010    -100                    100
A        01/05/2010   0               AL01     02/01/2010     100                      0


1. At Date 01/06/2010
2. At Date 01/01/2010
3. At Date 02/01/2010

Thank you.
johanntagleCommented:
Just for the specific output you specified, the simplest way would be:

select SUM(InvAmount) - SUM(AllocationAmount) - SUM(PaidAmount)
from AR_DATA
where paydate <= to_date('06-Jan-2010','dd-Mon-YYYY')
UNION
select SUM(InvAmount) - SUM(AllocationAmount) - SUM(PaidAmount)
from AR_DATA
where paydate <= to_date('01-Jan-2010','dd-Mon-YYYY')
UNION
select SUM(InvAmount) - SUM(AllocationAmount) - SUM(PaidAmount)
from AR_DATA
where paydate <= to_date('01-Feb-2010','dd-Mon-YYYY')

Open in new window


Again, I'm not sure re equivalent of to_date in MS-SQL (I mainly use Oracle) so please check.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

emi_sastraAuthor Commented:
Give me sometimes, I think there sometimes, since my report has some problem using the same approach.

Thank you.
emi_sastraAuthor Commented:
Please see below data :

InvNo    InvDate             InvAmount     PayNo                                       PayDate          AllocationAmount         PaidAmount
A          2011/01/13       31280000.00      11BD000122                                  2011/01/13                     0.00                       0.00
A          2011/01/13                   0.00      BM/1149-RP/BAD-JKT/1101/030         2011/01/28        -31280000.00       31280000.00
A          2011/01/13                   0.00      AP/BAD-JKT/1102/144                 2011/02/17         31280000.00                       0.00

B          2011/02/16       45000000.00      11BD000530                                  2011/02/16                    0.00      0.00
B          2011/02/16                   0.00      AP/BAD-JKT/1103/253                 2011/03/26        45000000.00      0.00

Expected Report format :

1.InvNo
2.InvDate
3.InvAmount
4.BegBalance
5.Sales
6.AllocationAmount
7.PaidAmount


Expected Report :

InvNo    InvDate          InvAmount     BegBalance                Sales               AllocationAmount         PaidAmount

1. Jan 1 2011 - Jan 31 2011

A          2011/01/13       31280000.00            0.00     31280000.00                                 0.00                   0.00

2. Feb 1 2011 - Feb 28 2011

A          2011/01/13       31280000.00  31280000.00               0.00                                  0.00       31280000.00
B          2011/02/16       45000000.00              0.00   45000000.00                                  0.00                   0.00            

3. Mar 1 2011 - Mar 31 2011

B          2011/02/16       45000000.00  45000000.00               0.00                      45000000.00                   0.00

4. Apr 1 2011 - Apr 30 2011

B          2011/02/16       45000000.00  45000000.00               0.00                      45000000.00                   0.00

Thank you.

johanntagleCommented:
Okay so you want to show:

1.  New invoices for the month that are left unpaid by the end of the month (A in the January Report)
2.  Invoices that were outstanding the previous month then paid in the current month (A in the February report)
3.  Invoices created the previous month or older and that are still left outstanding

Is that correct?

Assuming:
- BegBalance is the beginning balance at the start of the month covered by the report
- Sales only has a non-zero value on the month the invoice was created
- Invoices are always paid in full (I put in what I think can handle partial payment, but I'm not sure their correct and complete)

/*This addresses criteria 1 */
select InvNo, InvDate, sum(InvAmount) as InvAmount, 0 as BegBalance, sum(InvAmount) as Sales, sum(AllocationAmount), sum(PaidAmount)
from AR_DATA INNER JOIN
  (select InvNo, sum(InvAmount)-sum(AllocationAmount)-sum(PaidAmount)
  from AR_DATA
  where PayDate between to_date(v_start_date,'dd-Mon-YYYY') and to_date(v_end_date,'dd-Mon-YYYY')
  group by InvNo
  having sum(InvAmount)-sum(AllocationAmount)-sum(PaidAmount)>0) NEW_AR
ON AR_DATA.InvNo = NEW_AR.InvNo
where AR_DATA.InvAmount > 0
group by InvNo, InvDate, BegBalance
UNION
/*This addresses criteria 2 */
SELECT FULLY_PAID.InvNo, FULLY_PAID.InvDate, PREV_AR.BegBalance, 0 as Sales, 0 as AllocationAmount, FULLY_PAID.PaidAmount
FROM
  ( select InvNo, sum(InvAmount)-sum(AllocationAmount)-sum(PaidAmount) as BegBalance
     from AR_DATA
     where PayDate < to_date(v_start_date,'dd-Mon-YYYY') and to_date(v_end_date,'dd-Mon-YYYY')
     group by InvNo
     having sum(InvAmount)-sum(AllocationAmount)-sum(PaidAmount)>0
  ) PREV_AR
 INNER JOIN
  ( select InvNo, sum(InvAmount)-sum(AllocationAmount)-sum(PaidAmount), sum(PaidAmount) as PaidAmount
     from AR_DATA
     where PayDate < to_date(v_end_date,'dd-Mon-YYYY')
     group by InvNo
     having sum(InvAmount)-sum(AllocationAmount)-sum(PaidAmount)=0
  ) FULLY_PAID
 ON PREV_AR.InvNo=FULLY_PAID.InvNo
UNION
/*This addresses criteria 3 */
select InvNo, InvDate, sum(InvAmount), sum(InvAmount)-sum(AllocationAmount)-sum(PaidAmount) as BegBalance, 0 as Sales, sum(AllocationAmount), sum(PaidAmount)
from AR_DATA 
where PayDate < to_date(v_start_date,'dd-Mon-YYYY')
and InvNo not in (select distinct InvNo for AR_DATA where PayDate between to_date(v_start_date,'dd-Mon-YYYY') and to_date(v_end_date,'dd-Mon-YYYY') )
group by InvNo, InvDate, Sales
having sum(InvAmount)-sum(AllocationAmount)-sum(PaidAmount)>0

Open in new window


Replace v_start_date and v_end_date accordingly.  Also note warning re to_date in my previous post.

Warning - untested!  But it should point you to the right direction.

emi_sastraAuthor Commented:
Let my try first.

Thank you.
emi_sastraAuthor Commented:
So many query has no result column name such as :

select InvNo, sum(InvAmount)-sum(AllocationAmount)-sum(PaidAmount), sum(PaidAmount) as PaidAmount

Like this :
sum(InvAmount)-sum(AllocationAmount)-sum(PaidAmount),

Thank you.
emi_sastraAuthor Commented:
Begining Balance =
sum(InvAmount)-sum(AllocationAmount)-sum(PaidAmount)

select InvNo, sum(InvAmount)-sum(AllocationAmount)-sum(PaidAmount) as BegBalance
     from AR_DATA
     where PayDate < to_date(v_start_date,'dd-Mon-YYYY') and to_date(v_end_date,'dd-Mon-YYYY')
     group by InvNo
     having sum(InvAmount)-sum(AllocationAmount)-sum(PaidAmount)>0

I think this is not correct, please see invoice B.
At the beginning of March 1, 2001 will get wrong value.

The where clause also wrong.

Am I right ?

Thank you.
johanntagleCommented:
I think I got the first two criteria (I did this in MySQL but the query syntax (again except for the date handling) is fairly standard.  )

mysql> select * from AR_DATA;
+-------+------------+-----------+-----------------------------+------------+------------------+------------+
| InvNo | InvDate    | InvAmount | PayNo                       | PayDate    | AllocationAmount | PaidAmount |
+-------+------------+-----------+-----------------------------+------------+------------------+------------+
| A     | 2011-01-13 | 3.128e+07 | 11BD000122                  | 2011-01-13 |                0 |          0 |
| A     | 2011-01-13 |         0 | BM/1149-RP/BAD-JKT/1101/030 | 2011-01-28 |       -3.128e+07 |  3.128e+07 |
| A     | 2011-01-13 |         0 | AP/BAD-JKT/1102/144         | 2011-02-17 |        3.128e+07 |          0 |
| B     | 2011-02-16 |   4.5e+07 | 11BD000530                  | 2011-02-16 |                0 |          0 |
| B     | 2011-02-16 |         0 | AP/BAD-JKT/1103/253         | 2011-03-26 |          4.5e+07 |          0 |
+-------+------------+-----------+-----------------------------+------------+------------------+------------+
5 rows in set (0.00 sec)

mysql> select AR_DATA.InvNo as InvNo, AR_DATA.InvDate as InvDate, sum(InvAmount) as InvAmount, 0 as BegBalance, sum(InvAmount) as Sales, sum(AllocationAmount) as AllocationAmount, sum(PaidAmount) as PaidAmount
    -> from AR_DATA INNER JOIN
    ->   (select InvNo, sum(InvAmount)-sum(AllocationAmount)-sum(PaidAmount)
    ->   from AR_DATA
    ->   where PayDate between '2011-01-01' and '2011-02-01'
    ->   group by InvNo
    ->   having sum(InvAmount)-sum(AllocationAmount)-sum(PaidAmount)>0) NEW_AR
    -> ON AR_DATA.InvNo = NEW_AR.InvNo
    -> where AR_DATA.InvAmount > 0
    -> group by InvNo, InvDate, BegBalance
    -> UNION
    -> /*This addresses criteria 2 */
    -> SELECT FULLY_PAID.InvNo as InvNo, FULLY_PAID.InvDate as InvDate, PREV_AR.InvAmount as InvDate, PREV_AR.BegBalance as BegBalance, 0 as Sales, 0 as AllocationAmount, FULLY_PAID.PaidAmount as PaidAmount
    -> FROM
    ->   ( select InvNo, sum(InvAmount) as InvAmount, sum(InvAmount)-sum(AllocationAmount)-sum(PaidAmount) as BegBalance
    ->      from AR_DATA
    ->      where PayDate < '2011-01-01' and '2011-02-01'
    ->      group by InvNo
    ->      having sum(InvAmount)-sum(AllocationAmount)-sum(PaidAmount)>0
    ->   ) PREV_AR
    ->  INNER JOIN
    ->   ( select InvNo, InvDate, sum(InvAmount)-sum(AllocationAmount)-sum(PaidAmount), sum(PaidAmount) as PaidAmount
    ->      from AR_DATA
    ->      where PayDate < '2011-02-01'
    ->      group by InvNo, InvDate
    ->      having (sum(InvAmount)-sum(AllocationAmount)-sum(PaidAmount)=0 and sum(PaidAmount)>0)
    ->   ) FULLY_PAID
    ->  ON PREV_AR.InvNo=FULLY_PAID.InvNo;
+-------+------------+-----------+------------+----------+------------------+------------+
| InvNo | InvDate    | InvAmount | BegBalance | Sales    | AllocationAmount | PaidAmount |
+-------+------------+-----------+------------+----------+------------------+------------+
| A     | 2011-01-13 |  31280000 |          0 | 31280000 |                0 |          0 |
+-------+------------+-----------+------------+----------+------------------+------------+
1 row in set, 1 warning (0.00 sec)

mysql> select AR_DATA.InvNo as InvNo, AR_DATA.InvDate as InvDate, sum(InvAmount) as InvAmount, 0 as BegBalance, sum(InvAmount) as Sales, sum(AllocationAmount) as AllocationAmount, sum(PaidAmount) as PaidAmount from AR_DATA INNER JOIN   (select InvNo, sum(InvAmount)-sum(AllocationAmount)-sum(PaidAmount)   from AR_DATA   where PayDate between '2011-02-01' and '2011-03-01'   group by InvNo   having sum(InvAmount)-sum(AllocationAmount)-sum(PaidAmount)>0) NEW_AR ON AR_DATA.InvNo = NEW_AR.InvNo where AR_DATA.InvAmount > 0 group by InvNo, InvDate, BegBalance UNION  SELECT FULLY_PAID.InvNo as InvNo, FULLY_PAID.InvDate as InvDate, PREV_AR.InvAmount as InvDate, PREV_AR.BegBalance as BegBalance, 0 as Sales, 0 as AllocationAmount, FULLY_PAID.PaidAmount as PaidAmount FROM   ( select InvNo, sum(InvAmount) as InvAmount, sum(InvAmount)-sum(AllocationAmount)-sum(PaidAmount) as BegBalance      from AR_DATA      where PayDate < '2011-02-01' and '2011-03-01'      group by InvNo      having sum(InvAmount)-sum(AllocationAmount)-sum(PaidAmount)>0   ) PREV_AR  INNER JOIN   ( select InvNo, InvDate, sum(InvAmount)-sum(AllocationAmount)-sum(PaidAmount), sum(PaidAmount) as PaidAmount      from AR_DATA      where PayDate < '2011-03-01'      group by InvNo, InvDate      having (sum(InvAmount)-sum(AllocationAmount)-sum(PaidAmount)=0 and sum(PaidAmount)>0)   ) FULLY_PAID  ON PREV_AR.InvNo=FULLY_PAID.InvNo;
+-------+------------+-----------+------------+----------+------------------+------------+
| InvNo | InvDate    | InvAmount | BegBalance | Sales    | AllocationAmount | PaidAmount |
+-------+------------+-----------+------------+----------+------------------+------------+
| B     | 2011-02-16 |  45000000 |          0 | 45000000 |                0 |          0 |
| A     | 2011-01-13 |  31280000 |   31280000 |        0 |                0 |   31280000 |
+-------+------------+-----------+------------+----------+------------------+------------+
2 rows in set, 1 warning (0.00 sec)

mysql> select AR_DATA.InvNo as InvNo, AR_DATA.InvDate as InvDate, sum(InvAmount) as InvAmount, 0 as BegBalance, sum(InvAmount) as Sales, sum(AllocationAmount) as AllocationAmount, sum(PaidAmount) as PaidAmount from AR_DATA INNER JOIN   (select InvNo, sum(InvAmount)-sum(AllocationAmount)-sum(PaidAmount)   from AR_DATA   where PayDate between '2011-03-01' and '2011-04-01'   group by InvNo   having sum(InvAmount)-sum(AllocationAmount)-sum(PaidAmount)>0) NEW_AR ON AR_DATA.InvNo = NEW_AR.InvNo where AR_DATA.InvAmount > 0 group by InvNo, InvDate, BegBalance UNION  SELECT FULLY_PAID.InvNo as InvNo, FULLY_PAID.InvDate as InvDate, PREV_AR.InvAmount as InvDate, PREV_AR.BegBalance as BegBalance, 0 as Sales, 0 as AllocationAmount, FULLY_PAID.PaidAmount as PaidAmount FROM   ( select InvNo, sum(InvAmount) as InvAmount, sum(InvAmount)-sum(AllocationAmount)-sum(PaidAmount) as BegBalance      from AR_DATA      where PayDate < '2011-03-01' and '2011-04-01'      group by InvNo      having sum(InvAmount)-sum(AllocationAmount)-sum(PaidAmount)>0   ) PREV_AR  INNER JOIN   ( select InvNo, InvDate, sum(InvAmount)-sum(AllocationAmount)-sum(PaidAmount), sum(PaidAmount) as PaidAmount      from AR_DATA      where PayDate < '2011-04-01'      group by InvNo, InvDate      having (sum(InvAmount)-sum(AllocationAmount)-sum(PaidAmount)=0 and sum(PaidAmount)>0)   ) FULLY_PAID  ON PREV_AR.InvNo=FULLY_PAID.InvNo;
Empty set, 1 warning (0.00 sec)

Open in new window


(The second and third queries are the same as the first, with only the dates changed.)

The third criteria is proving a bit tricky.  I'll see if I have time for it later.  But we're getting there.
emi_sastraAuthor Commented:
As I mentioned before, the BegBalance on March 1, 2011 will get wrong value for invoice B.

What do you think ?

Thank you.
johanntagleCommented:
I'm basing it on the expected output you have given:



2. Feb 1 2011 - Feb 28 2011

A          2011/01/13       31280000.00  31280000.00               0.00                                  0.00       31280000.00
B          2011/02/16       45000000.00              0.00   45000000.00                                  0.00                   0.00            

+-------+------------+-----------+------------+----------+------------------+------------+
| InvNo | InvDate    | InvAmount | BegBalance | Sales    | AllocationAmount | PaidAmount |
+-------+------------+-----------+------------+----------+------------------+------------+
| B     | 2011-02-16 |  45000000 |          0 | 45000000 |                0 |          0 |
| A     | 2011-01-13 |  31280000 |   31280000 |        0 |                0 |   31280000 |
+-------+------------+-----------+------------+----------+------------------+------------+

Open in new window


If this is wrong, what is your expected output for BegBalance for B?
emi_sastraAuthor Commented:
Your result is for 2. Feb 1 2011 - Feb 28 2011

Look at below.

3. Mar 1 2011 - Mar 31 2011

B          2011/02/16       45000000.00  45000000.00               0.00                      45000000.00                   0.00

4. Apr 1 2011 - Apr 30 2011

B          2011/02/16       45000000.00  45000000.00               0.00                      45000000.00                   0.00

It the cheque is not cleared (AllocationAmount), will be carried forward the next period and so on.

Thank you.
johanntagleCommented:
Yes that is the 3rd criterion I said I haven't figured out yet.
emi_sastraAuthor Commented:
Ok.

Thank you.
johanntagleCommented:
Here you go:
select AR_DATA.InvNo as InvNo, AR_DATA.InvDate as InvDate, sum(InvAmount) as InvAmount, 0 as BegBalance, sum(InvAmount) as Sales, sum(AllocationAmount) as AllocationAmount, sum(PaidAmount) as PaidAmount
from AR_DATA INNER JOIN
  (select InvNo, sum(InvAmount)-sum(AllocationAmount)-sum(PaidAmount)
  from AR_DATA
  where PayDate between '2011-04-01' and '2011-05-01'
  group by InvNo
  having sum(InvAmount)-sum(AllocationAmount)-sum(PaidAmount)>0) NEW_AR
ON AR_DATA.InvNo = NEW_AR.InvNo
where AR_DATA.InvAmount > 0
group by InvNo, InvDate, BegBalance
UNION
/*This addresses criteria 2 */
SELECT FULLY_PAID.InvNo as InvNo, FULLY_PAID.InvDate as InvDate, PREV_AR.InvAmount as InvDate, PREV_AR.BegBalance as BegBalance, 0 as Sales, 0 as AllocationAmount, FULLY_PAID.PaidAmount as PaidAmount
FROM
  ( select InvNo, sum(InvAmount) as InvAmount, sum(InvAmount)-sum(AllocationAmount)-sum(PaidAmount) as BegBalance
     from AR_DATA
     where PayDate < '2011-04-01'
     group by InvNo
     having sum(InvAmount)-sum(AllocationAmount)-sum(PaidAmount)>0
  ) PREV_AR
 INNER JOIN
  ( select InvNo, InvDate, sum(InvAmount)-sum(AllocationAmount)-sum(PaidAmount), sum(PaidAmount) as PaidAmount
     from AR_DATA
     where PayDate < '2011-05-01'
     group by InvNo, InvDate
     having (sum(InvAmount)-sum(AllocationAmount)-sum(PaidAmount)=0 and sum(PaidAmount)>0)
  ) FULLY_PAID
 ON PREV_AR.InvNo=FULLY_PAID.InvNo
UNION
/*This addresses criteria 3 */
select previous.InvNo as InvNo, previous.InvDate as InvDate, sum(previous.InvAmount) as InvAmount, sum(previous.InvAmount)-sum(previous.PaidAmount) as BegBalance, 0 as Sales, current.AllocationAmount as AllocationAmount, sum(previous.PaidAmount) as PaidAmount
  from AR_DATA previous 
    join (select InvNo, sum(AllocationAmount) AllocationAmount, sum(InvAmount), sum(PaidAmount)
          from AR_DATA
          where PayDate < '2011-05-01'
          group by InvNo
	  having sum(InvAmount)-sum(PaidAmount)>0 
         ) current 
    on previous.InvNo=current.InvNo
  where previous.PayDate < '2011-04-01' 
  and previous.InvNo not in (select a.InvNo from 
        (select InvNo, sum(InvAmount)-sum(PaidAmount) BegBalance, sum(PaidAmount) PaidAmount
         from AR_DATA
         where PayDate < '2011-05-01' 
         group by InvNo
         having sum(InvAmount)-sum(PaidAmount)=0) a
         )
  group by previous.InvNo, previous.InvDate
  having sum(previous.InvAmount)-sum(previous.PaidAmount)>0        

Open in new window


See it in action in the attached spool file.

Whew!  That was tough =)
ar.txt

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
emi_sastraAuthor Commented:
Why still use below code ? The result will be 0 ?
 
select InvNo, sum(InvAmount) as InvAmount, sum(InvAmount)-sum(AllocationAmount)-sum(PaidAmount) as BegBalance
     from AR_DATA
     where PayDate < '2011-04-01'
     group by InvNo
     having sum(InvAmount)-sum(AllocationAmount)-sum(PaidAmount)>0

B          2011/02/16       45000000.00      11BD000530                                  2011/02/16                    0.00      0.00
B          2011/02/16                   0.00      AP/BAD-JKT/1103/253                 2011/03/26        45000000.00      0.00

sum(InvAmount)-sum(AllocationAmount)-sum(PaidAmount) as BegBalance
    45000000.00 -  45000000.00 - 0 = 0

Thank you.
johanntagleCommented:
Each subquery separated by a UNION clause actually looks for a match for one of the 3 criteria and that criterion only.

1.  New invoices for the month that are left unpaid by the end of the month (A in the January Report) is handled by:
select AR_DATA.InvNo as InvNo, AR_DATA.InvDate as InvDate, sum(InvAmount) as InvAmount, 0 as BegBalance, sum(InvAmount) as Sales, sum(AllocationAmount) as AllocationAmount, sum(PaidAmount) as PaidAmount
from AR_DATA INNER JOIN
  (select InvNo, sum(InvAmount)-sum(AllocationAmount)-sum(PaidAmount)
  from AR_DATA
  where PayDate between '2011-04-01' and '2011-05-01'
  group by InvNo
  having sum(InvAmount)-sum(AllocationAmount)-sum(PaidAmount)>0) NEW_AR
ON AR_DATA.InvNo = NEW_AR.InvNo
where AR_DATA.InvAmount > 0
group by InvNo, InvDate, BegBalance

Open in new window


2.  Invoices that were outstanding the previous month then paid in the current month (A in the February report) is handled by:
SELECT FULLY_PAID.InvNo as InvNo, FULLY_PAID.InvDate as InvDate, PREV_AR.InvAmount as InvDate, PREV_AR.BegBalance as BegBalance, 0 as Sales, 0 as AllocationAmount, FULLY_PAID.PaidAmount as PaidAmount
FROM
  ( select InvNo, sum(InvAmount) as InvAmount, sum(InvAmount)-sum(AllocationAmount)-sum(PaidAmount) as BegBalance
     from AR_DATA
     where PayDate < '2011-04-01'
     group by InvNo
     having sum(InvAmount)-sum(AllocationAmount)-sum(PaidAmount)>0
  ) PREV_AR
 INNER JOIN
  ( select InvNo, InvDate, sum(InvAmount)-sum(AllocationAmount)-sum(PaidAmount), sum(PaidAmount) as PaidAmount
     from AR_DATA
     where PayDate < '2011-05-01'
     group by InvNo, InvDate
     having (sum(InvAmount)-sum(AllocationAmount)-sum(PaidAmount)=0 and sum(PaidAmount)>0)
  ) FULLY_PAID
 ON PREV_AR.InvNo=FULLY_PAID.InvNo

Open in new window


3.  Invoices created the previous month or older and that are still left outstanding  is handled by:
select previous.InvNo as InvNo, previous.InvDate as InvDate, sum(previous.InvAmount) as InvAmount, sum(previous.InvAmount)-sum(previous.PaidAmount) as BegBalance, 0 as Sales, current.AllocationAmount as AllocationAmount, sum(previous.PaidAmount) as PaidAmount
  from AR_DATA previous 
    join (select InvNo, sum(AllocationAmount) AllocationAmount, sum(InvAmount), sum(PaidAmount)
          from AR_DATA
          where PayDate < '2011-05-01'
          group by InvNo
	  having sum(InvAmount)-sum(PaidAmount)>0 
         ) current 
    on previous.InvNo=current.InvNo
  where previous.PayDate < '2011-04-01' 
  and previous.InvNo not in (select a.InvNo from 
        (select InvNo, sum(InvAmount)-sum(PaidAmount) BegBalance, sum(PaidAmount) PaidAmount
         from AR_DATA
         where PayDate < '2011-05-01' 
         group by InvNo
         having sum(InvAmount)-sum(PaidAmount)=0) a
         )
  group by previous.InvNo, previous.InvDate
  having sum(previous.InvAmount)-sum(previous.PaidAmount)>0

Open in new window


For the January report, only the first subquery found something.  For Feb, the first two got results, for March and April, only the 3rd subquery found something.

It's possible that there's a simpler SQL to do this, but this is what I came up with.
emi_sastraAuthor Commented:
Let me learn it first what do you mean.

Thank you.
johanntagleCommented:
Hi let me know if you still have questions regarding the solution I gave.  I don't normally follow-up on closing a question but I spent a lot more time than I normally allow myself to answer questions on this and would like to at least earn my points =)
emi_sastraAuthor Commented:
I am sorry, just back again.

I have to test it.

At the moment it seems will working.

Thank you very much for your help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.