calculate sales based on specific dates in table in sql server 2000
Posted on 2011-02-16
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:
And in myTableB:
I would like the end result to show:
How can I do this?
Thank you in advance!