SQLserver 求1到3月，每月最后一天的各项的值

我写了一部分可以求1到3月的平均值，现在要求求去每月最后一天的各项值
sql语句如下

select
avg(case when year(D.RQ)=2021 and month(D.RQ)=1 then D.DYM  end) DYM1,
avg(case when year(D.RQ)=2021 and month(D.RQ)=1 then D.RY  end) RY1,
avg(case when year(D.RQ)=2021 and month(D.RQ)=2 then D.DYM  end) DYM2,
avg(case when year(D.RQ)=2021 and month(D.RQ)=2 then D.RY  end) RY2,
avg(case when year(D.RQ)=2021 and month(D.RQ)=2 then D.RYOU  end) RYOU2,
avg(case when year(D.RQ)=2021 and month(D.RQ)=3 then D.DYM  end) DYM3,
avg(case when year(D.RQ)=2021 and month(D.RQ)=3 then D.RY  end) RY3,
from DXTJ A left join GLY_JBXXB B on A.GLY=B.ID left join DEPT_JBXXB C on A.SGDW=C.DEPEID left join CSJING_SCSJB D on A.JH=D.JH
where JB='油井'

我想要达到的结果

求1到3月，每月最后一天的各项的值，谢谢大佬

你sqlserver的版本是多少？
如果是2012以上版本的话,可以用EOMONTH函数来取传入日期的月末最后一天,也就是说满足 "EOMONTH(日期)=日期" 的数据即为最后一天的数据；
如果是2012版本之前,则可以使用 "dateadd(d,-day(日期),dateadd(m,1,日期))=日期" 来取得最后一天的数据
至于这个条件写在哪,就看你完整的使用场景了,where里也行case when里也行,取出来的数据如果只有一行,max、sum、avg的结果都是一样的

请验证采纳，谢谢
--写了个函数，传入日期即可返回改日期当月最后一天
CREATE Function [dbo].[fn_GetLastDate]
(
    @date datetime
)
returns datetime
as
begin

declare @result datetime

 select @result = CHOOSE(month(@date),  
 DATEADD(DAY, 31 -day(@date), @date),
 IIF(YEAR(@date) % 4 = 0, DATEADD(DAY, 29 -day(@date), @date), DATEADD(DAY, 28 -day(@date), @date)), 
 DATEADD(DAY, 31 -day(@date), @date) ,
 DATEADD(DAY, 30 -day(@date), @date), 
 DATEADD(DAY, 31 -day(@date), @date),
 DATEADD(DAY, 30 -day(@date), @date), 
 DATEADD(DAY, 31 -day(@date), @date),
 DATEADD(DAY, 31 -day(@date), @date),
 DATEADD(DAY, 30 -day(@date), @date),
 DATEADD(DAY, 31 -day(@date), @date),
 DATEADD(DAY, 30 -day(@date), @date), 
 DATEADD(DAY, 31 -day(@date), @date))

 return convert(date, @result)

end


--调用函数
--很容易使用，参考下面2个例子：

select [dbo].[fn_GetLastDate]('2016-02-03 12:34:12')

select [dbo].[fn_GetLastDate](GETDATE())

分组求每月的最大日期，关联一下就可以了把

select
avg(case when year(D.RQ)=2021 and month(D.RQ)=1 then D.DYM  end) DYM1,
avg(case when year(D.RQ)=2021 and month(D.RQ)=1 then D.RY  end) RY1,
avg(case when year(D.RQ)=2021 and month(D.RQ)=2 then D.DYM  end) DYM2,
avg(case when year(D.RQ)=2021 and month(D.RQ)=2 then D.RY  end) RY2,
avg(case when year(D.RQ)=2021 and month(D.RQ)=2 then D.RYOU  end) RYOU2,
avg(case when year(D.RQ)=2021 and month(D.RQ)=3 then D.DYM  end) DYM3,
avg(case when year(D.RQ)=2021 and month(D.RQ)=3 then D.RY  end) RY3,
from DXTJ A left join GLY_JBXXB B on A.GLY=B.ID left join DEPT_JBXXB C on A.SGDW=C.DEPEID left join CSJING_SCSJB D on A.JH=D.JH 
JOIN (SELECT month(RQ) yf,max(RQ) RQ FROM CSJING_SCSJB GROUP BY month(RQ)) E on month(D.RQ)=E.yf and D.RQ=E.RQ
where JB='油井'