--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