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
  • Learn & ask questions
Solved

sql cash reporting

Posted on 2010-11-19
9
404 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

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…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

789 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