printmedia
asked on
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/2 010
ContractB---Customer123--- 03/02/2010 ---12/31/2 010
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!
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---
ContractB---Customer123---
And in myTableB:
Customer123---InvoiceDate:
Customer123---InvoiceDate:
Customer123---InvoiceDate:
I would like the end result to show:
ContractA---Customer123---
ContractB---Customer123---
How can I do this?
Thank you in advance!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER