如何计算同行中的日总数和月总数
如何计算同行中的日总数和月总数
提问于 2018-11-15 06:19:16
我需要一天的小现金总额(我已经得到这个)&需要每月总计与帐户代码有关的今天。接下来，我将显示每日总计的SQL。请有人帮我计算同列的每月总数。
SELECT DPetAcNo as AcNo,
SUM(DPetAmount) as DayTotal
FROM PettyDetail
WHERE DPetComCode='15'
and DPetLocCode='01'
and DPetDate=CONVERT(date,'20181113',111)
GROUP BY DPetAcNo以上查询的结果如下所示
根据计算结果，2018年/11/13 AcNo 009111共计=22 995.00，实际累计(2018/11/01 - 2018/11/13)共计009111 =136 265.42。
回答 4
Stack Overflow用户
回答已采纳
发布于 2018-11-15 06:34:33
regbas的修改后的答案应该表现得更好，因为它不考虑太多行：
SELECT DPetAcNo as AcNo,
SUM(case when DPetDate=CONVERT(date,'20181113',111) then DPetAmount else 0 end) as DayTotal,
SUM(DPetAmount) as MonthTotal
FROM PettyDetail
WHERE DPetComCode='15'
and DPetLocCode='01'
and DPetDate >= '2018-11-01'
AND DPetDate < '2018-12-01'
AND DPetAcNo IN (select dpetacno from pettydetail WHERE DPetComCode='15'
and DPetLocCode='01'
and DPetDate = '2018-11-13')
GROUP BY DPetAcNo如果要跳过转换代码混乱，Sqlserver将隐式地将‘yyyy’字符串转换为日期。拥有涵盖日期、代码、地区代码、帐户和金额的索引也会有所帮助。
--
处理此问题的另一种方法是使用您似乎已经为“日总计”和“累计总计”编写的查询，如下所示：
Select * from
(/*Insert your query that does daytotal into these brackets*/) d
Inner join
(/*Insert your query that does cumtotal into these brackets*/) c
ON d.DPetAcNo = c.dpetacnoStack Overflow用户
发布于 2018-11-15 06:23:11
尝尝这个
SELECT DPetAcNo as AcNo,
SUM(case when DPetDate=CONVERT(date,'20181113',111) then DPetAmount else 0 end) as DayTotal,
SUM(case when month(DPetDate)= 11 then DPetAmount else 0 end) as MonthTotal
FROM PettyDetail
WHERE DPetComCode='15'
and DPetLocCode='01'
GROUP BY DPetAcNoStack Overflow用户
发布于 2018-11-15 06:28:29
我假设您的DPetDate是日期或日期时间数据类型。
您需要将本月1日的行过滤到所需的日期。对于相同结果的日和月总计，在CASE中使用SUM ()
SELECT DPetAcNo as AcNo,
SUM(CASE WHEN DPetDate = '20181113' THEN DPetAmount ELSE 0 END) as DayTotal,
SUM(DPetAmount) as MonthTotal,
FROM PettyDetail
WHERE DPetComCode='15'
and DPetLocCode='01'
and DPetDate >= '20181101' -- from 1st of Nov
and DPetDate <= '20181113' -- to 13 of Nov
GROUP BY DPetAcNo编辑1:仅根据所需日期选择AccNo。使用子查询获取月份总计。或者，您也可以在CROSS APPLY中这样做。
SELECT *,
( SELECT SUM(DPetAmount)
FROM PettyDetail x
WHERE x.DPetAcNo = p.AcNo
AND x.DPetComCode = '15'
AND x.DPetLocCode = '01'
AND x.DPetDate >= '20180101'
AND x.DPetDate <= '20181113') as MonthTotal
FROM
(
SELECT DPetAcNo as AcNo,
SUM(CASE WHEN DPetDate = '20181113' THEN DPetAmount ELSE 0 END) as DayTotal
FROM PettyDetail p
WHERE DPetComCode='15'
and DPetLocCode='01'
and DPetDate = '20181113' -- for 13 of Nov only
GROUP BY DPetAcNo
) p页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/53313520
复制相关文章
相似问题
腾讯云开发者
