Notes

select
e.EduID, e.EduName, e.ActivateContent as Content,e.EduReserve,CONVERT(char(16),A.ActDateStart,120) as ActDateStart,CONVERT(char(16),A.ActDateEnd,120) as ActDateEnd
,(SELECT TOP 1 ImgFile FROM T_EduMImg M WHERE m.EduID=e.EduID AND IsShow=1 Order By Sequence) as MImg
,(SELECT ImgSrc FROM T_Content c WHERE (CASE WHEN ISNULL(charindex(',',e.ContentMenueID),0) > 0 THEN left(e.ContentMenueID,charindex(',',e.ContentMenueID)-1) ELSE '0' END)=c.ContentID) as CImg
,isnull(stuff((
select '、' + ContentNameCH from T_Content
where CHARINDEX(',' + cast (ContentID as varchar) + ',',',' + e.ContentMenueID + ',') > 0 for XML PATH('')
),1,1,''),'') as Kind
,isnull(stuff((
select '、' + EduAreaName from T_EduArea
where CHARINDEX(',' + cast (EduAreaID as varchar) + ',',',' + A.T_EduArea + ',') > 0 for XML PATH('')
),1,1,''),'') as Area
,isnull(stuff((
select '、' + AgeName from T_Age
where CHARINDEX(',' + cast (AgeID as varchar) + ',',',' + e.Age + ',') > 0 for XML PATH('')
),1,1,''),'') as Age
FROM T_Edu as e
join
(
SELECT a.EduID,max(a.T_EduArea) as T_EduArea,min(b.ActDateStart) as ActDateStart,MAX(b.ActDateEnd) as ActDateEnd from T_EduEchelon as a
join (
SELECT ea.EduID,ea.EchelonID,min(ea.ActDateStart) as ActDateStart,MAX(ea.ActDateEnd) as ActDateEnd from T_EduActivit as ea
left join T_Edu as e on e.EduID=ea.EduID
where datediff(day, getdate(), ea.ActDateStart) > e.SignDayLimit
and isNull(ea.CloseStatus,0)=0
GROUP BY ea.EduID,ea.EchelonID
) as b on a.EduID=b.EduID AND a.idx=b.EchelonID
GROUP by a.EduID
) as A ON A.EduID=e.EduID and convert(nvarchar,A.ActDateEnd,111) >= CONVERT(nvarchar,GETDATE(),111)
where e.EduReserve in(2,3,4,5) and e.Publish='1'