修改SQL Server查询以返回2022年Q4作家调查数据求助

问题分析 #

原查询有三个核心问题导致返回2023年数据：

• 变量@StartDate依赖year(getdate())获取当前年份，默认指向当年（如2023），而非目标的2022年
• Qtr字段计算基于@StartDate的月份，而非实际调查发送日期SurveySendDate的月份，导致所有数据被错误归类到@StartDate所在季度
• 未设置日期上限，查询会包含@StartDate之后的所有数据（比如2023年的记录）

修改后的查询代码 #

set datefirst 6  /* 把周六设为每周第一天 */

-- 直接指定2022年第四季度的起止日期
declare @Q4StartDate as date = '2022-10-01'
declare @Q4EndDate as date = '2022-12-31'

DROP TABLE IF EXISTS #WriterSurveys
DROP TABLE IF EXISTS #WriterResumes

Select * into #WriterSurveys
From
(
select
  @Q4StartDate as StartDate,
  -- 基于实际调查发送日期计算季度，而非固定变量的月份
  case month(cf.SendDate) 
       when 1 then 1
       when 2 then 1
       when 3 then 1
       when 4 then 2
       when 5 then 2
       when 6 then 2
       when 7 then 3
       when 8 then 3
       when 9 then 3
       when 10 then 4
       when 11 then 4
       when 12 then 4
  else 0
  end as Qtr,
  cd.WriterUserID,
  u.lastname + ', ' + u.firstname as WriterName,
  cfsa.SurveyAnswer,
  case cfsa.SurveyAnswer
       when 1 then 'Excellent'
       when 2 then 'Very Good'
       when 3 then 'Good'
       when 4 then 'Fair'
       when 5 then 'Poor'
  else 'N/A'
  end as SurveyAnswerText,
  cf.ClientID,
  c.lastname + ', ' + c.firstname as ClientName,
  cf.SendDate as SurveySendDate,
  cf.ReceivedDate as SurveyReceivedDate,
  cd.ClientDocumentTypeID,
  cd.IsResumeASAP,
  cd.IsResumeOnly,
  cd.IsRush
from ClientForm cf 
Left outer join ClientDocument cd on cd.ClientID = cf.ClientID
left outer join ClientFormSurveyAnswer cfsa on cfsa.SurveyQuestionID = 202 and cfsa.ClientFormID = cf.ClientFormID
left outer join [User] u on u.UserID = cd.WriterUserID
left outer join Client c on c.ClientID = cf.ClientID
where cf.FormTypeID = 7
      -- 限定发送日期在2022年第四季度范围内
      and cf.SendDate >= @Q4StartDate 
      and cf.SendDate <= @Q4EndDate
      /*and cf.ReceivedDate is not null*/
) as WS

Select * into #WriterResumes
From
(
select 
  count(ClientID) as NumResumes,
  max(WriterUserID) as WriterUserID
from
(
select 
  ClientID,
  cd.ClientDocumentID,
  cds.ClientDocumentWorkflowStepID,
  convert(date,cds.MovedIntoStepOn) as Step3Date,
  cd.WriterUserID
from ClientDocument cd
inner join ClientDocumentStep cds on cds.ClientDocumentID = cd.ClientDocumentID
left outer join [user] u on u.UserID = cd.CreatedBy
where cd.ClientDocumentTypeID = 5 and
      cds.ClientDocumentWorkflowStepID = 4 and
      -- 同样限定简历流程日期在2022年第四季度范围内
      convert(date,cds.MovedIntoStepOn) >= @Q4StartDate
      and convert(date,cds.MovedIntoStepOn) <= @Q4EndDate
) as p1
group by WriterUserID

) as WR


/*
select 
  * 
from #WriterSurveys
order by Qtr,
         WriterName
*/


select
  Qtr,
  year(@Q4StartDate) as 'Year', -- 显示目标年份2022
  WriterName,
  /*sum(isnull(NumResults0,0)) as 'N/A',*/
  sum(isnull(NumResults1,0)) as 'Excellent(1)',
  sum(isnull(NumResults2,0)) as 'Very Good(2)',
  sum(isnull(NumResults3,0)) as 'Good(3)',
  sum(isnull(NumResults4,0)) as 'Fair(4)',
  sum(isnull(NumResults5,0)) as 'Poor(5)',

  isnull(
  convert(decimal(10,2),convert(decimal(10,2),(
  sum(isnull(NumResults1,0))*1 +
  sum(isnull(NumResults2,0))*2 +
  sum(isnull(NumResults3,0))*3 +
  sum(isnull(NumResults4,0))*4 +
  sum(isnull(NumResults5,0))*5)) /
  convert(decimal(10,2),(sum(TotalResp))))
  ,0) as AverageRate,

  isnull(
  convert(decimal(10,2),convert(decimal(10,2),(
  sum(isnull(NumResults1,0)) +
  sum(isnull(NumResults2,0)) +
  sum(isnull(NumResults3,0)) )) /
  convert(decimal(10,2),(sum(TotalResp)))) * 100 
  ,0) as '% E/VG/G',

  sum(isnull(TotalResp,0)) as TotalResp,
  sum(TotalSent) as TotalSent,

  isnull(max(wr.NumResumes),0) as NumResumes
from
(
select
  Qtr,
  WriterUserID,
  WriterName,
  SurveyAnswer,
  case when SurveyAnswer = 0 then count(clientid) end as NumResults0,
  case when SurveyAnswer = 1 then count(clientid) end as NumResults1,
  case when SurveyAnswer = 2 then count(clientid) end as NumResults2,
  case when SurveyAnswer = 3 then count(clientid) end as NumResults3,
  case when SurveyAnswer = 4 then count(clientid) end as NumResults4,
  case when SurveyAnswer = 5 then count(clientid) end as NumResults5,
  case when SurveyAnswer > 0 then count(clientid) end as TotalResp,
  count(clientid) as TotalSent
from #WriterSurveys
group by Qtr,
         WriterName,
         WriterUserID,
         SurveyAnswer
) as p1
left outer join #WriterResumes wr on wr.WriterUserID = p1.WriterUserID
/*where TotalResp > 0*/
group by Qtr,
         WriterName
order by Qtr,
         WriterName

关键修改点说明 #

• 新增@Q4EndDate变量，明确指定季度结束日期，避免包含后续年份数据
• 将@StartDate替换为固定的2022年第四季度起始日期，不再依赖当前系统时间
• 修改Qtr字段的计算逻辑，基于cf.SendDate的月份来判断所属季度，确保数据归类正确
• 在两个临时表的过滤条件中同时加入<= @Q4EndDate，严格限定查询范围在2022年第四季度内

内容的提问来源于stack exchange，提问作者kzs