SQL sum data between 2 dates in one column

Question

I have a table with orders that is looks like:

t1

+-----------+---------+------------+
| client ID | order q | order date |
+-----------+---------+------------+
|     01    |  100    | 01-02-2016 |
|     01    |  350    | 03-05-2016 |
+-----------+---------+------------+

And I have second table with sales:

t2

+-----------+-------+------------+
| client ID | sales | sales date |
+-----------+-------+------------+
|   01      | 50    | 03-02-2016 |
|   01      | 50    | 10-02-2016 |
|   01      | 300   | 04-05-2016 |
|   01      | 50    | 15-05-201  |
+-----------+-------+------------+  

The aim is to get SUM of sales MORE than first order date and LESS than second order date:

Result

+-----------+---------+-------+
| cliend ID | order q | sales |
+-----------+---------+-------+
|  01       | 100     | 100   |
|  01       | 350     | 350   |
+-----------+---------+-------+

First idea is to make order dates rating like

DENSE_RANK() OVER(PARTITION BY [client ID] ORDER BY [order date] ASC) AS R

then do something like this:

select
    client ID,
    order q,
    sum (sales) as sales
from 
    t2  
left outer join
    t2.client ID = t1.client ID
where 
    [sales date] >= [order date] 
    and [sales date] <= [order date] in (select [order date] 
                                         from t2 
                                         where (R < (R+1)))
group by 
    client ID, order q

I know this is wrong but this is the logic in my head.

Can you please give me some ideas how to do this?

Answer 1

Looks like sum between dates. I assume including current order date excluding next order date.

select t.clientID, fromdate, sum(sales) 
from (
    select clientID,orderq
        , fromdate = orderdate
        , todate = dateadd(dd, -1, lead(orderdate,1,cast('2100-01-01' as date)) over(partition by clientID order by orderdate))
    from(
            values
            (01,100,cast('2016-02-01' as date)),
            (01,350,cast('2016-05-03' as date))
        ) orders(clientID,orderq,orderdate)
    ) t
join( 
        values  
        (01,50  ,cast('2016-02-03' as date)),
        (01,50  ,cast('2016-02-10' as date)),
        (01,300 ,cast('2016-05-04' as date)),
        (01,50  ,cast('2016-05-15' as date))
    ) sales(clientID, sales,salesdate)
    on sales.salesdate between fromdate and todate
group by t.clientID, fromdate