In a t-sql 2012 database, I am suppose to count the number of times each 'canned' message is used for
Elementary students in the last school year and the current school year.
Right now I have the following sql that kind of works:
SELECT GS.Comments,Count(*) AS [Counts]
FROM dbo.Enrol Enrol
JOIN dbo.Student Student
ON Student.StudentID = Enrol.StudentID
JOIN dbo.GS GS
ON GS.StudentID = Student.Studentid
AND (GS.Comments IS NOT NULL)
AND (GS.Comments <> '')
where Enrol.grade in ('KG','01','02','03','04','05','06')
and Enrol.endYear between 2016 and 2017
Group by GS.Comments
order by Counts desc,GS.Comments asc
The problem is the GS.Comments field is defined as varchar(1200). There can be one message in the field and/or there can be lots of messages in this field. Each message ends with a period and there is a space between each message.
An example of multiple messages in the one GS.Comments field would look like the following:
The student is trying hard and needs to make their time more efficiently. This student is good at math. This student turns in their assignments on time. This student seems to enjoy school.
An example of when one messages is in the one GS.Comments field would look like the following:
This student seems to enjoy school.
Thus would showe me the t-sql 2012 logic that I can use when the GS.Comments field contains multiple messages and/or just one message so that I can count the number of times each unique message has been used?