I have a table whose columns are as follows:
CREATE TABLE Tests ( TestDate date, EntityID int)
Using a stored procedure, I want to find the ‘academic year’ of pairs of tests that happened in the same ‘academic year’, where ‘academic year’ is defined as year part of the test date after a fixed number of months (@AcYearOff, value is typically equal to 7) is subtracted. I do this using the following code:
CREATE TABLE #tmpTable ( vYear char(4), vDate char(8)) INSERT # tmpTable (vYear, vDate) SELECT DATEPART(yyyy, dateadd(MONTH, @AcYearOff, TestDate)), format(TestDate, 'MMM yyyy') FROM Tests WHERE Tests.EntityID = @ EntityID SELECT vYear FROM # tmpTable GROUP BY vYear HAVING count(vYear) = 2 ORDER BY vYear
The above code functions exactly as expected. I now want to add to this query a string that shows the actual month and year in which the two tests were taken. I added the column vDate to the temporary table for this purpose, but I cannot figure out how to combine the two vDate values and return them as a string.
To be clear, here is some example data from the Tests table:
2018-08-12, 21 2018-09-04, 22 2019-04-17, 21 2019-08-03, 21
and the stored procedure should return:
‘2018’, ‘Aug 2018 and Apr 2019’
I look forward to seeing your suggestions. Thank you.