Solved

calculate sales based on specific dates in table in sql server 2000

Posted on 2011-02-16
2
346 Views
Last Modified: 2012-05-11
Hi all.

I have a SQL Server 2000 table (myTableA) that has the following fields: ContractNo, CustomerID, StartDate, EndDate

I have another table (myTableB)that stores every sales transaction for each customer. One of the fields in myTableB is InvoiceDate. I want to be able to sum up all the sales based on the InvoiceDate falling between the StartDate and EndDate for the customers' contractNo.

So for example, let's say I have in myTableA:

ContractA---Customer123---01/01/2010---03/01/2010
ContractB---Customer123---03/02/2010---12/31/2010

And in myTableB:
Customer123---InvoiceDate:02/01/2010---$5
Customer123---InvoiceDate:02/24/2010---$5
Customer123---InvoiceDate:03/05/2010---$20

I would like the end result to show:
ContractA---Customer123---$10
ContractB---Customer123---$20

How can I do this?

Thank you in advance!
0
Comment
Question by:printmedia
2 Comments
 
LVL 11

Accepted Solution

by:
JoeNuvo earned 500 total points
ID: 34907043
SELECT A.ContractNo, A.CustomerID, SUM(amount) AS Total
FROM myTableA A INNER JOIN myTableB B
	ON A.CustomerID = B.CustomerID 
AND B.InvoiceDate BETWEEN A.StartDate AND A.EndDate
GROUP BY A.ContractNo, A.CustomerID

Open in new window

0
 

Author Closing Comment

by:printmedia
ID: 34907160
Thanks!
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

816 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

6 Experts available now in Live!

Get 1:1 Help Now