SQL query for sales transactions with part payment transactions

Posted on 2009-02-16
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
Question by:dwknight
    LVL 25

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


    Author Closing Comment

    Thanks - sorry about the late reply.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now