需要使用Microsoft SQL Server返回特定日期的数据

Question

我有一个由经验丰富的团队成员创建的sql查询，我们意外地失去了。我能够修改由该团队成员创建的另一个查询，但似乎无法解决这个问题。该查询用于打印作者的调查。我需要它打印2022年10月1日至2022年12月31日(第四季度)的调查。当我运行查询时，它显示了2023年的调查，这不是我想要的。我只知道非常基本的sql语句，所以我在修改它以打印出我需要的内容时遇到了麻烦，而且我是我的团队中唯一一个对sql有一些基本知识的人。非常感谢你的帮助。

下面是查询:

set datefirst 6  /* Saturday */
declare @StartDate as date = '1/1/' + convert(varchar(4),year(getdate()))
DROP TABLE IF EXISTS #WriterSurveys
DROP TABLE IF EXISTS #WriterResumes
Select * into #WriterSurveys
From
(
select
@StartDate as StartDate,
case month(@StartDate) 
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
and cf.SendDate >= @StartDate 
/*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
convert(date,cds.MovedIntoStepOn) >= @StartDate
) as p1
group by WriterUserID
) as WR

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

select
Qtr,
year(@StartDate) as 'Year',
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

Answer 1

在查询的顶部使用GETDATE()，它返回当前日期年份。

如果您想返回2022年的值，请更改为此，我还添加了结束日期，因为您想返回两个数据集之间的值:

set datefirst 6  /* Saturday */
declare @StartDate as date = '10/1/2022'
declare @EndDate as date = '12/31/2022'
DROP TABLE IF EXISTS #WriterSurveys
DROP TABLE IF EXISTS #WriterResumes
Select * into #WriterSurveys
From
(
select
@StartDate as StartDate,
case month(@StartDate) 
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
and cf.SendDate >= @StartDate 
and cf.SendDate <= @EndDate
/*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
convert(date,cds.MovedIntoStepOn) >= @StartDate
) as p1
group by WriterUserID
) as WR

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

select
Qtr,
year(@StartDate) as 'Year',
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

希望这对你有帮助:)