--Teachers with kids and no gradebooks select sc, tn, te, tln, tf, em from tch where --## Teachers with kids logic (tn in (select tn from mst where ts > 0 and del = 0 and stg = ' ' and sc = tch.sc) or tn in (select cu from stu where del = 0 and rtrim(tg) = '' and sc = tch.sc)) --## Teachers with kids logic end and tn not in (select tn from gbk where del = 0 and sc = tch.sc) --Teachers with kids and gradebook counts select sc, tn, te, tln, tf, em, (select count(*) from gbk where tn = tch.tn and sc = tch.sc and del = 0) as HowManyGradebooks from tch where --## Teachers with kids logic (tn in (select tn from mst where ts > 0 and del = 0 and stg = ' ' and sc = tch.sc) or tn in (select cu from stu where del = 0 and rtrim(tg) = '' and sc = tch.sc)) --## Teachers with kids logic end --Teacher gradebooks including 'last updated score' date SELECT GBK.SC AS School, TCH.TN AS [Teacher Number], TCH.TE AS [Teacher Name], GBK.PD AS Period, GBK.NM AS [Course Name], GBK.TM AS GradeBookTerm, CONVERT(CHAR(10), GBK.D1, 101) AS [Begin Date], CONVERT(CHAR(10), GBK.D2, 101) AS [End Date] ,isnull((SELECT CONVERT(CHAR(10), MAX(GBU.DTS) , 101) FROM GBU where GBU.gn = GBK.gn and GBU.del = 0 GROUP BY GBU.GN),'') AS [Last Updated Score] ,GBE.SE AS Section ,MST.SM AS SectionTerm FROM GBK LEFT JOIN TCH ON TCH.TN = GBK.TN AND TCH.SC = GBK.SC LEFT JOIN GBE ON GBE.GN = GBK.GN LEFT JOIN MST ON GBE.SE = MST.SE AND MST.SC = TCH.SC where gbk.del = 0 GROUP BY GBK.SC, TCH.TN, TCH.TE, GBK.PD, GBK.NM, GBK.TM, GBK.D1, GBK.D2, GBK.SC, GBK.GN, GBE.SE, MST.SM order by GBK.sc, tch.tn, gbk.pd --Teacher gradebook utilization SELECT GBK.SC AS School, TCH.TN AS [Teacher Number], TCH.TE AS [Teacher Name], GBK.PD AS Period, GBK.NM AS [Course Name], GBK.TM AS GradeBookTerm, CONVERT(CHAR(10), GBK.D1, 101) AS [Begin Date], CONVERT(CHAR(10), GBK.D2, 101) AS [End Date] ,isnull((SELECT CONVERT(CHAR(10), MAX(gbs.DTS) , 101) FROM GBS where gbs.gn = GBK.gn and gbs.del = 0 and rtrim(gbs.mk) <> '' GROUP BY GBS.GN),'') AS [Last Updated Score] ,isnull((SELECT count(*) FROM GBS where gbs.gn = GBK.gn and gbs.del = 0 and gbs.dts >= (CONVERT(CHAR(10),current_timestamp-7, 101)) and rtrim(gbs.mk) <> '' GROUP BY GBS.GN),'') AS [Student Scores Changed within Last Week] ,isnull((SELECT count(*) FROM GBS where gbs.gn = GBK.gn and gbs.del = 0 and gbs.dts >= (CONVERT(CHAR(10),current_timestamp-14, 101)) and rtrim(gbs.mk) <> '' GROUP BY GBS.GN),'') AS [Student Scores Changed within Last Two Weeks] ,isnull((SELECT count(*) FROM GBS where gbs.gn = GBK.gn and gbs.del = 0 and gbs.dts >= (CONVERT(CHAR(10),current_timestamp-30, 101)) and rtrim(gbs.mk) <> '' GROUP BY GBS.GN),'') AS [Student Scores Changed within Last 30 days] ,GBE.SE AS Section ,MST.SM AS SectionTerm FROM GBK LEFT JOIN TCH ON TCH.TN = GBK.TN AND TCH.SC = GBK.SC LEFT JOIN GBE ON GBE.GN = GBK.GN LEFT JOIN MST ON GBE.SE = MST.SE AND MST.SC = TCH.SC where gbk.del = 0 GROUP BY GBK.SC, TCH.TN, TCH.TE, GBK.PD, GBK.NM, GBK.TM, GBK.D1, GBK.D2, GBK.SC, GBK.GN, GBE.SE, MST.SM order by GBK.sc, tch.tn, gbk.pd