34,590
社区成员
发帖
与我相关
我的任务
分享简单sql，跨年金额怎样分配到每年金额，高分求解！！！
T1
房间号 单价 起期 止期 金额
01 0.70 2017-03-14 2018-03-13 997
02 1.20 2017-04-11 2018-04-10 1438
03 1.00 2017-05-11 2017-12-31 600
T2
房间号 单价 起期 止期 金额 年度1 金额1 年度2 金额2
01 0.70 2017-03-14 2018-03-13 997 2017 800 2018 197
02 1.20 2017-04-11 2018-04-10 1438 2017 1044 2018 394
03 1.00 2017-05-11 2017-12-31 600 2017 600 2018 0
计算起止期间金额分别在每年的金额，年度2的金额=金额 - 年度1的金额
平均每天单价 = 金额 / 起止期间的天数
年度1的金额 = 平均每天单价 * 在年度1的天数
求怎样写sql得到T2
房间号 单价 起期 止期 金额
01 0.70 2017-03-14 2018-03-13 997
02 1.20 2017-04-11 2018-04-10 1438
03 1.00 2017-05-11 2017-12-31 600
T2
房间号 单价 起期 止期 金额 年度1 金额1 年度2 金额2
01 0.70 2017-03-14 2018-03-13 997 2017 800 2018 197
02 1.20 2017-04-11 2018-04-10 1438 2017 1044 2018 394
03 1.00 2017-05-11 2017-12-31 600 2017 600 2018 0
计算起止期间金额分别在每年的金额，年度2的金额=金额 - 年度1的金额
平均每天单价 = 金额 / 起止期间的天数
年度1的金额 = 平均每天单价 * 在年度1的天数
求怎样写sql得到T2
...全文
请发表友善的回复…
发表回复
lantianhf888 2017-08-15
- 打赏
- 举报
ch21st ，如果T#，T#3T表的结构变下，后面的天数1，金额1等怎么都不对了，怎么改呢
if object_id(N'Tempdb..#t') is not null drop table #T
if object_id(N'Tempdb..#t3') is not null drop table #T3
go
CREATE TABLE #t(房间号 VARCHAR(10),期间 VARCHAR(10),标准名称 VARCHAR(20),单价 DECIMAL(10, 2),起期 DATETIME,止期 DATETIME,金额 DECIMAL(10, 2))
CREATE TABLE #t3(标准名称 VARCHAR(20),收费项目 nvarchar(20))
GO
INSERT INTO #t
SELECT '01' ,'1季度','标间(标准)',0.70 ,'2017-01-01' ,'2017-03-31' ,997 UNION ALL
SELECT '01' ,'2季度','标间(标准)',1.20 ,'2017-04-01' ,'2017-06-30' ,1438 UNION ALL
SELECT '02' ,'1季度','家庭间(标准)',1.20 ,'2017-04-11' ,'2018-04-10' ,1438 UNION ALL
SELECT '03' ,'1季度','家庭间(标准)',1.00 ,'2017-05-11' , '2017-12-31' , 600
go
INSERT INTO #t3
SELECT '标间(标准)',N'标间' UNION ALL
SELECT '标间(标准)',N'标间' UNION ALL
SELECT '家庭间(标准)',N'家庭间'
declare @cols nvarchar(4000),@sql nvarchar(4000)
select @cols=isnull(@cols+N',',N'')+ltrim(st+sv.number) +N'as 年度'+ltrim(sv.number+1) +N',sum(case when year(dateadd(d,hsv.number*200+sv.number,起期))='+ltrim(st+sv.number)+N' then 1 else 0 end) as 天数'+ltrim(sv.number+1)
+N',sum(case when year(dateadd(d,hsv.number*200+sv.number,起期))='+ltrim(st+sv.number)+N' then 金额/(datediff(d,起期,止期)+1) else 0 end) as 金额'+ltrim(sv.number+1)
from (
select min(year(起期)) as st,max(year(止期)) as ed from #t
) as t
inner join master.dbo.spt_values as sv on sv.number between 0 and ed-st
where sv.type='P'
set @sql=N'select t.房间号,t.期间,t.标准名称,t3.收费项目,t.单价,t.起期,t.止期,t.金额,round(金额/(datediff(d,起期,止期)+1),14) as 计算单价,datediff(d,起期,止期)+1 as 总天数,'+@cols+N' from #t as t inner join #t3 as t3 on t.标准名称=t3.标准名称 inner join master.dbo.spt_values as hsv on hsv.type=''P'' inner join master.dbo.spt_values as sv on sv.type=''P''
and (hsv.number*200+sv.number) between 0 and datediff(d,起期,止期) where hsv.number<=200 and sv.number<200 group by t.房间号,t.期间,t.标准名称,t3.收费项目,t.单价,t.起期,t.止期,t.金额,round(金额/(datediff(d,起期,止期)+1),14) order by t.房间号'
select @sql
exec(@sql)
道素 2017-08-14
- 打赏
- 举报
抱歉最近比较忙，没来论坛，不知道问题解决没有，我的语句加个关联表简单调整下就行
if object_id(N'Tempdb..#t') is not null drop table #T
if object_id(N'Tempdb..#t3') is not null drop table #T3
go
CREATE TABLE #t(房间号 VARCHAR(10),单价 DECIMAL(10, 2),起期 DATETIME,止期 DATETIME,金额 DECIMAL(10, 2))
CREATE TABLE #t3(房间号 VARCHAR(10),类型 nvarchar(10))
GO
INSERT INTO #t
SELECT '01' ,0.70 ,'2017-03-14' ,'2018-03-13' ,997 UNION ALL
SELECT '02' ,1.20 ,'2017-04-11' ,'2018-04-10' ,1438 UNION ALL
SELECT '03' ,1.00 ,'2017-05-11' , '2017-12-31' , 600
go
INSERT INTO #t3
SELECT '01',N'大床' UNION ALL
SELECT '02',N'双床' UNION ALL
SELECT '03',N'双床'
declare @cols nvarchar(4000),@sql nvarchar(4000)
select @cols=isnull(@cols+N',',N'')+ltrim(st+sv.number) +N'as 年度'+ltrim(sv.number+1) +N',sum(case when year(dateadd(d,hsv.number*200+sv.number,起期))='+ltrim(st+sv.number)+N' then 1 else 0 end) as 天数'+ltrim(sv.number+1)
+N',sum(case when year(dateadd(d,hsv.number*200+sv.number,起期))='+ltrim(st+sv.number)+N' then 金额/(datediff(d,起期,止期)+1) else 0 end) as 金额'+ltrim(sv.number+1)
from (
select min(year(起期)) as st,max(year(止期)) as ed from #t
) as t
inner join master.dbo.spt_values as sv on sv.number between 0 and ed-st
where sv.type='P'
set @sql=N'select t.房间号,t3.类型,t.单价,t.起期,t.止期,t.金额,round(金额/(datediff(d,起期,止期)+1),14) as 计算单价,datediff(d,起期,止期)+1 as 总天数,'+@cols+N' from #t as t inner join #t3 as t3 on t.房间号=t3.房间号 inner join master.dbo.spt_values as hsv on hsv.type=''P'' inner join master.dbo.spt_values as sv on sv.type=''P''
and (hsv.number*200+sv.number) between 0 and datediff(d,起期,止期) where hsv.number<=200 and sv.number<200 group by t.房间号,t3.类型,t.单价,t.起期,t.止期,t.金额,round(金额/(datediff(d,起期,止期)+1),14) order by 房间号'
select @sql
exec(@sql)
+-----+----+-----+-------------------------+-------------------------+------+-----------------+-----+------+-----+-------------------+------+-----+-----------------+
| 房间号 | 类型 | 单价 | 起期 | 止期 | 金额 | 计算单价 | 总天数 | 年度1 | 天数1 | 金额1 | 年度2 | 天数2 | 金额2 |
+-----+----+-----+-------------------------+-------------------------+------+-----------------+-----+------+-----+-------------------+------+-----+-----------------+
| 01 | 大床 | 0.7 | 2017-03-14 00:00:00:000 | 2018-03-13 00:00:00:000 | 997 | 2.731506849315 | 365 | 2017 | 293 | 800.331506849295 | 2018 | 72 | 196.66849315068 |
| 02 | 双床 | 1.2 | 2017-04-11 00:00:00:000 | 2018-04-10 00:00:00:000 | 1438 | 3.9397260273972 | 365 | 2017 | 265 | 1044.027397260258 | 2018 | 100 | 393.97260273972 |
| 03 | 双床 | 1 | 2017-05-11 00:00:00:000 | 2017-12-31 00:00:00:000 | 600 | 2.5531914893617 | 235 | 2017 | 235 | 599.9999999999995 | 2018 | 0 | 0 |
+-----+----+-----+-------------------------+-------------------------+------+-----------------+-----+------+-----+-------------------+------+-----+-----------------+
zhouyuehai1978 2017-08-11
- 打赏
- 举报
楼主这是在问一个技术问题还是在做一个系统啊
lantianhf888 2017-08-10
- 打赏
- 举报
没有解决，请看22楼，按17楼结果修改！谢谢各位
xb8315381 2017-08-10
- 打赏
- 举报
with cte(房间号,单价,起期,止期,金额) as (
select '01',0.70 ,'2017-03-14' ,'2018-03-13', 997 union
select '02', 1.20 ,'2017-04-11' ,'2018-04-10', 1438 union
select '03', 1.00 ,'2017-05-11' ,'2017-12-31', 600
)
select *,YEAR([起期]) as [年度1],cast([金额]/cast(DATEDIFF(dd,[起期],[止期]) as DECIMAL(10, 6)) as DECIMAL(10, 6))*DATEDIFF(DD,[起期],'2017-12-31') as [金额1],
YEAR([止期]) as [年度2], [金额]-cast([金额]/cast(DATEDIFF(dd,[起期],[止期]) as DECIMAL(10, 6)) as DECIMAL(10, 6))*DATEDIFF(DD,[起期],'2017-12-31') as [金额2]
from cte
lantianhf888 2017-08-09
- 打赏
- 举报
问题没解决，请看上一楼！
lantianhf888 2017-08-08
- 打赏
- 举报
17#楼的ch21st在吗，根据17楼的结果修改，关联T3表的类型字段进来，并在T1表中加入字段 收费日期，将17楼的结果写成存储过程，要求有四个参数，一个参数是年度，一个类型，另外两个是收费开始日期，收费结束日期（根据给出时间段），来查询收费日期在这个时间段范围内的数据
顺势而为1 2017-08-08
- 打赏
- 举报
楼主, 帮你加上类型了
if not object_id(N'Tempdb..#Tmp_Data') is null
drop table #Tmp_Data
Go
CREATE TABLE #Tmp_Data
(
Room_No VARCHAR(10) ,
Price DECIMAL(13, 2) ,
Start_Date Datetime ,
End_Date Datetime ,
Amount decimal(13,2)
)
GO
INSERT INTO #Tmp_Data
SELECT '01' ,0.70 ,'2017-03-14' ,'2018-03-13' ,997 UNION ALL
SELECT '02' ,1.20 ,'2017-04-11' ,'2018-04-10' ,1438 UNION ALL
SELECT '03' ,1.00 ,'2017-05-11' , '2017-12-31' , 600
GO
if not object_id(N'Tempdb..#T3') is null
drop table #T3
Go
CREATE TABLE #T3 (Room_No VARCHAR(10) ,Bed_Type nvarchar(10) )
INSERT INTO #T3
SELECT '01' ,N'大床' UNION ALL
SELECT '02' ,N'双床' UNION ALL
SELECT '03' ,N'双床'
GO
ALTER TABLE #Tmp_Data Add Bed_Type varchar(10)
GO
UPDATE #Tmp_Data
SET Bed_Type=b.Bed_Type
From #Tmp_Data a Join #T3 b on a.Room_No=b.Room_No
--计算起止期间金额分别在每年的金额，年度2的金额=金额 - 年度1的金额
--平均每天单价 = 金额 / 起止期间的天数
--年度1的金额 = 平均每天单价 * 在年度1的天数
--求怎样写sql得到T2
If not object_id(N'Tempdb..#Tmp_Expand') is null
drop table #Tmp_Expand
Go
Select Room_No,Bed_Type,Price,Start_Date,End_Date,Amount,TotDays,AveragePrice,Calc_Year,DATEDIFF(d, Start_Date, End_Date)+1 as YearDays
INTO #Tmp_Expand
From (
Select Room_No,Bed_Type,Price,
case when year(start_date)=calc_year then start_date else cast(calc_year as varchar)+'-01-01' end as Start_Date,
case when year(End_Date)=calc_year then End_Date else cast(calc_year as varchar)+'-12-31' end as End_Date
,Amount,TotDays,Amount/TotDays as AveragePrice,calc_year
From (
Select a.Room_No,Bed_Type,Price,Start_Date,End_Date,Amount,
DATEDIFF(d, Start_Date, End_Date)+1 as TotDays,
Year(DATEADD(YEAR,x.number,Start_Date)) as calc_year
From #Tmp_Data a
join master.dbo.spt_values x on x.number<=DATEDIFF(Year,Start_Date,End_Date)
Where x.type='P'
) a
) a
DECLARE @sql nvarchar(2000)
SET @sql=''
DECLARE @colName01 nvarchar(50)
DECLARE @colName02 nvarchar(50)
DECLARE @YearAmount decimal
DECLARE @minYear int
DECLARE @maxYear int
DECLARE @i int
SET @i=1
Select @minYear=min(Calc_Year),@maxYear=max(Calc_Year) From #Tmp_Expand
While @minYear<=@maxYear
Begin
SET @colName01=N'年度'+ CAST(@i as varchar)
SET @colName02=N'金额'+cast(@i as varchar)
SET @sql='Alter Table #Tmp_Data Add ' + @colName01+' int, '+@colName02+' Decimal(18,6) '
Exec (@sql)
SET @sql='update #Tmp_Data '
+' set '+@colName01+' ='''+cast(@minYear as varchar)+''','+ @colName02+' = YearDays*AveragePrice '
+' from #Tmp_Data a '
+' join #Tmp_Expand b on a.Room_No=b.Room_No '
+' Where Calc_Year=' + cast(@minYear as varchar)
Exec (@sql)
SET @i=@i+1
SET @minYear=@minYear+1
End
Select * From #Tmp_Data
lantianhf888 2017-08-07
- 打赏
- 举报
T1表不是汇总来的，是原始表，要想先用T1与18楼的T3关联下，再得出ch21st 17楼的结果，主要是在T1基础上再关联上类型这列进来
道素 2017-08-07
- 打赏
- 举报
更正后：
if object_id(N'Tempdb..#t') is not null drop table #T
go
CREATE TABLE #t(房间号 VARCHAR(10),单价 DECIMAL(10, 2),起期 DATETIME,止期 DATETIME,金额 DECIMAL(10, 2))
GO
INSERT INTO #t
SELECT '01' ,0.70 ,'2017-03-14' ,'2018-03-13' ,997 UNION ALL
SELECT '02' ,1.20 ,'2017-04-11' ,'2018-04-10' ,1438 UNION ALL
SELECT '03' ,1.00 ,'2017-05-11' , '2017-12-31' , 600
declare @cols nvarchar(4000),@sql nvarchar(4000)
select @cols=isnull(@cols+N',',N'')+ltrim(st+sv.number) +N'as 年度'+ltrim(sv.number+1) +N',sum(case when year(dateadd(d,hsv.number*200+sv.number,起期))='+ltrim(st+sv.number)+N' then 1 else 0 end) as 天数'+ltrim(sv.number+1)
+N',sum(case when year(dateadd(d,hsv.number*200+sv.number,起期))='+ltrim(st+sv.number)+N' then 金额/(datediff(d,起期,止期)+1) else 0 end) as 金额'+ltrim(sv.number+1)
from (
select min(year(起期)) as st,max(year(止期)) as ed from #t
) as t
inner join master.dbo.spt_values as sv on sv.number between 0 and ed-st
where sv.type='P'
set @sql=N'select t.房间号,t.单价,t.起期,t.止期,t.金额,round(金额/(datediff(d,起期,止期)+1),14) as 计算单价,datediff(d,起期,止期)+1 as 总天数,'+@cols+N' from #t as t inner join master.dbo.spt_values as hsv on hsv.type=''P'' inner join master.dbo.spt_values as sv on sv.type=''P''
and (hsv.number*200+sv.number) between 0 and datediff(d,起期,止期) where hsv.number<=200 and sv.number<200 group by t.房间号,t.单价,t.起期,t.止期,t.金额,round(金额/(datediff(d,起期,止期)+1),14) order by 房间号'
select @sql
exec(@sql)
+-----+-----+-------------------------+-------------------------+------+-----------------+-----+------+-----+-------------------+------+-----+-----------------+
| 房间号 | 单价 | 起期 | 止期 | 金额 | 计算单价 | 总天数 | 年度1 | 天数1 | 金额1 | 年度2 | 天数2 | 金额2 |
+-----+-----+-------------------------+-------------------------+------+-----------------+-----+------+-----+-------------------+------+-----+-----------------+
| 01 | 0.7 | 2017-03-14 00:00:00:000 | 2018-03-13 00:00:00:000 | 997 | 2.731506849315 | 365 | 2017 | 293 | 800.331506849295 | 2018 | 72 | 196.66849315068 |
| 02 | 1.2 | 2017-04-11 00:00:00:000 | 2018-04-10 00:00:00:000 | 1438 | 3.9397260273972 | 365 | 2017 | 265 | 1044.027397260258 | 2018 | 100 | 393.97260273972 |
| 03 | 1 | 2017-05-11 00:00:00:000 | 2017-12-31 00:00:00:000 | 600 | 2.5531914893617 | 235 | 2017 | 235 | 599.9999999999995 | 2018 | 0 | 0 |
+-----+-----+-------------------------+-------------------------+------+-----------------+-----+------+-----+-------------------+------+-----+-----------------+
道素 2017-08-07
- 打赏
- 举报
上面总天数有误需要再加一天，比如两个日期差一天，实际是两天
道素 2017-08-07
- 打赏
- 举报
这样吗？只要限定位数不管是多少，即使100也会涉及到四舍五入
declare @cols nvarchar(4000),@sql nvarchar(4000)
select @cols=isnull(@cols+N',',N'')+ltrim(st+sv.number) +N'as 年度'+ltrim(sv.number+1) +N',sum(case when year(dateadd(d,hsv.number*200+sv.number,起期))='+ltrim(st+sv.number)+N' then 1 else 0 end) as 天数'+ltrim(sv.number+1)
+N',sum(case when year(dateadd(d,hsv.number*200+sv.number,起期))='+ltrim(st+sv.number)+N' then 金额/datediff(d,起期,止期) else 0 end) as 金额'+ltrim(sv.number+1)
from (
select min(year(起期)) as st,max(year(止期)) as ed from #t
) as t
inner join master.dbo.spt_values as sv on sv.number between 0 and ed-st
where sv.type='P'
set @sql=N'select t.房间号,t.单价,t.起期,t.止期,t.金额,round(金额/datediff(d,起期,止期),14) as 计算单价,datediff(d,起期,止期) as 总天数,'+@cols+N' from #t as t inner join master.dbo.spt_values as hsv on hsv.type=''P'' inner join master.dbo.spt_values as sv on sv.type=''P''
and (hsv.number*200+sv.number) between 0 and datediff(d,起期,止期) where hsv.number<=200 and sv.number<200 group by t.房间号,t.单价,t.起期,t.止期,t.金额,round(金额/datediff(d,起期,止期),14) order by 房间号'
select @sql
exec(@sql)
+-----+-----+-------------------------+-------------------------+------+-----------------+-----+------+-----+-------------------+------+-----+-------------------+
| 房间号 | 单价 | 起期 | 止期 | 金额 | 计算单价 | 总天数 | 年度1 | 天数1 | 金额1 | 年度2 | 天数2 | 金额2 |
+-----+-----+-------------------------+-------------------------+------+-----------------+-----+------+-----+-------------------+------+-----+-------------------+
| 01 | 0.7 | 2017-03-14 00:00:00:000 | 2018-03-13 00:00:00:000 | 997 | 2.7390109890109 | 364 | 2017 | 293 | 802.5302197801937 | 2018 | 72 | 197.2087912087848 |
| 02 | 1.2 | 2017-04-11 00:00:00:000 | 2018-04-10 00:00:00:000 | 1438 | 3.9505494505494 | 364 | 2017 | 265 | 1046.895604395591 | 2018 | 100 | 395.05494505494 |
| 03 | 1 | 2017-05-11 00:00:00:000 | 2017-12-31 00:00:00:000 | 600 | 2.5641025641025 | 234 | 2017 | 235 | 602.5641025640875 | 2018 | 0 | 0 |
+-----+-----+-------------------------+-------------------------+------+-----------------+-----+------+-----+-------------------+------+-----+-------------------+
lantianhf888 2017-08-07
- 打赏
- 举报
回复楼上ch21st，能否把总天数，计算单价（不要四舍五入，要保留14位），每年的天数加上，每年金额四合五入保留两位
mywisdom88 2017-08-07
- 打赏
- 举报
T1
房间号 单价 起期 止期 金额
01 0.70 2017-03-14 2018-03-13 997
02 1.20 2017-04-11 2018-04-10 1438
03 1.00 2017-05-11 2017-12-31 600
T2
房间号 单价 起期 止期 金额 年度1 金额1 年度2 金额2
01 0.70 2017-03-14 2018-03-13 997 2017 800 2018 197
02 1.20 2017-04-11 2018-04-10 1438 2017 1044 2018 394
03 1.00 2017-05-11 2017-12-31 600 2017 600 2018 0
请问,楼主,你T1表是不是从其他表汇总过来的?你现在是不是想要T2格式的汇总表?
其实,你从T1的原始表数据那里,直接汇总,不要从T1这个结果汇总,更加快
lantianhf888 2017-08-07
- 打赏
- 举报
回复楼上ch21st，上面结果正确，如果再加T3表，T1.房间号=T3.房间号，想在查询的结果中将T3的类型也显示，怎样修改sql
T1
房间号 单价 起期 止期 金额
01 0.70 2017-03-14 2018-03-13 997
02 1.20 2017-04-11 2018-04-10 1438
03 1.00 2017-05-11 2017-12-31 600
T3
房间号 类型
01 大床
02 双床
03 双床
道素 2017-08-07
- 打赏
- 举报
结果有些误差，你可以自己调整
if object_id(N'Tempdb..#t') is not null drop table #T
go
CREATE TABLE #t(房间号 VARCHAR(10),单价 DECIMAL(10, 2),起期 DATETIME,止期 DATETIME,金额 DECIMAL(10, 2))
GO
INSERT INTO #t
SELECT '01' ,0.70 ,'2017-03-14' ,'2018-03-13' ,997 UNION ALL
SELECT '02' ,1.20 ,'2017-04-11' ,'2018-04-10' ,1438 UNION ALL
SELECT '03' ,1.00 ,'2017-05-11' , '2017-12-31' , 600
declare @cols nvarchar(4000),@sql nvarchar(4000)
select @cols=isnull(@cols+N',',N'')+ltrim(st+sv.number) +N'as 年度'+ltrim(sv.number+1) +N',sum(case when year(dateadd(d,hsv.number*200+sv.number,起期))='+ltrim(st+sv.number)+N' then 金额/datediff(d,起期,止期) else 0 end) as 金额'+ltrim(sv.number+1)
from (
select min(year(起期)) as st,max(year(止期)) as ed from #t
) as t
inner join master.dbo.spt_values as sv on sv.number between 0 and ed-st
where sv.type='P'
set @sql=N'select t.房间号,t.单价,t.起期,t.止期,t.金额,'+@cols+N' from #t as t inner join master.dbo.spt_values as hsv on hsv.type=''P'' inner join master.dbo.spt_values as sv on sv.type=''P''
and (hsv.number*200+sv.number) between 0 and datediff(d,起期,止期) where hsv.number<=200 and sv.number<200 group by t.房间号,t.单价,t.起期,t.止期,t.金额 order by 房间号'
select @sql
exec(@sql)
01 .70 2017-03-14 00:00:00.000 2018-03-13 00:00:00.000 997.00 2017 802.5302197801937 2018 197.2087912087848
02 1.20 2017-04-11 00:00:00.000 2018-04-10 00:00:00.000 1438.00 2017 1046.8956043955910 2018 395.0549450549400
03 1.00 2017-05-11 00:00:00.000 2017-12-31 00:00:00.000 600.00 2017 602.5641025640875 2018 .0000000000000
lantianhf888 2017-08-06
- 打赏
- 举报
楼主更正下，求2000的sql
顺势而为1 2017-08-06
- 打赏
- 举报
需要用动态SQL 行转列实现
if not object_id(N'Tempdb..#Tmp_Data') is null
drop table #Tmp_Data
Go
CREATE TABLE #Tmp_Data
(
Room_No VARCHAR(10) ,
Price DECIMAL(13, 2) ,
Start_Date Datetime ,
End_Date Datetime ,
Amount decimal(13,2)
)
GO
INSERT INTO #Tmp_Data
SELECT '01' ,0.70 ,'2017-03-14' ,'2019-03-13' ,1997 UNION ALL
SELECT '02' ,1.20 ,'2016-04-11' ,'2018-04-10' ,1438 UNION ALL
SELECT '03' ,1.00 ,'2017-05-11' , '2017-12-31' , 600
GO
--计算起止期间金额分别在每年的金额，年度2的金额=金额 - 年度1的金额
--平均每天单价 = 金额 / 起止期间的天数
--年度1的金额 = 平均每天单价 * 在年度1的天数
--求怎样写sql得到T2
If not object_id(N'Tempdb..#Tmp_Expand') is null
drop table #Tmp_Expand
Go
Select Room_No,Price,Start_Date,End_Date,Amount,TotDays,AveragePrice,Calc_Year,DATEDIFF(d, Start_Date, End_Date) as YearDays
INTO #Tmp_Expand
From (
Select Room_No,Price,
case when year(start_date)=calc_year then start_date else cast(calc_year as varchar)+'-01-01' end as Start_Date,
case when year(End_Date)=calc_year then End_Date else cast(calc_year as varchar)+'-12-31' end as End_Date
,Amount,TotDays,Amount/TotDays as AveragePrice,calc_year
From (
Select Room_No,Price,Start_Date,End_Date,Amount,
DATEDIFF(d, Start_Date, End_Date) as TotDays,
Year(DATEADD(YEAR,b.number,Start_Date)) as calc_year
From #Tmp_Data a
join master.dbo.spt_values b on b.number<=DATEDIFF(Year,Start_Date,End_Date)
Where b.type='P'
) a
) a
DECLARE @sql nvarchar(2000)
SET @sql=''
DECLARE @colName01 nvarchar(50)
DECLARE @colName02 nvarchar(50)
DECLARE @YearAmount decimal(13,2)
DECLARE @minYear int
DECLARE @maxYear int
DECLARE @i int
SET @i=1
Select @minYear=min(Calc_Year),@maxYear=max(Calc_Year) From #Tmp_Expand
While @minYear<=@maxYear
Begin
SET @colName01='Year'+ CAST(@i as varchar)
SET @colName02='Amount'+cast(@i as varchar)
SET @sql='Alter Table #Tmp_Data Add ' + @colName01+' int, '+@colName02+' Decimal(13,2) '
Exec (@sql)
SET @sql='update #Tmp_Data '
+' set '+@colName01+' ='''+cast(@minYear as varchar)+''','+ @colName02+' = YearDays*AveragePrice '
+' from #Tmp_Data a '
+' join #Tmp_Expand b on a.Room_No=b.Room_No '
+' Where Calc_Year=' + cast(@minYear as varchar)
Exec (@sql)
SET @i=@i+1
SET @minYear=@minYear+1
End
Select * From #Tmp_Data
不穿内裤的超人 2017-08-06
- 打赏
- 举报
全部字段给出版：
;with cte(房间号,单价,起期,止期,金额) as (
select '01',0.70 ,'2017-03-14' ,'2018-03-13', 997 union
select '02', 1.20 ,'2017-04-11' ,'2018-04-10', 1438 union
select '03', 1.00 ,'2017-05-11' ,'2017-12-31', 600
)
select *,'总天数'=DATEDIFF(DD,起期,止期),'计算单价'=cast(金额/cast(DATEDIFF(DD,起期,止期) as decimal(10,4)) as decimal(10,4)),
'年度1占用天数'=DATEDIFF(dd,起期,cast(convert(char(4),起期,112)+1 as CHAR(4))+'-01-01 00:00:00'),
'年度1'=convert(char(4),起期,112),
'金额1'=DATEDIFF(dd,起期,cast(convert(char(4),起期,112)+1 as CHAR(4))+'-01-01 00:00:00')*cast(金额/cast(DATEDIFF(DD,起期,止期) as decimal(10,4)) as decimal(10,4)),
'年度2占用天数'=case when convert(char(4),起期,112)=convert(char(4),止期,112) then 0 else
DATEDIFF(dd,cast(convert(char(4),起期,112)+1 as CHAR(4))+'-01-01 00:00:00',止期) end,
'年度2'=case when convert(char(4),起期,112)=convert(char(4),止期,112) then convert(char(4),起期,112)+1 else convert(char(4),止期,112) end
,'金额2'=case when convert(char(4),起期,112)=convert(char(4),止期,112) then 0 else
DATEDIFF(dd,cast(convert(char(4),起期,112)+1 as CHAR(4))+'-01-01 00:00:00',止期)*cast(金额/cast(DATEDIFF(DD,起期,止期) as decimal(10,2)) as decimal(10,2)) end
,'截断时间'=cast(convert(char(4),起期,112)+1 as CHAR(4))+'-01-01 00:00:00'
from cte 房间号 单价 起期 止期 金额 总天数 计算单价 年度1占用天数 年度1 金额1 年度2占用天数 年度2 金额2 截断时间
01 0.70 2017-03-14 2018-03-13 997 364 2.7390 293 2017 802.5270 71 2018 194.54 2018-01-01 00:00:00
02 1.20 2017-04-11 2018-04-10 1438 364 3.9505 265 2017 1046.8825 99 2018 391.05 2018-01-01 00:00:00
03 1.00 2017-05-11 2017-12-31 600 234 2.5641 235 2017 602.5635 0 2018 0.00 2018-01-01 00:00:00
OwenZeng_DBA 2017-08-06
- 打赏
- 举报
800和197这个数字是怎么算出来的
加载更多回复（8）
