22,209
社区成员
发帖
与我相关
我的任务
分享求教高手一个按月份拆分金额的SQL写法
现有一个数据库表Table中存放的字段、数据如下所示：
自动ID 开始日期 结束日期 总金额
1 2016-05-10 2018-05-09 5000
2 2013-09-01 2016-08-31 9000
3 2016-06-01 2016-12-31 2000
4 2015-01-01 2015-12-31 4000
5 2016-01-01 2016-01-31 500
.......
注：其中的开始日期、结束日期之间，有单个月、多个月跨年、多个月不跨年的多种情况，但无论任何情况，肯定最小单位是月，不会出现少于1个月的情况。
现在想把这些记录用SQL全部拆分成单个月的，金额则是用“总金额 / 开始日期和结束日期之间月份数量” 来平均计算即可
比如：第1行就需要拆分成
开始日期 结束日期 总金额
2016-05-10 2016-06-09 208.33
2016-06-10 2016-07-09 208.33
2016-07-10 2016-08-09 208.33
......
2018-04-10 2018-05-09 208.33
类似这种效果就可以了，抱歉分不多了，请大神指教。
自动ID 开始日期 结束日期 总金额
1 2016-05-10 2018-05-09 5000
2 2013-09-01 2016-08-31 9000
3 2016-06-01 2016-12-31 2000
4 2015-01-01 2015-12-31 4000
5 2016-01-01 2016-01-31 500
.......
注：其中的开始日期、结束日期之间，有单个月、多个月跨年、多个月不跨年的多种情况，但无论任何情况，肯定最小单位是月，不会出现少于1个月的情况。
现在想把这些记录用SQL全部拆分成单个月的，金额则是用“总金额 / 开始日期和结束日期之间月份数量” 来平均计算即可
比如：第1行就需要拆分成
开始日期 结束日期 总金额
2016-05-10 2016-06-09 208.33
2016-06-10 2016-07-09 208.33
2016-07-10 2016-08-09 208.33
......
2018-04-10 2018-05-09 208.33
类似这种效果就可以了，抱歉分不多了，请大神指教。
...全文
请发表友善的回复…
发表回复
顺势而为1 2017-06-05
- 打赏
- 举报
是不是笔误啊 not null啊
if not object_id(N'Tempdb..#T') is not null[/quote]
if not object_id(N'Tempdb..#T') is not null[/quote]
二月十六 2017-06-05
- 打赏
- 举报
不是，前边写not了
if not object_id(N'Tempdb..#T') is null
bluetata 2017-06-05
- 打赏
- 举报
是不是笔误啊 not null啊
if not object_id(N'Tempdb..#T') is not null
if not object_id(N'Tempdb..#T') is not null二月十六 2017-06-05
- 打赏
- 举报
试试这个：
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([自动ID] int,[开始日期] Date,[结束日期] Date,[总金额] DECIMAL(10,2))
Insert #T
select 1,'2016-05-10','2018-05-09',5000 union all
select 2,'2013-09-01','2016-08-31',9000 union all
select 3,'2016-06-01','2016-12-31',2000 union all
select 4,'2015-01-01','2015-12-31',4000 union all
select 5,'2016-01-01','2016-01-31',500
Go
--测试数据结束
SELECT [开始日期] ,
DATEADD(MONTH, b.number+1, 开始日期) AS [结束日期] ,
[总金额] / CASE WHEN DATEDIFF(MONTH, 开始日期, 结束日期) <> 0
THEN DATEDIFF(MONTH, 开始日期, 结束日期)
ELSE 1
END AS 总金额
FROM #T
INNER JOIN master.dbo.spt_values AS b ON b.type = 'P'
WHERE b.number < ( CASE WHEN DATEDIFF(MONTH, 开始日期, 结束日期) <> 0
THEN DATEDIFF(MONTH, 开始日期, 结束日期)
ELSE 1
END )
Mr_Nice 2017-06-05
- 打赏
- 举报
--> 测试数据: [sTab]
if object_id('[sTab]') is not null drop table [sTab]
create table [sTab] (自动ID int,开始日期 datetime,结束日期 datetime,总金额 int)
insert into [sTab]
select 1,'2016-05-10','2018-05-09',5000 union all
select 2,'2013-09-01','2016-08-31',9000 union all
select 3,'2016-06-01','2016-12-31',2000 union all
select 4,'2015-01-01','2015-12-31',4000 union all
select 5,'2016-01-01','2016-01-31',500
select * from [sTab]
SELECT CONVERT(VARCHAR(10),DATEADD(mm, B.number, dbo.sTab.开始日期),120) AS 开始日期,
CONVERT(VARCHAR(10),DATEADD(dd,-1,DATEADD(mm, B.number + 1, dbo.sTab.开始日期)),120) AS 结束日期,
CONVERT(DECIMAL(10, 2), 总金额 * 1.00
/ CASE WHEN DATEDIFF(mm, 开始日期, 结束日期) = 0 THEN 1
ELSE DATEDIFF(mm, 开始日期, 结束日期)
END) AS perMonthMoney
FROM dbo.sTab
INNER JOIN master..spt_values B ON DATEADD(mm, B.number, dbo.sTab.开始日期) BETWEEN dbo.sTab.开始日期
AND
dbo.sTab.结束日期
WHERE B.type = 'P';
/*
开始日期 结束日期 perMonthMoney
2016-05-10 2016-06-09 208.33
2016-06-10 2016-07-09 208.33
2016-07-10 2016-08-09 208.33
2016-08-10 2016-09-09 208.33
2016-09-10 2016-10-09 208.33
2016-10-10 2016-11-09 208.33
2016-11-10 2016-12-09 208.33
2016-12-10 2017-01-09 208.33
2017-01-10 2017-02-09 208.33
2017-02-10 2017-03-09 208.33
2017-03-10 2017-04-09 208.33
2017-04-10 2017-05-09 208.33
2017-05-10 2017-06-09 208.33
2017-06-10 2017-07-09 208.33
2017-07-10 2017-08-09 208.33
2017-08-10 2017-09-09 208.33
2017-09-10 2017-10-09 208.33
2017-10-10 2017-11-09 208.33
2017-11-10 2017-12-09 208.33
2017-12-10 2018-01-09 208.33
2018-01-10 2018-02-09 208.33
2018-02-10 2018-03-09 208.33
2018-03-10 2018-04-09 208.33
2018-04-10 2018-05-09 208.33
2013-09-01 2013-09-30 257.14
2013-10-01 2013-10-31 257.14
2013-11-01 2013-11-30 257.14
2013-12-01 2013-12-31 257.14
2014-01-01 2014-01-31 257.14
2014-02-01 2014-02-28 257.14
2014-03-01 2014-03-31 257.14
2014-04-01 2014-04-30 257.14
2014-05-01 2014-05-31 257.14
2014-06-01 2014-06-30 257.14
2014-07-01 2014-07-31 257.14
2014-08-01 2014-08-31 257.14
2014-09-01 2014-09-30 257.14
2014-10-01 2014-10-31 257.14
2014-11-01 2014-11-30 257.14
2014-12-01 2014-12-31 257.14
2015-01-01 2015-01-31 257.14
2015-02-01 2015-02-28 257.14
2015-03-01 2015-03-31 257.14
2015-04-01 2015-04-30 257.14
2015-05-01 2015-05-31 257.14
2015-06-01 2015-06-30 257.14
2015-07-01 2015-07-31 257.14
2015-08-01 2015-08-31 257.14
2015-09-01 2015-09-30 257.14
2015-10-01 2015-10-31 257.14
2015-11-01 2015-11-30 257.14
2015-12-01 2015-12-31 257.14
2016-01-01 2016-01-31 257.14
2016-02-01 2016-02-29 257.14
2016-03-01 2016-03-31 257.14
2016-04-01 2016-04-30 257.14
2016-05-01 2016-05-31 257.14
2016-06-01 2016-06-30 257.14
2016-07-01 2016-07-31 257.14
2016-08-01 2016-08-31 257.14
2016-06-01 2016-06-30 333.33
2016-07-01 2016-07-31 333.33
2016-08-01 2016-08-31 333.33
2016-09-01 2016-09-30 333.33
2016-10-01 2016-10-31 333.33
2016-11-01 2016-11-30 333.33
2016-12-01 2016-12-31 333.33
2015-01-01 2015-01-31 363.64
2015-02-01 2015-02-28 363.64
2015-03-01 2015-03-31 363.64
2015-04-01 2015-04-30 363.64
2015-05-01 2015-05-31 363.64
2015-06-01 2015-06-30 363.64
2015-07-01 2015-07-31 363.64
2015-08-01 2015-08-31 363.64
2015-09-01 2015-09-30 363.64
2015-10-01 2015-10-31 363.64
2015-11-01 2015-11-30 363.64
2015-12-01 2015-12-31 363.64
2016-01-01 2016-01-31 500.00*/
