函数dateadd功能:在向指定日期加上一段时间的基础上,返回新的 datetime 值,例如:向日期加上2天 得到的结果如下:select dateadd(day,2,'2020-10-15') --返回:2020-10-17 00:00:00.000
--统计预计补贴截止时间
select a.ryid,b.xm,b.sfzh,b.xl,count(DISTINCT a.btny) btcs,sum(a.btje) bthj,
STUFF(
(
SELECT ',' + RTRIM(convert(varchar(7),t.btny,120)) FROM (SELECT DISTINCT btny FROM [shbt_je] WHERE ryid = a.ryid)t FOR XML PATH('')
),
1,
1,
''
) AS value ,
min(btny) as fbt,
CASE WHEN b.xl='本科' THEN dateadd(month,60,min(btny))
WHEN b.xl='硕士研究生' THEN dateadd(month,60,min(btny))
WHEN b.xl='博士研究生' THEN dateadd(month,60,min(btny))
ELSE NULL END
as lbt
from shbt_je a
inner join shbt_ry b
on a.ryid=b.ryid group by a.ryid,b.xm,b.sfzh,b.xl