• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 365
  • 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
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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