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

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

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
printmedia
Asked:
printmedia
1 Solution
 
JoeNuvoCommented:
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
 
printmediaAuthor Commented:
Thanks!
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

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