0

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.

  • What version of SQL Server please? Also, why type yyyy instead of YEAR? – Aaron Bertrand Aug 8 at 16:23
  • version: SQL 2014 Why yyyy: Typo (but for some reason, it seems to have worked...) – GRoston Aug 8 at 17:40
3

In SQL Server 2017 or Azure SQL Database:

SELECT vYear, STRING_AGG(vDate, ' and ')
FROM #tmpTable
GROUP BY vYear
HAVING count(vYear) = 2
ORDER BY vYear;

In SQL Server 2016 or lower:

SELECT vYear, STUFF((SELECT ' and ' + vDate
  FROM #tmpTable
  WHERE vYear = t.vYear
  ORDER BY vDate
  FOR XML PATH, TYPE).value(N'.[1]',N'nvarchar(max)'),1,5,'')
FROM #tmpTable AS t
GROUP BY vYear
HAVING count(vYear) = 2
ORDER BY vYear;
  • Aaron - Would not have hit upon this in a million years. Tried your code and it pulled vDate for every entry that matched vYear. (Note that the real data has more entries that what was shown above.) Needless to say, simply inserting the GROUP BY and HAVING clauses into the inner SELECT does not work since vDate is not a part of those clauses. – GRoston Aug 8 at 17:48
  • Aaron - to be clearer: The real model is slightly more complex - there are two other columns that are used in the GROUP BY clause that I omitted from the example for simplicity. Thus, with this solution, the vDate for those entries get pulled in when vYear matches. – GRoston Aug 8 at 17:55
  • DUH! Just added those columns to the WHERE clause in the subquery. Works like a charm - thanks! – GRoston Aug 8 at 17:57
  • @GRoston Yeah, sorry, I didn't include your WHERE clause, I just dumped the sample data from the question into the #temp tables, so I didn't have to apply any further filters. – Aaron Bertrand Aug 8 at 18:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.