34,590
社区成员
日期 单据编号 物料编码 入库数量 仓库
2022-11-05 00:00:00.000 SCRK00034573 153899 645.0000000000 100284
2022-11-05 00:00:00.000 SCRK00034598 153927 498.0000000000 100284
2022-11-06 00:00:00.000 SCRK00034660 155336 672.0000000000 100292
2022-11-06 00:00:00.000 SCRK00034660 155336 672.0000000000 100292
2022-11-06 00:00:00.000 SCRK00034660 155336 672.0000000000 100292
2022-11-06 00:00:00.000 SCRK00034665 155258 1123.2000000000 100292
2022-11-06 00:00:00.000 SCRK00034665 155258 1123.2000000000 100292
2022-11-06 00:00:00.000 SCRK00034665 155258 842.4000000000 100292
2022-11-06 00:00:00.000 SCRK00034665 155258 1123.2000000000 100292
2022-11-06 00:00:00.000 SCRK00034665 155258 1404.0000000000 100292
2022-11-06 00:00:00.000 SCRK00034700 155255 786.2400000000 100292
2022-11-06 00:00:00.000 SCRK00034700 155255 786.2400000000 100292
2022-11-06 00:00:00.000 SCRK00034715 154006 2955.0000000000 100284
2022-11-06 00:00:00.000 SCRK00034716 153809 233.0000000000 100280
2022-11-06 00:00:00.000 SCRK00034725 153835 293.0000000000 100284
要求按照日期统计不同的 仓库FSTOCKID 100280当日入库和当月累计到当日入库的和值,做如下表输出
第一工序属于100284 | 第二工序100292 | 第三工序100280 | ||||
日产出 | 月产出 | 日产出 | 月产出 | 日产出 | 月产出 | |
物料编码 | 当日入库 | 本月累计入库 | 当日入库 | 本月累计产出 | 当日入库 | 本月累计产出 |
153899 | ||||||
153927 | ||||||
154114 | ||||||
155336 | ||||||
155258 | ||||||
155336 | ||||||
154006 | ||||||
153835 |
得用 case 表达式分类聚合,前两列的示意:
select 物料编码,
sum(case when cast(getdate() as date) = cast(日期 as date) and 仓库 = '100284' then 入库数量 else 0 end) 第一工序日产出,
sum(case when left(cast(getdate() as datetime2), 7) = left(cast(日期 as datetime2), 7) and 仓库 = '100284' then 入库数量 else 0 end) 第一工序月产出
from 表名
group by 物料编码
这里得转成 datetime2 格式再用 left 取年和月