• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 561
  • Last Modified:

SQL query for sales transactions with part payment transactions

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

dwknightAuthor Commented:
Thanks - sorry about the late reply.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now