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.

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.

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.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license!
Start your 7-day free trial.

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.

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.

```
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')
```

Again, I'm not sure re equivalent of to_date in MS-SQL (I mainly use Oracle) so please check.

Thank you.

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/03

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.

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
```

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.

select InvNo, sum(InvAmount)-sum(Allocat

Like this :

sum(InvAmount)-sum(Allocat

Thank you.

sum(InvAmount)-sum(Allocat

select InvNo, sum(InvAmount)-sum(Allocat

from AR_DATA

where PayDate < to_date(v_start_date,'dd-M

group by InvNo

having sum(InvAmount)-sum(Allocat

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.

```
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)
```

(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.

What do you think ?

Thank you.

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 |
+-------+------------+-----------+------------+----------+------------------+------------+
```

If this is wrong, what is your expected output for BegBalance for B?

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.

```
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
```

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 trialselect InvNo, sum(InvAmount) as InvAmount, sum(InvAmount)-sum(Allocat

from AR_DATA

where PayDate < '2011-04-01'

group by InvNo

having sum(InvAmount)-sum(Allocat

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(Allocat

45000000.00 - 45000000.00 - 0 = 0

Thank you.

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
```

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
```

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
```

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.

I have to test it.

At the moment it seems will working.

Thank you very much for your help.

Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license!
Start your 7-day free trial.

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:

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:

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.