July 13th, 2012, 02:48 PM
Query with Multiple Date Ranges
First off, I am in the process of learning SQL so apologies in advance if I have trouble understanding or am not clear in my communication.
I have two tables (see below)
Columns (the combination of Account ID and Contract Start Date is unique)
1) Account ID
2) Contract Start Date (different for each Account ID)
3) Contract End Date (different for each Account ID)
4) Contract Amount
1) Date (MM/DD/YYYY format)
2) Account ID
3) # of Sales
I want to know if it's possible to run a query that will sum up all sales by Account ID but only if the sales came in between the contract start and end dates unique to that Account ID. Is this doable?
Appreciate any help and thanks in advance!
July 13th, 2012, 04:14 PM
Originally Posted by ParadiseCity310
what's the actual datatype of the datetime column?
also, what database is this?
July 13th, 2012, 04:24 PM
Appreciate the fast response!
I am using SQL Server 2008 and the data type of the column is (date, not null)
July 13th, 2012, 04:41 PM
also, thread moved to SQL Server forum
SELECT table1.[Account ID]
, SUM(table2.[# of Sales]) AS total_sales
ON table2.[Account ID] = table1.[Account ID]
AND table2.[Date] BETWEEN table1.[Contract Start Date]
AND table1.[Contract End Date]
BY table1.[Account ID]
July 13th, 2012, 06:16 PM