sql - SQL - 财政年度年初至今计算
问题描述
我正在尝试计算按日历年分组查询的年初至今总计,但有一列以从财政年度开始的分钟为单位计算总时间(即从同年的 4 月 1 日开始,如果月份 >= 4 月,则该月份发生在否则为上一年的 4 月 1 日)
我已使用以下脚本尝试过此操作,但无法在 sum() over() 子句中使用 case 语句。
declare @yearmonth int = 4
declare @NumPreceeding int = case when right(@yearmonth,2) in (01,02,03) then 9+right(@yearmonth,1)
else (((12-(right(@yearmonth,2)+8)))*(-1))
end
select ColumnDescription
,sum(TotalMinutes) [TotalMinutes]
,sum(sum(TotalMinutes)) over (order by YearMonth rows between cast(@NumPreceeding as int) preceding and current row)
,YearMonth
from MyTable
where yearmonth between '201704' and '201706'
group by ColumnDescription ,YearMonth
order by yearmonth
你知道我怎样才能让它工作吗?
解决方案
如果您想要累积总和,那么我希望:
select ColumnDescription, YearMonth, sum(TotalMinutes) as TotalMinutes,
sum(sum(TotalMinutes)) over (partition by ColumnDescription order by YearMonth) as running_TotalMinutes
from MyTable
where yearmonth between '201704' and '201706'
group by ColumnDescription, YearMonth
order by yearmonth;
如果要连续多年执行此操作,则需要提取会计年度。这有点麻烦,但可行:
select ColumnDescription, YearMonth, sum(TotalMinutes) as TotalMinutes,
sum(sum(TotalMinutes)) over (partition by ColumnDescription, v.fiscal_year order by YearMonth) as running_TotalMinutes
from MyTable t cross apply
(values (case when right(yearmonth, 2) >= '04' then cast(left(yearmonth, 4) as int)
else cast(left(yearmonth, 4) as int) - 1
end)
) v(fiscal_year)
group by ColumnDescription, YearMonth
order by yearmonth;
推荐阅读
- forms - Yesod - 带有分页的单子表单,甚至带有分页的输入表单,有没有一种最佳的方法来实现它
- node.js - 如何将自定义功能添加到表达模块
- java - 如何通过 Spring-Feign 获取 InputStream?
- javascript - 图像剪辑中心
- c - 如何理解表达式?
- c# - How to get reference to GameObject without using Find() in C#/Unity?
- javascript - Electron ipcMain / ipcRenderer 交付保证
- ruby-on-rails - Rails - 如何在同一操作中重定向、闪烁错误/通知和下载 csv?
- workbox - 如何从服务工作者向 Workbox 类实例的“消息”事件发送消息?
- typescript - NuxtJS Auth 错误与属性不存在