Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# sql cash reporting

Posted on 2010-11-19
Medium Priority
428 Views
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
Question by:gsgi
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 7

Expert Comment

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

8080_Diver earned 2000 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
``````
0

LVL 13

Author Comment

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

Thanks,
gsgi
0

LVL 6

Expert Comment

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

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

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

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

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

ID: 34191239
Thanks!
0

## Featured Post

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this â€¦
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
###### Suggested Courses
Course of the Month9 days, 16 hours left to enroll