declare @table table([id] int ,[name] varchar(10),price numeric(12,2),beginDate datetime,endDate datetime)
declare @tableDetail table([id] int,[fid] int,price numeric(12,2),beginDate datetime,endDate datetime)
insert into @table(id,name,price,beginDate,endDate)
select 1,'张三',0.00,'2012-07-01','2012-08-31'

insert into @tableDetail(id,fid,price,beginDate,endDate)
select 1,1,10.00,'2012-07-05','2012-07-10' union all
select 2,1,10.00,'2012-07-08','2012-07-20' union all
select 3,1,10.00,'2012-07-25','2012-07-31' union all
select 4,1,10.00,'2012-08-01','2012-08-20' union all
select 5,1,10.00,'2012-08-08','2012-08-15' 

想查到下面的这样的数据，从7月1号到8月31号每一天都有价格就算是0也有数据出来。

/*
name        price      beginDate       endDate
---------- ----------- -------------   ----------
张三         0.00      2012-07-01      2012-07-04 
张三        10.00      2012-07-05      2012-07-07 
张三        20.00      2012-07-08      2012-07-20 
张三         0.00      2012-07-21      2012-07-24 
张三        10.00      2012-07-25      2012-07-31 
张三        10.00      2012-08-01      2012-08-07 
张三        20.00      2012-08-08      2012-08-15 
张三        10.00      2012-08-16      2012-08-20
张三         0.00      2012-08-21      2012-08-31

*/

declare @table table([id] int ,[name] varchar(10),price numeric(12,2),beginDate datetime,endDate datetime)
declare @tableDetail table([id] int,[fid] int,price numeric(12,2),beginDate datetime,endDate datetime)
insert into @table(id,name,price,beginDate,endDate)
select 1,'张三',0.00,'2012-07-01','2012-08-31' UNION
select 2,'李四',0.00,'2012-07-01','2012-09-30' 

insert into @tableDetail(id,fid,price,beginDate,endDate)
select 1,1,10.00,'2012-07-01','2012-07-10' union all
select 2,1,10.00,'2012-07-02','2012-07-20' union all
select 3,1,10.00,'2012-07-02','2012-07-20' union all
select 4,1,10.00,'2012-07-20','2012-07-31' union all
select 5,1,10.00,'2012-08-01','2012-08-20' UNION all
select 6,2,10.00,'2012-07-05','2012-07-10' union all
select 7,2,10.00,'2012-07-08','2012-07-20' union all
select 8,2,10.00,'2012-07-25','2012-07-31' union all
select 9,2,10.00,'2012-08-01','2012-08-20' union all
select 10,2,10.00,'2012-08-08','2012-08-15'
;
WITH t0 AS
(
    SELECT fid,SUM(price) AS price,beginDate,endDate
    FROM @tableDetail
    GROUP BY fid,beginDate,endDate
),
t1 AS
(
SELECT DISTINCT A.name,A.BeginDate AS AllDate
FROM @table AS A INNER JOIN t0 AS B ON A.id = B.fid
UNION ALL
SELECT DISTINCT A.name,B.BeginDate
FROM @table AS A INNER JOIN t0 AS B ON A.id = B.fid
UNION ALL
SELECT A.name,B.EndDate
FROM @table AS A INNER JOIN t0 AS B ON A.id = B.fid
UNION ALL
(SELECT A.name,A.endDate
FROM @table AS A INNER JOIN t0 AS B ON A.id = B.fid
UNION 
SELECT A.name,DATEADD(DAY,-1,B.BeginDate)
FROM @table AS A INNER JOIN t0 AS B ON A.id = B.fid AND NOT EXISTS (SELECT 1 FROM @table AS C INNER JOIN t0 AS D ON C.id = D.fid AND DATEADD(DAY,-1,B.BeginDate) = D.endDate) AND DATEADD(DAY,-1,B.BeginDate) > A.beginDate)
UNION ALL
SELECT A.name,DATEADD(DAY,1,B.EndDate)
FROM @table AS A INNER JOIN t0 AS B ON A.id = B.fid AND NOT EXISTS (SELECT 1 FROM @table AS C INNER JOIN t0 AS D ON C.id = D.fid AND DATEADD(DAY,1,B.endDate) = D.beginDate) AND DATEADD(DAY,1,B.endDate) < A.endDate),
t2 AS
(
    SELECT name,AllDate,RN=ROW_NUMBER() OVER (PARTITION BY name ORDER BY AllDate)
    FROM t1
),
t3 AS
(
SELECT A.name,A.AllDate AS BeginDate,B.AllDate AS EndDate
FROM t2 AS A,t2 AS B
WHERE A.RN = B.RN - 1 AND A.RN % 2 = 1 AND B.RN % 2 = 0 AND A.name = B.name)

SELECT  t3.Name,
        ISNULL(SUM(A.price),0) AS Price,
        CONVERT(VARCHAR(10),t3.beginDate,20) AS beginDate,
        CONVERT(VARCHAR(10),t3.endDate,20) AS endDate
FROM    t0 AS A INNER JOIN @table AS B ON A.fid = B.id
        RIGHT OUTER JOIN t3 ON A.beginDa