SQL 对一列中 2 个日期之间的数据求和

Question

我有一个包含订单的表，如下所示:

t1

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

我有第二张销售表:

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  |
+-----------+-------+------------+  

目标是获得大于第一个订单日期且小于第二个订单日期的销售额SUM:

结果

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

第一个想法是对订单日期进行评级

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

然后做这样的事情:

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

我知道这是错误的，但这是我头脑中的逻辑。

您能给我一些如何做到这一点的想法吗？

Answer 1

看起来像是日期之间的总和。我假设包括当前订单日期，不包括下一个订单日期。

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