Solved

sql cash reporting

Posted on 2010-11-19
9
377 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:gsgi
9 Comments
 
LVL 7

Expert Comment

by:rmm2001
ID: 34177358
SELECT Office, SUM(monies), month(thedate) + '/1/' + year(thedate)
FROM table
GROUP BY Office,  month(thedate) + '/1/' + year(thedate)
0
 
LVL 22

Accepted Solution

by:
8080_Diver earned 500 total points
ID: 34177511
Yes it can be done.  However, the query to accomplish it is not nearly as simple as the one offered in response #34177358.

(I have copied your sample data to the Code attachment so that it is easier to read. ;-)

Look at the first payment received (on 2010-01-20).  according to your initial post, that should apply to the first (office1) Charge.  Now, the payment received on 2010-02-05 would need to have $50 applied to the first charge, $200 applied to the second charge, and $50 applied to the third charge.  That is going to complicate things.

The approach I generally take is to separate the Charges and Payments into separate tables.  In the Charges Table, each row has a column for UnpaidBalance Default value = AmtCharged).  In the Payments table there is a column for AmtApplied (default value = 0).  Then, when I start processing payments, I select all payments that have a PaymentAmt that is greater than the AmtApplied.  Then I select the Charges that have non-zero UnpaidBalance amounts and that do not exceed the difference between the Payment entry's PaymentAmount and the AmtApplied (i.e. the AmtUnapplied).  I then process the AmtUnapplied, applying it against the oldest Charges entry first and applying the lesser of the UnappliedAmt or the UnpaidBalance to the Charges Entry, adding that amount to the AmtApplied and subtracting it from the UnappliedAmt.  (Among other things, this also allows for people lioke me who don't remember the remaining balance and pay somewhere between $0 and $15 more than the outstanding balance, thus having a Credit Balance. ;-)

I would advise doing this in a stored procedure or, if possible, in an SSIS/DTS package that runs periodically.

It can also be done with triggers on the Payments and Charges tables so that, whenever a Payment or Charge entry is made, the stored procedure is executed to update the appropriate columns in the tables.  If your reports are run, for instance, once a day, you may want to execute the stored proc as a part of the report job stream so as to minimize the load on the server.

Note, by having the two separate tables with the additional columns, this simplifies the whole process and it also provides the data for the reports more easily. ;-)


Your Sample Data:

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

Open in new window

0
 
LVL 13

Author Comment

by:gsgi
ID: 34177669
My plan is to run this once a month, for the previous month.

Thanks,
gsgi
0
 
LVL 6

Expert Comment

by:subhashpunia
ID: 34187069
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)
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 22

Expert Comment

by:8080_Diver
ID: 34190531
@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.
0
 
LVL 13

Author Comment

by:gsgi
ID: 34190766
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...
0
 
LVL 13

Author Comment

by:gsgi
ID: 34190777
oh, i forgot amount-credited ... that would be important, and also as a check pk-of-payment-entry
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 34190888
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 ;-)..
0
 
LVL 13

Author Closing Comment

by:gsgi
ID: 34191239
Thanks!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now