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?
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
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加