22,210
社区成员
发帖
与我相关
我的任务
分享SQL分周期统计问题请教
SID sDate fDate aMount
1 2016-01-01 2016-03-28 2000
2 2016-02-02 2016-03-08 1000
3 2016-02-26 2016-04-18 3000
4 2016-03-05 2016-04-03 500
5 2016-04-10 2016-05-30 4000
如上数据
sql获取时间区间内本月25日和下月24日，开始时间为sDate的最小值，完成时间为fDate的最大值
其中aMount值根据每条记录在统计周期中所占的天数来算出每条记录在所属周期内的金额值，最后将每条记录在每个统计周期中的值分别加起来，算出每个统计周期的汇总值，再通过每个统计周期+截止上个统计周期的汇总值除以所有记录aMount的汇总值，再算出每个统计周期的占比
如：
第一条记录在他所属的第一个统计周期内的金额为
(DATEDIFF(day,'2016-01-01','2016-01-24'))*2000/ DATEDIFF(day,'2016-01-01','2016-01-24')
最后的数据展示为：
sDate fDate aMount lJaMount Com_rate
2016-01-01 2016-01-24
2016-01-25 2016-02-24
2016-02-25 2016-03-24
2016-03-25 2016-04-24
2016-05-25 2016-05-30
1 2016-01-01 2016-03-28 2000
2 2016-02-02 2016-03-08 1000
3 2016-02-26 2016-04-18 3000
4 2016-03-05 2016-04-03 500
5 2016-04-10 2016-05-30 4000
如上数据
sql获取时间区间内本月25日和下月24日，开始时间为sDate的最小值，完成时间为fDate的最大值
其中aMount值根据每条记录在统计周期中所占的天数来算出每条记录在所属周期内的金额值，最后将每条记录在每个统计周期中的值分别加起来，算出每个统计周期的汇总值，再通过每个统计周期+截止上个统计周期的汇总值除以所有记录aMount的汇总值，再算出每个统计周期的占比
如：
第一条记录在他所属的第一个统计周期内的金额为
(DATEDIFF(day,'2016-01-01','2016-01-24'))*2000/ DATEDIFF(day,'2016-01-01','2016-01-24')
最后的数据展示为：
sDate fDate aMount lJaMount Com_rate
2016-01-01 2016-01-24
2016-01-25 2016-02-24
2016-02-25 2016-03-24
2016-03-25 2016-04-24
2016-05-25 2016-05-30
...全文
请发表友善的回复…
发表回复
Tiger_Zhao 2017-02-09
- 打赏
- 举报
分项目自动生成时段表。
程序让人工指定业务的起止日期（用本项目已有数据的起止日期校验），自动生成时段记录；
人工录入数据时进行日期校验，不许录入起止日期外的数据——提示用户修改起止日期。
程序让人工指定业务的起止日期（用本项目已有数据的起止日期校验），自动生成时段记录；
人工录入数据时进行日期校验，不许录入起止日期外的数据——提示用户修改起止日期。
weixin_36785129 2017-02-09
- 打赏
- 举报
A、时间范围是从（sDate,fDate）中取出来的，开始时间为sDate的最小时间，完成时间为fDate的最大时间，而其中sDate,fDate都是人为录入的；
B、因为系统中存在多个项目，每个项目都有时间周期，手动维护不太现实，所以只能自动去取作业项中的最小开始时间、最大完成时间
Tiger_Zhao 2017-02-09
- 打赏
- 举报
A)你没有查询的过滤范围吗？
比如统计2016年下半年的数据，可以预先求出 2016-05-25、2016-06-24
B)总是由实际业务产生数据的
那么你建个时段表，把第一次业务至今的所有时段预先填好，用来替换CTE语句中的 a
比如统计2016年下半年的数据，可以预先求出 2016-05-25、2016-06-24
B)总是由实际业务产生数据的
那么你建个时段表，把第一次业务至今的所有时段预先填好，用来替换CTE语句中的 a
weixin_36785129 2017-02-08
- 打赏
- 举报
[Quote=引用 12 楼 weixin_36785129 的回复:]能不能将第一个统计周期的开始时间改为上月的25日，最后一个统计周期的完成时间修改为下月24日，
这样的话，整个统计周期看起来就比较整体、比较规则，如果后续要跟实际统计周期进行关联的话也能匹配上 [/Quote]
这才是合理的常规做法！
[/quote]
其table1中的数据是不定的，应该不能直接用2015-12-25,2016-1-25固定住吧
weixin_36785129 2017-02-08
- 打赏
- 举报
大哥，1#的语句最后进行调整，
其中日期区间，这样直接写有点问题，我将目前全部的代码贴出来你看看，应该怎么样修改，谢谢
;WITH table1 ( SID, sDate, fDate, amount_plan )
AS (
SELECT '143EA571-3772-42DF-8E8F-6C638D8405B0','2017-06-26','2017-07-21',16000 UNION ALL
SELECT '2095DF0B-B5F0-48EC-A1C0-2A8E5EBAAD22','2017-02-09','2017-03-22',240000 UNION ALL
SELECT '3DC52EEC-9656-4BFE-8B09-CE25C8A402BE','2017-05-11','2017-06-13',192000 UNION ALL
SELECT '6E894637-B5F6-4DBB-BB68-5F1AF51C8913','2017-04-20','2017-05-10',120000 UNION ALL
SELECT '6F69296E-3DD5-44E8-8FF3-23455BB9BBA0','2017-07-24','2017-08-04',80000 UNION ALL
SELECT 'A2919D17-1876-4761-A5FA-CA3CC95E6744','2017-02-23','2017-04-19',320000 UNION ALL
SELECT 'A74985B1-3D8C-486B-9427-A0F0966D89B9','2017-01-12','2017-01-31',112000 UNION ALL
SELECT 'BC6BFFF7-58E3-47AA-B4BD-BF521DF9717C','2017-06-14','2017-07-07',56000 UNION ALL
SELECT 'DFB2C4AA-EE19-4BD6-9FB6-0AA34303B683','2017-01-12','2017-02-08',14000
),
z0 ( sDate, fDate )
AS ( SELECT MIN(sDate) AS sDate ,MAX(fDate) AS fDate
FROM table1
),
z1 ( sDate, fDate, MIN_sDate, MAX_fDate )
AS ( SELECT CASE WHEN DAY(sDate) <= 24
THEN DATEADD(month, -1,
DATEADD(day, 25 - DAY(sDate),
sDate))
ELSE DATEADD(day, 25 - DAY(sDate), sDate)
END ,
CASE WHEN DAY(sDate) <= 24
THEN DATEADD(day, 24 - DAY(sDate), sDate)
ELSE DATEADD(month, 1,
DATEADD(day, 24 - DAY(sDate),
sDate))
END ,sDate ,fDate
FROM z0
),
a ( aID, sDate, fDate )
AS ( -- 日期区间
SELECT n.number + 1 ,
CASE WHEN n.number = 0 THEN z1.MIN_sDate
ELSE DATEADD(month, n.number, z1.sDate)
END ,
CASE WHEN z1.MAX_fDate <= DATEADD(month,
n.number,
z1.fDate)
THEN z1.MAX_fDate
ELSE DATEADD(month, n.number, z1.fDate)
END
FROM z1
JOIN ( SELECT number
FROM master..spt_values
WHERE type = 'p'
) n ON DATEADD(month, n.number, sDate) < = MAX_fDate
),
b AS ( -- 切割日期区间
SELECT a.aID ,
CASE WHEN a.sDate > t.sDate THEN a.SDate
ELSE t.sDate
END AS sDate ,
CASE WHEN a.fDate < t.fDate THEN a.fDate
ELSE t.fDate
END AS fDate ,
DATEDIFF(day, t.sDate, t.fDate) + 1 AS totalDays ,
t.amount_plan AS totalAmount
FROM a
JOIN table1 t ON a.sDate <= t.fDate
AND t.sDate <= a.fDate
),
c ( aID, amount_plan )
AS ( -- 分隔 amount
SELECT aID ,CONVERT(MONEY, totalAmount * 1.0* ( DATEDIFF(day, sDate, fDate) + 1 )/ totalDays)
FROM b
),
d ( amount_plan )
AS ( -- 合计 amount
SELECT SUM(amount_plan)
FROM c
),
e ( aID, amount_plan )
AS ( -- 分段统计 amount
SELECT aID ,SUM(amount_plan)
FROM c
GROUP BY aID
),
f ( aID, amount_plan, LJamount_plan )
AS ( -- 分段统计 LJamount
SELECT e1.aID ,MAX(e1.amount_plan) ,SUM(e2.amount_plan)
FROM e e1
JOIN e e2 ON e2.aID <= e1.aID
GROUP BY e1.aID
)
SELECT a.aID,CONVERT(varchar(100), a.sDate, 23)sDate,CONVERT(varchar(100), a.fDate, 23)fDate,
f.amount_plan ,
f.LJamount_plan ,
ISNULL(F.amount_plan, 0)/ d.amount_plan AS Com_rate_plan,
ISNULL(F.LJamount_plan, 0) / d.amount_plan AS LJ_Com_rate_plan
FROM a
JOIN d ON 1 = 1
LEFT JOIN f ON a.aid = f.aid Tiger_Zhao 2017-02-07
- 打赏
- 举报
[Quote=引用 12 楼 weixin_36785129 的回复:]能不能将第一个统计周期的开始时间改为上月的25日，最后一个统计周期的完成时间修改为下月24日，
这样的话，整个统计周期看起来就比较整体、比较规则，如果后续要跟实际统计周期进行关联的话也能匹配上 [/Quote]
这才是合理的常规做法！
修改#1语句：
这样的话，整个统计周期看起来就比较整体、比较规则，如果后续要跟实际统计周期进行关联的话也能匹配上 [/Quote]
这才是合理的常规做法！
修改#1语句：
...
a(aID,sDate,fDate) AS ( -- 日期区间
SELECT number + 1,
DATEADD(month,number,'2015-12-25'),
DATEADD(month,number,'2016-01-24')
FROM master..spt_values
WHERE type = 'p'
AND number < 6
),
... aID sDate fDate amount LJamount Com_rate
----------- ---------- ---------- --------------------- --------------------- ---------------------
1 2015-12-25 2016-01-24 545.4545 545.4545 0.0519
2 2016-01-25 2016-02-24 1343.4344 1888.8889 0.1798
3 2016-02-25 2016-03-24 2938.441 4827.3299 0.4597
4 2016-03-25 2016-04-24 2849.1407 7676.4706 0.731
5 2016-04-25 2016-05-24 2352.9412 10029.4118 0.9551
6 2016-05-25 2016-06-24 470.5882 10500.00 1.00weixin_36785129 2017-02-02
- 打赏
- 举报
能不能将第一个统计周期的开始时间改为上月的25日，最后一个统计周期的完成时间修改为下月24日，
这样的话，整个统计周期看起来就比较整体、比较规则，如果后续要跟实际统计周期进行关联的话也能匹配上
T-Share 2017-01-27
- 打赏
- 举报
留用
中国风 2017-01-22
- 打赏
- 举报
#4的方式测试了下，日期拆分了，后面的金额算的不太对
复制提示rowS附近有语法错误，rows 没有用过 [/quote]
这是SQL2012支持的用法
算法，按天数拆分AMOUNT,用这功能是递减法
比如：10拆3份,每份3.33，最后一份为3.34
weixin_36785129 2017-01-21
- 打赏
- 举报
#4的方式测试了下，日期拆分了，后面的金额算的不太对
复制提示rowS附近有语法错误，rows 没有用过
中国风 2017-01-20
- 打赏
- 举报
用#4方法测过没，你的主要问题是日期的拆分吧，用#4上面方法效率相对较高
Tiger_Zhao 2017-01-20
- 打赏
- 举报
你的要求可以做到，只要把我#1中的a改造一下
用你#5的数据代进去（字段名用原来的）
WITH
/* 测试数据
table1(SID,sDate,fDate,amount) AS (
...
), */
z0(sDate,fDate) AS (
SELECT MIN(sDate) AS sDate,
MAX(fDate) AS fDate
FROM table1
),
z1(sDate,fDate,MIN_sDate,MAX_fDate) AS (
SELECT CASE WHEN DAY(sDate) <= 24
THEN DATEADD(month,-1, DATEADD(day,25-DAY(sDate),sDate) )
ELSE DATEADD(day,25-DAY(sDate),sDate)
END,
CASE WHEN DAY(sDate) <= 24
THEN DATEADD(day,24-DAY(sDate),sDate)
ELSE DATEADD(month,1, DATEADD(day,24-DAY(sDate),sDate) )
END,
sDate,
fDate
FROM z0
),
a(aID,sDate,fDate) AS ( -- 日期区间
SELECT n.number + 1,
CASE WHEN n.number = 0
THEN z1.MIN_sDate
ELSE DATEADD(month,n.number,z1.sDate)
END,
CASE WHEN z1.MAX_fDate <= DATEADD(month,n.number,z1.fDate)
THEN z1.MAX_fDate
ELSE DATEADD(month,n.number,z1.fDate)
END
FROM z1
JOIN (SELECT number
FROM master..spt_values
WHERE type = 'p'
) n
ON DATEADD(month,n.number,sDate) < = MAX_fDate
),
...用你#5的数据代进去（字段名用原来的）
...
table1(SID,sDate,fDate,amount) AS (
SELECT '143EA571-3772-42DF-8E8F-6C638D8405B0','2017-06-26','2017-07-21',16000 UNION ALL
SELECT '2095DF0B-B5F0-48EC-A1C0-2A8E5EBAAD22','2017-02-09','2017-03-22',240000 UNION ALL
SELECT '3DC52EEC-9656-4BFE-8B09-CE25C8A402BE','2017-05-11','2017-06-13',192000 UNION ALL
SELECT '6E894637-B5F6-4DBB-BB68-5F1AF51C8913','2017-04-20','2017-05-10',120000 UNION ALL
SELECT '6F69296E-3DD5-44E8-8FF3-23455BB9BBA0','2017-07-24','2017-08-04',80000 UNION ALL
SELECT 'A2919D17-1876-4761-A5FA-CA3CC95E6744','2017-02-23','2017-04-19',320000 UNION ALL
SELECT 'A74985B1-3D8C-486B-9427-A0F0966D89B9','2017-01-12','2017-01-31',112000 UNION ALL
SELECT 'BC6BFFF7-58E3-47AA-B4BD-BF521DF9717C','2017-06-14','2017-07-07',56000 UNION ALL
SELECT 'DFB2C4AA-EE19-4BD6-9FB6-0AA34303B683','2017-01-12','2017-02-08',14000
),
... aID sDate fDate amount LJamount Com_rate
----------- ---------- ---------- --------------------- --------------------- ---------------------
1 2017-01-12 2017-01-24 79300.00 79300.00 0.0689
2 2017-01-25 2017-02-24 149557.1428 228857.1428 0.199
3 2017-02-25 2017-03-24 308571.4286 537428.5714 0.4673
4 2017-03-25 2017-04-24 177142.8572 714571.4286 0.6213
5 2017-04-25 2017-05-24 170487.3949 885058.8235 0.7696
6 2017-05-25 2017-06-24 138607.8432 1023666.6667 0.8901
7 2017-06-25 2017-07-24 53000.00 1076666.6667 0.9362
8 2017-07-25 2017-08-04 73333.3333 1150000.00 1.00Tiger_Zhao 2017-01-20
- 打赏
- 举报
我是说你的设计不合理。
比如你这9条数据是从查询范围 2017-01-01 ～ 2017-08-31 来的，那么应该直接从查询范围内划分统计区段：第一段 2017-01-01 ～ 2017-01-24，最后一段 2017-08-25 ～ 2017-08-31。
注：#1代码 a 中的几个日期值，我原本的设想不是由业务数据决定的，而是由查询范围 2016-01-01 ～ 2016-05-30 决定的。
按你的做法，头尾两段区间和业务日期相关，中间段的区间却和业务日期无关，这在表现上属于不一致。
更极端点，比如只有这样2条业务数据
2017-01-01 ～ 2017-01-15
2017-01-03 ～ 2017-03-15
那么中间区段 2017-01-25 ～ 2017-02-24 没有任何业务，结果怎么表现？
比如你这9条数据是从查询范围 2017-01-01 ～ 2017-08-31 来的，那么应该直接从查询范围内划分统计区段：第一段 2017-01-01 ～ 2017-01-24，最后一段 2017-08-25 ～ 2017-08-31。
注：#1代码 a 中的几个日期值，我原本的设想不是由业务数据决定的，而是由查询范围 2016-01-01 ～ 2016-05-30 决定的。
按你的做法，头尾两段区间和业务日期相关，中间段的区间却和业务日期无关，这在表现上属于不一致。
更极端点，比如只有这样2条业务数据
2017-01-01 ～ 2017-01-15
2017-01-03 ～ 2017-03-15
那么中间区段 2017-01-25 ～ 2017-02-24 没有任何业务，结果怎么表现？
weixin_36785129 2017-01-19
- 打赏
- 举报
大神，可能之前我描述需求不是很准确，现在我重新理了下，希望大神再帮忙看看，拜谢！！！
SELECT OBJ_SID,ps,pf,Remain_cost FROM dbo.pro_obj WHERE obj_type='TASK'
/*
obj_sid ps pf Remain_cost
143EA571-3772-42DF-8E8F-6C638D8405B0 2017-06-26 2017-07-21 16000
2095DF0B-B5F0-48EC-A1C0-2A8E5EBAAD22 2017-02-09 2017-03-22 240000
3DC52EEC-9656-4BFE-8B09-CE25C8A402BE 2017-05-11 2017-06-13 192000
6E894637-B5F6-4DBB-BB68-5F1AF51C8913 2017-04-20 2017-05-10 120000
6F69296E-3DD5-44E8-8FF3-23455BB9BBA0 2017-07-24 2017-08-04 80000
A2919D17-1876-4761-A5FA-CA3CC95E6744 2017-02-23 2017-04-19 320000
A74985B1-3D8C-486B-9427-A0F0966D89B9 2017-01-12 2017-01-31 112000
BC6BFFF7-58E3-47AA-B4BD-BF521DF9717C 2017-06-14 2017-07-07 56000
DFB2C4AA-EE19-4BD6-9FB6-0AA34303B683 2017-01-12 2017-02-08 14000
*/
/*
统计周期的开始时间为ps中的最小值，完成时间为pf中的最大值
（也就是说在计算统计周期的时候将PS里面的最小值取出来，将pf中的最大值取出来，在这个时间段内进行周期分解,记录不确定就这9条，可能更多）
每个统计周期获取时间区间内（最小ps，最大pf）本月25日到下月24日，
如果开始时间大于等于25日，那统计周期的第一条的开始应该为原有的开始时间，截止日期取到下个月24日，第二条开始时间为下个月25日，截止时间为下下个月24日，以此类推
例如：开始时间为2016-1-28，那统计周期第一条，第二条为
sDate fDate
2016-1-28 2016-2-24
2016-2-25 2016-3-24
如果开始时间小于25日，那统计周期的第一条的开始应该为原有的开始时间，截止日期取到本月24日，第二条开始时间为下个月25日，截止时间为下下个月24日，以此类推
例如：开始时间为2016-1-20，那统计周期第一条，第二条为
sDate fDate
2016-1-20 2016-1-24
2016-1-25 2016-2-24
如果完成时间小于等于24日，那统计周期的最后一条的开始时间为上个月25日，截止时间为完成时间
例如：完成时间为2016-1-20，那统计周期最后一条为
sDate fDate
2015-12-25 2016-1-20
如果完成时间大于24日，那统计周期的最后一条的开始时间为本月25日，截止时间为完成时间
例如：完成时间为2016-1-27，那统计周期最后一条为
sDate fDate
2016-1-25 2016-1-27
其中Remain_cost值根据每条记录在统计周期中所占的天数来算出每条记录在所属周期内的金额值，最后将每条记录在每个统计周期中的值分别加起来，
算出每个统计周期的汇总值，再通过每个统计周期+截止上个统计周期的汇总值除以所有记录Remain_cost的汇总值，再算出每个统计周期的占比
如：
第一条记录在他所属的第一个统计周期内的金额为
(DATEDIFF(day,'2017-06-26','2017-07-24')+1)16000/ (DATEDIFF(day,'2017-06-26','2017-07-21')+1)
最后的数据展示为：
sDate fDate aMount lJaMount Com_rate
2017-01-12 2017-01-24
2017-01-25 2017-02-24
2017-02-25 2017-03-24
2017-03-25 2017-04-24
2017-05-25 2017-06-24
2017-06-25 2017-07-24
2017-07-25 2017-08-04
*/
Tiger_Zhao 2017-01-19
- 打赏
- 举报
[Quote=引用 2 楼 weixin_36785129 的回复:]（统计周期数据应该从所有记录中开始时间最小，完成时间最大这个周期去取）[/Quote]
你的业务设计偏复杂。
比如我们按自然月统计，无论业务是发生在 2016-01-01 还是 2016-01-31 的，都算在1月里，并不需要注明1月是从1日开始还是从31日开始的。
如果有特别需要，用 MIN/MAX 函数单独统计 最早/最晚业务日期啊。
你现在按24日划分统计月，同样原理。
统计区间(sDate,fDate)应该是固定不变的，有需要就增加(MIN_sDate,MAX_fDate)列。
又：回复时请勿全文引用
你的业务设计偏复杂。
比如我们按自然月统计，无论业务是发生在 2016-01-01 还是 2016-01-31 的，都算在1月里，并不需要注明1月是从1日开始还是从31日开始的。
如果有特别需要，用 MIN/MAX 函数单独统计 最早/最晚业务日期啊。
你现在按24日划分统计月，同样原理。
统计区间(sDate,fDate)应该是固定不变的，有需要就增加(MIN_sDate,MAX_fDate)列。
又：回复时请勿全文引用
中国风 2017-01-19
- 打赏
- 举报
这样效果？猜一个
e.g.
use Tempdb
go
--> --> 中国风(Roy)生成測試數據
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([SID] int,[sDate] DATE,[fDate] DATE,[aMount] MONEY)
Insert #T
select 1,'2016-01-01','2016-03-28',2000 union all
select 2,'2016-02-02','2016-03-08',1000 union all
select 3,'2016-02-26','2016-04-18',3000 union all
select 4,'2016-03-05','2016-04-03',500 union all
select 5,'2016-04-10','2016-05-30',4000
UNION ALL SELECT 6,'2016-01-01','2016-01-31',31
GO
;WITH CTET
AS
(
SELECT a.SID ,
CASE WHEN b.number = 0 THEN a.sDate
WHEN DAY(sDate) > 24
THEN CONVERT(VARCHAR(8), DATEADD(m, b.number, a.sDate), 120)
+ '25'
ELSE CONVERT(VARCHAR(8), DATEADD(m, b.number - 1, a.sDate), 120)
+ '25'
END AS sDate ,
CASE WHEN DAY(a.fDate) <= 24
AND DAY(a.sDate) > 24
AND CONVERT(VARCHAR(8), DATEADD(m, b.number + 1, a.sDate), 120)
+ '24' > a.fDate
OR CONVERT(VARCHAR(8), DATEADD(m, b.number, a.sDate), 120)
+ '24' > a.fDate THEN a.fDate
WHEN DAY(a.sDate) > 24
AND CONVERT(VARCHAR(8), DATEADD(m, b.number, a.sDate), 120)
+ '24' <= a.fDate
THEN CONVERT(VARCHAR(8), DATEADD(m, b.number + 1, a.sDate), 120)
+ '24'
ELSE CONVERT(VARCHAR(8), DATEADD(m, b.number, a.sDate), 120)
+ '24'
END AS fDate ,
a.aMount ,
a.fDate AS fDate2 ,
a.aMount / (DATEDIFF(dd, a.sDate, a.fDate) + 1) AS DailyPrice
FROM #T AS a
INNER JOIN master.dbo.spt_values AS b ON b.type = 'P'
AND ( CONVERT(VARCHAR(8), DATEADD(m,
b.number,
a.sDate), 120)
+ '24' <= a.fDate
OR CONVERT(VARCHAR(8), DATEADD(m,
b.number - 1,
a.sDate), 120)
+ '25' < a.fDate
AND CONVERT(VARCHAR(8), DATEADD(m,
b.number,
a.sDate), 120)
+ '25' > a.fDate
AND DAY(sDate) <= 25
)
)
SELECT SID ,
sDate ,
fDate ,
aMount,
DailyPrice ,
CASE WHEN fDate=fDate2 THEN aMount-ISNULL(SUM((DATEDIFF(dd, sDate, fDate) + 1 ) * DailyPrice)OVER(PARTITION BY SID ORDER BY sDate ASC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),0)
ELSE (DATEDIFF(dd, sDate, fDate) + 1 ) * DailyPrice END AS lJaMount
,( DATEDIFF(dd, sDate, fDate) + 1 ) * DailyPrice * 100 / aMount AS Com_rate
FROM CTET
ORDER BY SID,sDate;
/*
SID sDate fDate aMount DailyPrice lJaMount Com_rate
1 2016-01-01 2016-01-24 2000.00 22.7272 545.4528 27.2726
1 2016-01-25 2016-02-24 2000.00 22.7272 704.5432 35.2271
1 2016-02-25 2016-03-24 2000.00 22.7272 659.0888 32.9544
1 2016-03-25 2016-03-28 2000.00 22.7272 90.9152 4.5454
2 2016-02-02 2016-02-24 1000.00 27.7777 638.8871 63.8887
2 2016-02-25 2016-03-08 1000.00 27.7777 361.1129 36.111
3 2016-02-26 2016-03-24 3000.00 56.6037 1584.9036 52.8301
3 2016-03-25 2016-04-18 3000.00 56.6037 1415.0964 47.1697
4 2016-03-05 2016-03-24 500.00 16.6666 333.332 66.6664
4 2016-03-25 2016-04-03 500.00 16.6666 166.668 33.3332
5 2016-04-10 2016-04-24 4000.00 78.4313 1176.4695 29.4117
5 2016-04-25 2016-05-24 4000.00 78.4313 2352.939 58.8234
5 2016-05-25 2016-05-30 4000.00 78.4313 470.5915 11.7646
6 2016-01-01 2016-01-24 31.00 1.00 24.00 77.4193
6 2016-01-25 2016-01-31 31.00 1.00 7.00 22.5806
*/weixin_36785129 2017-01-18
- 打赏
- 举报
大神有点疑问
1、获取的时间周期
sql获取时间区间内本月25日和下月24日，起止时间是动态的
（统计周期数据应该从所有记录中开始时间最小，完成时间最大这个周期去取）
统计周期中取数
应该是
如果开始时间大于25日，那统计周期的第一条的开始应该为原有的开始时间，截止日期取到下个月24日，第二条开始时间为下个月25日，截止时间为下下个月24日，以此类推
如果开始时间小于等于25日，那统计周期的第一条的开始应该为原有的开始时间，截止日期取到本月24日，第二条开始时间为下个月25日，截止时间为下下个月24日，以此类推
如果完成时间小于等于24日，那统计周期的最后一条的开始时间为上个月25日，截止时间为完成时间
如果完成时间大于24日，那统计周期的最后一条的开始时间为本月25日，截止时间为完成时间
Tiger_Zhao 2017-01-18
- 打赏
- 举报
WITH
/* 测试数据
table1(SID,sDate,fDate,amount) AS (
SELECT 1,'2016-01-01','2016-03-28',2000 UNION ALL
SELECT 2,'2016-02-02','2016-03-08',1000 UNION ALL
SELECT 3,'2016-02-26','2016-04-18',3000 UNION ALL
SELECT 4,'2016-03-05','2016-04-03',500 UNION ALL
SELECT 5,'2016-04-10','2016-05-30',4000
), */
a(aID,sDate,fDate) AS ( -- 日期区间
SELECT number + 1,
CASE WHEN number = 0
THEN '2016-01-01'
ELSE DATEADD(month,number,'2015-12-25')
END,
CASE WHEN number = 5
THEN '2016-05-30'
ELSE DATEADD(month,number,'2016-01-24')
END
FROM master..spt_values
WHERE type = 'p'
AND number < 6
),
b AS ( -- 切割日期区间
SELECT a.aID,
CASE WHEN a.sDate > t.sDate THEN a.SDate ELSE t.sDate END AS sDate,
CASE WHEN a.fDate < t.fDate THEN a.fDate ELSE t.fDate END AS fDate,
DATEDIFF(day,t.sDate,t.fDate) + 1 AS totalDays,
t.amount AS totalAmount
/* DEBUG
,a.sDate AS a_sDate,
a.fDate AS a_fDate,
t.SID,
t.sDate AS t_sDate,
t.fDate As t_fDate
*/
FROM a
JOIN table1 t
ON a.sDate <= t.fDate
AND t.sDate <= a.fDate
),
c(aID,amount) AS ( -- 分隔 amount
SELECT aID,
CONVERT(money, totalAmount * 1.0 * (DATEDIFF(day,sDate,fDate)+1) / totalDays)
FROM b
),
d(amount) AS ( -- 合计 amount
SELECT sum(amount)
FROM c
),
e(aID,amount) AS ( -- 分段统计 amount
SELECT aID,
SUM(amount)
FROM c
GROUP BY aID
),
f(aID,amount,LJamount) AS ( -- 分段统计 LJamount
SELECT e1.aID,
MAX(e1.amount),
SUM(e2.amount)
FROM e e1
JOIN e e2
ON e2.aID <= e1.aID
GROUP BY e1.aID
)
SELECT a.*,
f.amount,
f.LJamount,
ISNULL(F.LJamount,0) / d.amount AS Com_rate
FROM a
JOIN d
ON 1=1
LEFT JOIN f
ON a.aid = f.aid aID sDate fDate amount LJamount Com_rate
----------- ---------- ---------- --------------------- --------------------- ---------------------
1 2016-01-01 2016-01-24 545.4545 545.4545 0.0519
2 2016-01-25 2016-02-24 1343.4344 1888.8889 0.1798
3 2016-02-25 2016-03-24 2938.441 4827.3299 0.4597
4 2016-03-25 2016-04-24 2849.1407 7676.4706 0.731
5 2016-04-25 2016-05-24 2352.9412 10029.4118 0.9551
6 2016-05-25 2016-05-30 470.5882 10500.00 1.00
DEBUG: 中间数据 b
aID sDate fDate totalDays totalAmount a_sDate a_fDate SID t_sDate t_fDate
----------- ---------- ---------- ----------- ----------- ---------- ---------- ----------- ---------- ----------
1 2016-01-01 2016-01-24 88 2000 2016-01-01 2016-01-24 1 2016-01-01 2016-03-28
2 2016-01-25 2016-02-24 88 2000 2016-01-25 2016-02-24 1 2016-01-01 2016-03-28
3 2016-02-25 2016-03-24 88 2000 2016-02-25 2016-03-24 1 2016-01-01 2016-03-28
4 2016-03-25 2016-03-28 88 2000 2016-03-25 2016-04-24 1 2016-01-01 2016-03-28
2 2016-02-02 2016-02-24 36 1000 2016-01-25 2016-02-24 2 2016-02-02 2016-03-08
3 2016-02-25 2016-03-08 36 1000 2016-02-25 2016-03-24 2 2016-02-02 2016-03-08
3 2016-02-26 2016-03-24 53 3000 2016-02-25 2016-03-24 3 2016-02-26 2016-04-18
4 2016-03-25 2016-04-18 53 3000 2016-03-25 2016-04-24 3 2016-02-26 2016-04-18
3 2016-03-05 2016-03-24 30 500 2016-02-25 2016-03-24 4 2016-03-05 2016-04-03
4 2016-03-25 2016-04-03 30 500 2016-03-25 2016-04-24 4 2016-03-05 2016-04-03
4 2016-04-10 2016-04-24 51 4000 2016-03-25 2016-04-24 5 2016-04-10 2016-05-30
5 2016-04-25 2016-05-24 51 4000 2016-04-25 2016-05-24 5 2016-04-10 2016-05-30
6 2016-05-25 2016-05-30 51 4000 2016-05-25 2016-06-24 5 2016-04-10 2016-05-30
