如何重置月初至今运行合计
如何重置月初至今运行合计
提问于 2016-08-10 18:58:57
SELECT DISTINCT
ACCOUNTDATE,
PROPERTYNAME,
rt.management
FROM
aaa t
CROSS APPLY
(SELECT
SUM(MANAGEMENT) AS management
FROM
aaa
WHERE
PROPERTYNAME = t.PROPERTYNAME
AND ACCOUNTDATE <= t.ACCOUNTDATE) as rt
WHERE
AccountDate BETWEEN @STARTOFMONTH_MAN AND @ENDOFMONTH_MAN
ORDER BY
AccountDate这是我的查询，我想要计算当月到目前为止的运行总数，例如:来自1/08/2016-31/08/2016的运行总数，并且再次从1/09/2016-31/09/2016的开始重置总数。
但从上面的查询中，我无法重置下个月的总数。
回答 5
Stack Overflow用户
回答已采纳
发布于 2016-08-10 19:07:10
尝试
SELECT DISTINCT
ACCOUNTDATE
,PROPERTYNAME
,rt.management
from aaa t
cross apply
(select SUM(MANAGEMENT) as management
from aaa
where
PROPERTYNAME = t.PROPERTYNAME and
ACCOUNTDATE BETWEEN dateadd(MONTH, datediff(MONTH, 0,t.ACCOUNTDATE),0) -- start of month
AND t.ACCOUNTDATE
) as rt
WHERE AccountDate BETWEEN @STARTOFMONTH_MAN AND @ENDOFMONTH_MAN
ORDER BY AccountDateStack Overflow用户
发布于 2016-08-10 19:02:08
如果需要运行总计，则在SQL Server 2012+中，您可以使用：
select aaa.*,
sum(management) over (partition by year(accountdate), month(accountdate)
order by accountdate
) as rt
from aaa
where AccountDate between @STARTOFMONTH_MAN and @ENDOFMONTH_MAN
order by Accountdate;在早期版本中，您可以使用cross apply并在逻辑中包含年份和月份：
SELECT t.*, rt.management
from aaa t cross apply
(select SUM(MANAGEMENT) as management
from aaa t2
where t2.PROPERTYNAME = t.PROPERTYNAME and
year(t2.accountdate) = year(t.accountdate) and
month(t2.accountdate) = month(t.accountdate) and
t2.ACCOUNTDATE <= t.ACCOUNTDATE
) rt
WHERE AccountDate BETWEEN @STARTOFMONTH_MAN AND @ENDOFMONTH_MAN
ORDER BY AccountDate;Stack Overflow用户
发布于 2016-08-10 19:06:15
如果您没有使用SQL-Server 2012+，则必须使用相关查询(或某种连接)：
SELECT DISTINCT t.ACCOUNTDATE
,t.PROPERTYNAME
,(SELECT SUM(s.MANAGMENT) FROM aaa s
WHERE MONTH(t.accountdate) = MONTH(s.accountdate)
AND YEAR(t.accountdate) = YEAR(s.accountdate)
AND s.PROPERTYNAME = t.PROPERTYNAME) as rt
from aaa t
WHERE t.AccountDate BETWEEN @STARTOFMONTH_MAN AND @ENDOFMONTH_MAN
ORDER BY t.AccountDate页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/38871498
复制相关文章
相似问题
腾讯云开发者
