求开始累计、年初到本期累计的SQL
来源:互联网 发布:易城数据官网 编辑:程序博客网 时间:2024/04/26 03:25
CREATE TABLE cs_f_CostStatus(
[costKPIKey] [int] NULL,
[orgKey] [int] NULL,
[dayKey] [int] NULL,
[actAmt] [numeric](19, 6) NULL,
[balAmt] [numeric](19, 6) NULL,
[recAmt] [numeric](19, 6) NULL,
)
在以上表结构,求实际产值actAmt、计量balAmt、回款recAmt的年累、开累,其中dayKey以200904的数字形式表示年月。
现有以下SQL,评价性能等方面的优缺点.
--一次求年累开累
select cs1.costKPIKey, cs1.orgKey, cs1.dayKey, sum(cs2.actAmt) as actAmtSum,
sum(cs2.balAmt) as balAmtSum, sum(cs2.recAmt) as recAmtSum,
sum(CASE WHEN (cs1.dayKey-(cs1.dayKey % 100)<=cs2.dayKey) THEN cs2.actAmt ELSE 0 END) as actAmtSumYear,
sum(CASE WHEN (cs1.dayKey-(cs1.dayKey % 100)<=cs2.dayKey) THEN cs2.balAmt ELSE 0 END) as balAmtSumYear,
sum(CASE WHEN (cs1.dayKey-(cs1.dayKey % 100)<=cs2.dayKey) THEN cs2.recAmt ELSE 0 END) as recAmtSumYear
from cs_f_CostStatus cs1
inner join cs_f_CostStatus cs2
on (cs1.costKPIKey=cs2.costKPIKey) and (cs1.orgKey=cs2.orgKey) and (cs1.dayKey>=cs2.dayKey)
group by cs1.costKPIKey, cs1.orgKey, cs1.dayKey
order by cs1.costKPIKey, cs1.orgKey, cs1.dayKey
--只求开累
select cs1.costKPIKey, cs1.orgKey, cs1.dayKey, sum(cs2.actAmt) as actAmtSumYear,
sum(cs2.balAmt) as balAmtSumYear, sum(cs2.recAmt) as recAmtSumYear
from cs_f_CostStatus cs1
inner join cs_f_CostStatus cs2
on (cs1.costKPIKey=cs2.costKPIKey) and (cs1.orgKey=cs2.orgKey) and (cs1.dayKey>=cs2.dayKey)
group by cs1.costKPIKey, cs1.orgKey, cs1.dayKey
--只求年累
select cs1.costKPIKey, cs1.orgKey, cs1.dayKey, sum(cs2.actAmt) as actAmtSumYear,
sum(cs2.balAmt) as balAmtSumYear, sum(cs2.recAmt) as recAmtSumYear
from cs_f_CostStatus cs1
inner join cs_f_CostStatus cs2
on (cs1.costKPIKey=cs2.costKPIKey) and (cs1.orgKey=cs2.orgKey) and (cs1.dayKey>=cs2.dayKey)
and (cs1.dayKey-(cs1.dayKey % 100)<=cs2.dayKey)
group by cs1.costKPIKey, cs1.orgKey, cs1.dayKey
- 求开始累计、年初到本期累计的SQL
- SQL 求按日累计
- 关于期初余额/本期合计/本年累计的算法。
- 累计
- 求SQL实现小计与累计?
- 今天开始点滴累计
- 一道累计求合计的解答
- sql server 点滴累计
- 帮朋友写的一个累计SQL代码,有分季度,和总累计
- 数据的累计
- Sql Server累计求和问题
- 使用SQL查询累计值
- SQL之累计和及累计差详解
- 出口变量增强- 截至到输入日期的本月累计
- sql 循环累计一个按日期的和
- 一段累计计算与折行并用的SQL
- SQL Server 语句:每月之前的金额累计
- SQL SERVER 2008 R2的累计补丁下载地址
- pku_1936 All in All
- Ant全攻略
- 一头猪重400斤,一座桥承重200斤,猪怎么过桥??(经典)
- pku_1953 World Cup Noise
- Ruby学习笔记(1)
- 求开始累计、年初到本期累计的SQL
- 关于LPTSTR
- 加我呀
- 中国移动今年将推手机应用程序商店
- 怎么实现????????什么思路!
- 【BASH】检测命令执行的结果
- 我的十年
- Windows mobile 下读取手机SIM卡信息
- WM5 WM6注册表修改大全(转)