We help IT Professionals succeed at work.

SQL query for sales transactions with part payment transactions

dwknight
dwknight asked
on
Medium Priority
584 Views
Last Modified: 2012-05-06
I have a query that needs to be written:

I have a transaction table where sales are placed. I have a payment table where payments are received and are linked by a bookingNo (numeric field)

I have a report that is run periodically that has a from and a to date range (user selected)

This has been running very well when there is one transaction and one payment, or many transaction parts and one payment.

I now have a situation where a transaction (made up of 1 or more items) can have 2 or more payments assigned to it. (part payments over a time period).

I need run an sql query where the income from any date range (user entered)  obtains the transaction total from the sales table, then finds any payments before a payment found in the report date range (user selected to /from date range) - then subtracts any previously found  part payments from the total to arrive at a current balance. After this is done, any current part payments made in the report date range are subtracted from the remaining balance to arrive at a new balance.

The issue is that in the payment table - a payment date is recorded for each payment made. How do I select all payments before a certain date from the payment table and subtotal them, then subtract the payment sub total from the sales transaction table to arrive at a balance owing?

An example
sales table:
record 4
bookingid 5
detail shirt
Date 1/1/2009
amount 50.00
recordid 6
bookingid 5
detail shorts
date 1/1/2009
amount 40
payment table:
recordid 6
booking 5
date 5/1/2009
amountpd 20
record id 8
booking 5
date 10/1/2009
amountpd 10

I need to get a query to obtain all subtotals for the sales and purchases tables and subtract one from the other to show a total owing.

Many thanks
Comment
Watch Question

Commented:
Something like this, perhaps?
DECLARE @fromdate datetime, @todate datetime
SELECT @fromdate = '01-Jan-2009 00:00', @todate = '10-Jan-2009 23:59'
 
SELECT ST.bookingid, ST.detail, ST.[Date], ST.amount - ISNULL(SUM(PT.amountpd), 0) as Balance
FROM [sales table] ST
LEFT JOIN [payment table] PT ON PT.booking = ST.bookingid AND PT.[date] BETWEEN @fromdate AND @todate
WHERE  ST.[date] BETWEEN @fromdate AND @todate
GROUP BY ST.bookingid, ST.Detail, ST.[Date], ST.amount

Open in new window

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Thanks - sorry about the late reply.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.