select sum(DATEDIFF("D",M.ActiveStart,DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0))) as Duration_Days
, m.HomeBranch, m.LocationName
from AX.Memberships M
where m.ActiveStart < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0)
and (M.ActiveEnd > DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0) or M.ActiveEnd is null)
group by m.HomeBranch, m.LocationName
我尝试使用CTE,但结果不同,我认为查询存在一些问题。上一个查询很简单,但是我想转换成CTE表。
with CTE_ABC AS (
select
SUM(CASE
When m.ActiveStart < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0) and (M.ActiveEnd >
DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) ,-1) or M.ActiveEnd is null)
then DATEDIFF(D,M.ActiveStart,DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0)) else 0 end) as Total_Duration
, M.HomeBranch
, M.LocationName
from AX.Memberships M
group by M.HomeBranch, M.LocationName
)
Select Total_Duration
From CTE_ABC
这是问题所在:
select DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) ,-1) --> this is end of the month
与...不同
select DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0) --> this is start of the month
因此,您的查询将产生不同的结果。
您的cte
外观应如下所示,因此原始查询的结果相同。
with CTE_ABC AS (
select
SUM(CASE
When m.ActiveStart< DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0) and (M.ActiveEnd >
DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0) or M.ActiveEnd is null)
then DATEDIFF(D,M.ActiveStart,DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0)) else 0 end) as
Total_Duration,
M.HomeBranch,
M.LocationName
from AX.Memberships M
group by M.HomeBranch,M.LocationName
)
Select Total_Duration From CTE_ABC
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句