Solved

sql cash reporting

Posted on 2010-11-19
9
410 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

679 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