Sql Server 按日统计产量
发布时间 2023-12-07 16:37:51作者: flysun027
set @date= '2023-12-01 00:00:00.000';
with t as
(
select number 日期
from master..spt_values with(nolock)
where type= N'P'
and number >= 1
and number <= datediff(DAY, DATEADD(MONTH, DATEDIFF(MONTH,0,@date), 0), DATEADD(MONTH, DATEDIFF(MONTH, 0, @date) + 1, 0))
), t2 as
(
SELECT DATEPART(day, DateTime_Offload) AS [日], count( [ID]) 产量
FROM [A53_AMES].[dbo].[Production_Data]
where DateTime_Offload > dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0, @date), 0)) and DateTime_Offload < dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0, @date) + 1, 0)) and Station_Offload = 'OP265'
group by DATEPART(day, DateTime_Offload)
)
select 日期, case when (产量 is null) then 0 else 产量 end 产量 from t left join t2 on t.日期 = t2.日