For use on blog post: http://normansolutions.co.uk/post/schoolbase-rewards--sanctions-via-email

CreateRandSView.sql

CREATE VIEW DBA_RandSView
AS
SELECT TOP (100) PERCENT Sanctions.Sanction
	,Pupil.Pu_Surname
	,Pupil.Pu_GivenName
	,Years.YearDesc
	,PupSanction.PupSanDate
	,PupSanction.PupSanCount
	,PupSanction.PupNewNote
	,Staff.UserName
	,Sanctions.SanIdent
	,Pupil.PupOrigNum
	,PupSanction.PupSanNote
FROM dbo.PupSanction AS PupSanction
INNER JOIN dbo.Staff AS Staff ON PupSanction.UserIdent = Staff.UserIdent
INNER JOIN dbo.Pupil AS Pupil ON PupSanction.PupOrigNum = Pupil.PupOrigNum
INNER JOIN dbo.Sanctions AS Sanctions ON PupSanction.SanIdent = Sanctions.SanIdent
INNER JOIN dbo.Years AS Years ON Pupil.YearIdent = Years.YearIdent
ORDER BY Years.YearDesc
	,PupSanction.PupSanDate