Go Premium for a chance to win a PS4. Enter to Win

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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

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