一个时间段内的统计汇总，每个月的汇率不一样？如何统计

Question

我有一张表，比如说有三个字段，
--箱子表
create table a(箱子 varchar(20), 日租金 float, 币种 varchar(20))
insert into a 
select '2000', 5, 'USD'
--汇率表
create table sysrate(ryear int, rmonth int, rate float)
insert into sysrate
select 2010, 1, 8
union all 
select 2010, 2, 10
union all 
select 2010, 3, 11我要如何统计2010年1到3月份，箱子'2000'的月租金，得到如下结果
箱子     月份   月租金
2010      1      31 * 5 * 8 
2010      2      28 * 5 * 10
2010      3      31 * 5 * 11用存储过程或sql啥实现，头大中！！！！！

Answer 1

select 箱子,rmonth,日租金*rate*(32-Day(convert(datetime,rtrim(ryear)+'-'+rtrim(rmonth)+'-1')+ (32-Day(convert(datetime,rtrim(ryear)+'-'+rtrim(rmonth)+'-1')))))
from a,sysrate

Answer 2

--> 测试数据: ta
if object_id('ta') is not null drop table ta
go 
create table ta (id varchar(1),name sql_variant)
insert into ta
select 'a',null union all
select 'b',null union all
select 'c',null--> 测试数据: a
if object_id('a') is not null drop table a
go 
--箱子表
create table a(箱子 varchar(20), 日租金 float, 币种 varchar(20))
insert into a  
select '2000', 5, 'USD'if object_id('sysrate') is not null drop table sysrate
go 
--汇率表
create table sysrate(ryear int, rmonth int, rate float)
insert into sysrate
select 2010, 1, 8
union all  
select 2010, 2, 10
union all  
select 2010, 3, 11
select 
ryear,
rmonth,
月租金=日租金*rate*day(dateadd(day,-1,ltrim(ryear)+'-'+ltrim(rmonth+1)+'-01')),
月租金1=ltrim(日租金)+'*'+ltrim(rate)+'*'+ltrim(day(dateadd(day,-1,ltrim(ryear)+'-'+ltrim(rmonth+1)+'-01')))  --这样看着更直观
from a
cross join
sysrate b
ryear       rmonth      月租金                    月租金1
----------- ----------- ---------------------- ------------------------------------------------------------
2010        1           1240                   5*8*31
2010        2           1400                   5*10*28
2010        3           1705                   5*11*31(3 行受影响)

Answer 3

select a.箱子,ryear,rmonth,
'月租金'=case when rmonth in (1,3,5,7,8,10,12) then 31*日租金*rate
         when rmonth=2 then 28*日租金*rate 
        else 30*日租金*rate end from a,sysrate

Answer 4

IF exists (select * from dbo.sysobjects where id=OBJECT_ID(N'fn_getDaysofGivenMonth') and OBJECTPROPERTY(id, N'IsScalarFunction')=1)
drop function fn_getDaysofGivenMonth
gocreate function fn_getDaysofGivenMonth(@date datetime)
returns int
as
begin
declare @begin_month datetime, @end_month datetime, @days int

select @begin_month  = dateadd(month, datediff(month, 0, @date), 0)
select @end_month = dateadd(dd,-datepart(dd,@date),dateadd(mm,1,@date))
select @days = datediff(dd,@begin_month,@end_month)+1
return @days
end
goselect a.箱子,s.rmonth as 月份, dbo.fn_getDaysofGivenMonth(cast(s.ryear+'-'+s.rmonth as datetime))*a.日租金*s.rate as 月租金
from sysrate s , a

Answer 5

Create FUNCTION dbo.f_GetMonthDays
 (
 @year int,
 @month int
 )
RETURNS int
AS
 BEGIN
  Declare @Ret_V int
  Declare @tmpDate datetime  SET @tmpDate = Convert(datetime, Cast(@year as varchar(4)) + '-' + Cast(@month as varchar(2)) + '-01')  SET @Ret_V = DateDiff(dd,@tmpDate,DateAdd(mm,1,@tmpDate))  RETURN @Ret_V
 END
 
 select dbo.f_GetMonthDays ('2010','03')
select ryear,rmonth,月租金=dbo.f_GetMonthDays (ryear,rmonth)*a.日租金*rate 
from sysrate,a

Answer 6

--箱子表
if OBJECT_ID('a') is not null
drop table a
create table a(箱子 varchar(20), 日租金 float, 币种 varchar(20))
insert into a  
select '2000', 5, 'USD'
--汇率表
create table sysrate(ryear int, rmonth int, rate float)
insert into sysrate
select 2010, 1, 8
union all  
select 2010, 2, 10
union all  
select 2010, 3, 11select 箱子,ryear 年份, rmonth 月份,
      月租金 = ltrim(case when rmonth=3 then  datepart(day,dateadd(MONTH,1,cast(ltrim(ryear)+ '-'+ltrim(1)+ '-01' as datetime)-1)) 
                     else datepart(day,dateadd(MONTH,1,cast(ltrim(ryear)+ '-'+ltrim(rmonth)+ '-01' as datetime)-1)) end)
               +' * '+ltrim(a.日租金)+' * '+LTRIM(rate)                                            
from a cross join sysrate 箱子 年份 月份 月租金
2000 2010 1 31 * 5 * 8
2000 2010 2 28 * 5 * 10
2000 2010 3 31 * 5 * 11

Answer 7

--> 测试数据: ta
if object_id('ta') is not null drop table ta
go 
create table ta (id varchar(1),name sql_variant)
insert into ta
select 'a',null union all
select 'b',null union all
select 'c',null--> 测试数据: a
if object_id('a') is not null drop table a
go 
--箱子表
create table a(箱子 varchar(20), 日租金 float, 币种 varchar(20))
insert into a  
select '2000', 5, 'USD'if object_id('sysrate') is not null drop table sysrate
go 
--汇率表
create table sysrate(ryear int, rmonth int, rate float)
insert into sysrate
select 2010, 1, 8
union all  
select 2010, 2, 10
union all  
select 2010, 3, 11
declare @t1 datetime,@t2 datetime
set @t1='2010-1-5'
set @t2='2010-3-19'
select 
ryear,
rmonth,
月租金=日租金*rate*
case when month(@t1)=month(rmonth) then day(dateadd(day,-1,ltrim(ryear)+'-'+ltrim(rmonth+1)+'-01'))-day(@t1)+1
 when month(@t2)=month(rmonth) then day(@t2)
 else day(dateadd(day,-1,ltrim(ryear)+'-'+ltrim(rmonth+1)+'-01'))
end 
/*
,月租金1=ltrim(日租金)+'*'+ltrim(rate)+'*'+  --这样看着更直观
ltrim(case when month(@t1)=rmonth then day(dateadd(day,-1,ltrim(ryear)+'-'+ltrim(rmonth+1)+'-01'))-day(@t1)+1
 when month(@t2)=rmonth then day(@t2)
 else day(dateadd(day,-1,ltrim(ryear)+'-'+ltrim(rmonth+1)+'-01'))
end) 
*/
from a
cross join
sysrate b 
ryear       rmonth      月租金                    月租金1
----------- ----------- ---------------------- ------------------------------------------------------------
2010        1           1080                   5*8*27
2010        2           1200                   5*10*28
2010        3           1485                   5*11*19(3 行受影响)