sql - 获取日期范围的问题

Question

我试图m1.Price通过将日期范围作为 . 从 m1 中获取总和m1.tdate between w.adate1 and w.adate2。问题是m1.price没有正确计算。请查看 SQL 代码。

with m1 as 
(
    select sum(c.price) as Price,c.tdate ,c.unit
    from Dim_stocks c 
    group by c.tdate ,c.unit
)
select
    sum(w.total) as cost,
    w.a_time,
    w.unit,
    sum(m1.Price) as price
from Target_Shipment w 
left join m1 on m1.tdate = w.a_time and m1.unit = w.unit
where m1.tdate between cast(DATEADD(month,DATEDIFF(month,0,w.a_time),0) as date) and w.a_time    
group by w.a_time, w.unit

标签： sqlsql-servertsql

Answer 1

尽管在不正确了解问题的情况下无法回答。需要样本数据。但是您是否只想按日期获取 sum(price) 组？

with m1 as 
(
    select
        sum(c.price) over (partition by cast(c.tdate-DAY(c.tdate)+1 as date) order by tdate rows between unbounded preceding and current row) as Price
        , c.tdate, c.unit
    from Dim_stocks c 
)
select
    sum(w.total) as cost,
    w.a_time,
    w.unit,
    sum(m1.Price) as price
from Target_Shipment w 
left join m1 on m1.tdate = w.a_time and m1.unit = w.unit
where m1.tdate between cast(DATEADD(month,DATEDIFF(month,0,w.a_time),0) as date) and w.a_time    
group by w.a_time, w.unit

例子：

create table Dim_stocks(unit varchar(10), tdate date, price float);
insert into Dim_stocks values('MUM' ,'3/1/2021',    2010.3);
insert into Dim_stocks values('MUM' ,'3/1/2021',    4308.49);
insert into Dim_stocks values('MUM' ,'3/2/2021',    2690.75);
insert into Dim_stocks values('MUM' ,'3/5/2021',    3091.4);
insert into Dim_stocks values('COL' ,'2/10/2020',   4025.99);
insert into Dim_stocks values('COL' ,'2/28/2021',   4032.73);
insert into Dim_stocks values('COL' ,'2/1/2021',    3978.79);
insert into Dim_stocks values('COL' ,'11/23/2020',  4157.69);
insert into Dim_stocks values('PUN' ,'11/3/2020',   4032.13);
insert into Dim_stocks values('COL' ,'11/3/2020',   4025.99);
insert into Dim_stocks values('PUN' ,'11/1/2020',   4019.24);

select
    sum(c.price) over (partition by DATEADD(day, 1, EOMONTH(DATEADD(month, -1, c.tdate))) order by tdate rows between unbounded preceding and current row) as Price
    , c.tdate, c.unit
from Dim_stocks c 
     

输出：

价格	日期	单元
4025.99	2020-02-10	科尔
4019.24	2020-11-01	双关语
8051.37	2020-11-03	双关语
12077.36	2020-11-03	科尔
16235.05	2020-11-23	科尔
3978.79	2021-02-01	科尔
8011.52	2021-02-28	科尔
2010.3	2021-03-01	妈妈
6318.79	2021-03-01	妈妈
9009.54	2021-03-02	妈妈
12100.94	2021-03-05	妈妈

db<>在这里摆弄