Assign all records which have the same ID, an existing value in a column from the result set in SQL-CodePudding

I have a query result set which looks something like this:

CASEID	AssessmentID	QUESTION	QUARTER	ANSWER
1	1	WhatQuarter	Q1	Q1
1	1	QuestionB	NULL	Y
1	1	QuestionC	NULL	Z
1	19	WhatQuarter	Q2	Q2
1	19	QuestionB	NULL	Y
1	19	QuestionC	NULL	Z
2	3	WhatQuarter	Q1	Q1
2	3	QuestionB	NULL	Y
2	3	QuestionC	NULL	Z
3	7	WhatQuarter	Q3	Q3
3	7	QuestionB	NULL	Y
3	7	QuestionC	NULL	Z

What I would like to do is to assign the value in the Quarter column to all records in these results which have the same ID. I want my results to look something like this:

CASEID	AssessmentID	QUESTION	QUARTER	ANSWER
1	1	WhatQuarter	Q1	Q1
1	1	QuestionB	Q1	Y
1	1	QuestionC	Q1	Z
1	19	WhatQuarter	Q2	Q2
1	19	QuestionB	Q2	Y
1	19	QuestionC	Q2	Z
2	3	WhatQuarter	Q1	Q1
2	3	QuestionB	Q1	Y
2	3	QuestionC	Q1	Z
3	7	WhatQuarter	Q3	Q3
3	7	QuestionB	Q3	Y
3	7	QuestionC	Q3	Z

I know there is some smart way using CASE WHEN to achieve this, but I cannot seem to figure it our. Ideally this would not involve any temp tables. The database is designed poorly in this respect as it does not record the dates that assessments were started so this means I have to use CASE WHEN to generate the quarter column. Any help would be much appreciated, thanks :)

EDIT:

Query is as follows, with CaseReference = CaseID in example ASVFieldName = Question

The other columns match names.

USE MyPersonalSupport_reporting

DECLARE @StartDate AS DATE 
SET @StartDate = '2021-10-01' 

DECLARE @EndDate AS DATE 
SET @EndDate = '2021-12-31'  

SELECT

*

FROM 


(SELECT 

SubContractName,
CCC.CaseReference,
AssessmentTemplate,
CCC.Keyworker,
ASVFieldName,
CASE WHEN ASVFieldName = 'Level_of_support' THEN LookupAnswer ELSE NULL END AS LevelOfSupport,
CASE WHEN (ASVFieldName = 'Quarter') OR (ASVFieldName LIKE '%What_quarter%') THEN LookupAnswer ELSE NULL END AS [Quarter],
ASVType,
ASVValue,
CASE WHEN Answer IS NULL THEN AAA.LookupAnswer ELSE Answer END AS Answer,
CCC.CurrentCaseStatus,
FullName AS CustomerFullName,
DateReceived,
LIVE,
ServiceEndDate,
ASSApprovalStatus,
ASTPublishLevel,
AAA.ClientModified,
AAA.ClientCreated,
AAA.AssessmentId


FROM mps.CaseContractCustomer as CCC 

LEFT JOIN mps.CrossTabCaseStatusChangeDates as CSCDV ON CCC.CaseID = CSCDV.CaseID
LEFT JOIN mps.AssessmentsQuarterEndAnswers AS AAA ON CCC.CaseID = AAA.CaseId


WHERE 

CSCDV.Live < @EndDate

AND (CCC.ServiceEndDate > @StartDate OR CCC.ServiceEndDate IS NULL)

AND AssessmentTemplate LIKE 'Quarter%'


) AS X

WHERE ClientCreated > DATEADD(week,-3,@StartDate)

ORDER BY X.SubContractName, X.CaseReference, X.AssessmentId, X.Quarter DESC, X.KeyWorker, X.ClientModified OFFSET 0 ROWS

CodePudding user response：

then you have to provide your query but here is one way:

select * , max(QUARTER) over (partition by CASEID, AssessmentID) as new_quarter
from yourtable

so to incorporate that into your query:

select ... , 
max(CASE WHEN (ASVFieldName = 'Quarter') OR (ASVFieldName LIKE '%What_quarter%') THEN LookupAnswer
                  ELSE NULL
              END) over (partition by CASEID, AssessmentID) as [Quarter] 
,...