比较扭结的日历统计

比较纠结的日历统计
先表述下，我有3张表:
第一张日历表 calendar 其中 F_DATE 包含每一天日期 INT型
第二张表 instance表里面记录了 instance_id,start_date 开始时间,end_date 结束时间，ini_investment 初始金额四个字段，这个主要是对calendar日期选择。
第三张表 trade_results 交易表其中有 instance_id ，code 产品代码，trade_date int型交易日期（不是每天都交易），trade_type 交易类型 0买 2卖，Amount 交易金额

表结构大致就这样了，数据的话calendar 不用我给了吧，一个日历表。只有一个F_DATE每一天都有。

第二张表 instance_id,start_date ,end_date ，ini_investment
AAA , 20120601 ,20120627 , 1000000.000

第三张表 instance_id ，code ，trade_date ，trade_type ，Amount
AAA , 600048 ,20120607 , 0 ,333329.920
AAA , 600383 ,20120611 , 0 ,333331.050
AAA , 600383 ,20120625 , 2 ,302125.590

要求结果 f_date ，instance_id ，ini_investment，balance（ini_investment - AMOUNT(交易类型0) + AMOUNT(交易类型2)）
20120601， AAA , 1000000.000 ,1000000.000
20120602， AAA , 1000000.000 ,1000000.000
...
20120607， AAA , 1000000.000 ,666670.08
20120608， AAA , 1000000.000 ,666670.08
...
20120611， AAA , 1000000.000 ,333339.03
20120612， AAA , 1000000.000 ,333339.03
...
20120625， AAA , 1000000.000 ,635464.62
20120626， AAA , 1000000.000 ,635464.62
20120627， AAA , 1000000.000 ,635464.62

不知道我表述清楚没有，求爱锅，F哥，各路大神解决下。貌似我提问最多只能给100，咋不能给300分呢，难道级别低了？

------解决方案--------------------

SQL code

select cast(convert(varchar(8),dateadd(dd,num-1,'20120601'),112) as int) as f_date
into ta
from (select top 1000 num=row_number() over(order by getdate()) from sys.objects,sys.columns)t
where dateadd(dd,num-1,'20120601')<='20120630'
go
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([instance_id] varchar(3),[start_date] int,[end_date] int,[ini_investment] numeric(10,3))
insert [tb]
select 'AAA','20120601','20120627',1000000.000
go
if object_id('[tc]') is not null drop table [tc]
go
create table [tc]([instance_id] varchar(3),[code] int,[trade_date] int,[trade_type] int,[Amount] numeric(9,3))
insert [tc]
select 'AAA',600048,'20120607',0,333329.920 union all
select 'AAA',600383,'20120611',0,333331.050 union all
select 'AAA',600383,'20120625',2,302125.590
go

;with cte as(
select a.f_date,b.instance_id,b.ini_investment,c.AMOUNT
 from ta a join tb b on a.f_date between b.start_date and b.end_date
left join tc c on b.instance_id=c.instance_id and a.f_date=c.trade_date
) 
select f_date,instance_id,ini_investment,
  balance=ini_investment-isnull((select sum(AMOUNT) from cte where instance_id=t.instance_id and f_date<=t.f_date),0)
from cte t

/**
f_date      instance_id ini_investment                          balance
----------- ----------- --------------------------------------- ---------------------------------------
20120601    AAA         1000000.000                             1000000.000
20120602    AAA         1000000.000                             1000000.000
20120603    AAA         1000000.000                             1000000.000
20120604    AAA         1000000.000                             1000000.000
20120605    AAA         1000000.000                             1000000.000
20120606    AAA         1000000.000                             1000000.000
20120607    AAA         1000000.000                             666670.080
20120608    AAA         1000000.000                             666670.080
20120609    AAA         1000000.000                             666670.080
20120610    AAA         1000000.000                             666670.080
20120611    AAA         1000000.000                             333339.030
20120612    AAA         1000000.000                             333339.030
20120613    AAA         1000000.000                             333339.030
20120614    AAA         1000000.000                             333339.030
20120615    AAA         1000000.000                             333339.030
20120616    AAA         1000000.000                             333339.030
20120617    AAA         1000000.000                             333339.030
20120618    AAA         1000000.000                             333339.030
20120619    AAA         1000000.000                             333339.030
20120620    AAA         1000000.000                             333339.030
20120621    AAA         1000000.000                             333339.030
20120622    AAA         1000000.000                             333339.030
20120623    AAA         1000000.000                             333339.030
20120624    AAA         1000000.000                             333339.030
20120625    AAA         1000000.000                             31213.440
20120626    AAA         1000000.000                             31213.440
20120627    AAA         1000000.000                             31213.440

(27 行受影响)
**/

相关推荐