asked on

sql cash reporting

Can this be done in sql, or do i need a procedural language like .NET?

Make a report as below that shows by month which offices brought in money owed. Apply payments towards the oldest charge on the books by individual client... see below...

1/1/2010 client1 charge 100.00 office1
1/10/2010 client1 charge 200.00 office2
1/20/2010 client1 pay 50.00
1/25/2010 client1 charge 100.00 office3
2/1/2010 client1 charge 150.00 office2
2/5/2010 client1 pay 300.00
2/10/2010 client1 charge 200.00 office1
2/15/2010 client1 pay 200.00
1/1/2010 client2 charge 200.00 office1
1/10/2010 client2 charge 100.00 office2
1/20/2010 client2 pay 150.00
1/25/2010 client2 charge 200.00 office3
2/1/2010 client2 charge 50.00 office2
2/5/2010 client2 pay 200.00
2/10/2010 client2 charge 400.00 office1
2/15/2010 client2 pay 500.00

report
payments in january
office1 200.00

payments in february
office1 400.00
office2 500.00
office3 300.00

rmm2001

SELECT Office, SUM(monies), month(thedate) + '/1/' + year(thedate)
FROM table
GROUP BY Office, month(thedate) + '/1/' + year(thedate)

ASKER CERTIFIED SOLUTION

8080_Diver

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gsgi

ASKER

My plan is to run this once a month, for the previous month.

Thanks,
gsgi

subhashpunia

SELECT Office, SUM(monies), month(thedate) + '/1/' + year(thedate)
FROM table
WHERE MONTH(theDate) = month(CAST(FLOOR(CAST(getdate() AS DECIMAL(12, 5))) - (DAY(getdate())) AS DATETIME))
AND YEAR(theDate) = YEAR(CAST(FLOOR(CAST(getdate() AS DECIMAL(12, 5))) - (DAY(getdate())) AS DATETIME))
GROUP BY Office, month(thedate) + '/1/' + year(thedate)

8080_Diver

@gsgi,

What do you plan to do about carry-over charges/payments from previous months? For instance, if a charge in January has not been completely covered by payments in January, how does that impact payments being made in February?

Will/won't you need to have some sort of "Balance Forward" factor in February that provide the net impact of all previous Charges and Payments? While the "Balance Forward" would, hopefully, be a zero, it could be non-zero if, for instance, a Charge occurs on January 31 and the payment comes in on February 1.

gsgi

ASKER

your unpaidbalance column should work. when a payment comes in in feb, i will simply look for the first unpaidbalance > 0 and start calculating which office gets credit (or partial) credit for that payment. i do think i need a table: payment-date office-getting-credit pk-of-orig-charge ... i really do not need pk-of-orig-charge but it is good to have as a check...

gsgi

ASKER

oh, i forgot amount-credited ... that would be important, and also as a check pk-of-payment-entry

8080_Diver

I have a tendency to maintain the two different types of transactions in separate tables (Charges and Payments), as I indicated, and to maintain the AmtPaid in the Charges table and the AmtApplied in the Payments table. It may be a little more trouble but, in the long run, I have found that it provides a good check & balance approach to the problem. It also lets you easily provide an invoice that shows the chargesand payments and to age the unpaid/over-paid balance (although, I usually don't show an aging of the over-paid balance and just opt for an indication of a Credit Balance ;-)..

gsgi

ASKER

Thanks!