Need SQL Query to return data for a specific date using Microsoft SQL Server-CodePudding

I have an sql query that was created by an experienced team member who we lost unexpectedly. I was able to modify a different query created by that team member, but can't seem to figure this one out. The query is used to print out writers' surveys. I need it to print out surveys from 10/1/2022 - 12/31/2022 (4th quarter). When I run the query, it shows me surveys for 2023, which is not what I'm looking for. I only know very basic sql statements, so I'm having trouble modifying it to print out what I need it to and I'm the only one on my team who has some basic knowledge of sql. Help would be greatly appreciated.

Here's the query:

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

CodePudding user response：

On the top of the query you use GETDATE(), which returns the current date year.

If you want to return values for 2022, please change to this, I've also added an end date since you had wanted to return a value between two data sets:

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

Hope this helps :)