flysun027

declare @date datetime
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]) 產(chǎn)量
  FROM [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 (產(chǎn)量 is null) then 0  else 產(chǎn)量 end 產(chǎn)量 from t left join t2 on t.日期 = t2.日