在线求一个SQL语句~~~~ 有关累计的
我有一个表， 要全部生成结果， 如果能用一个SQL语句搞定最好，
其中：
从vip第一次消费记录的每个月都要生成， 比如A 2013年1月份开始消费， 2月份没消费， 2月份也要出来，
B从2月份开始消费， 那记录就从2月份开始
这是原始表语句：
create table #xs(vip varchar(20), date datetime, sale float)
insert into #xs(vip, date, sale)
select 'A' as vip,'2013-1-1' as date,230 as sale union all
select 'A','2013-1-5',400 union all
select 'A','2013-3-6',500 union all
select 'A','2013-4-30',600 union all
select 'B','2013-2-3',200 union all
select 'B','2013-3-5',300 union all
select 'B','2013-4-24',400
select * from #xs
if object_id('tempdb..#xs') is not null
drop table #xs;
create table #xs(vip varchar(20), date datetime, sale float)
insert into #xs(vip, date, sale)
select 'A' as vip,'2013-1-1' as date,230 as sale union all
select 'A','2013-1-5',400 union all
select 'A','2013-3-6',500 union all
select 'A','2013-4-30',600 union all
select 'B','2013-2-3',200 union all
select 'B','2013-3-5',300 union all
select 'B','2013-4-24',400
;with t as
(
select dateadd(month, number,'20130101') MonthDay
from master.dbo.spt_values
where type='p'
)
,tRange as
(
select vip,convert(varchar(6),min(date),112)+'01' MinMonth,convert(varchar(6),max(date),112)+'01' MaxMonth
from #xs
group by vip
),tDimDate as
(
select tr.vip,t.MonthDay StartDay,dateadd(d,-1,DATEADD(month,1,t.MonthDay)) EndDay
from tRange tr
join t
on tr.MinMonth<=t.MonthDay
and tr.MaxMonth>=t.MonthDay
),tdata as (
select t.vip,EndDay
,DATEDIFF(d,(select top 1 date from #xs xs where xs.date <=t.EndDay and xs.vip=t.vip order by xs.date desc ),EndDay) NoVisitDays
,count(x.date) VisitDays
,sum(isnull(sale,0)) Sales
from tDimDate t
left join #xs x
on x.[date] between t.StartDay and t.EndDay
and t.vip=x.vip
group by t.vip,EndDay
)
select vip,EndDay,NoVisitDays,VisitDays
,sum(VisitDays) over(partition by vip order by EndDay ) sumVisitDays
,sum(Sales) over(partition by vip order by EndDay ) sumSales
from tdata
create table #xs(vip varchar(20), date datetime, sale float)
insert into #xs(vip, date, sale)
select 'A' as vip,'2013-1-1' as date, 230 as sale union all
select 'A','2013-1-5', 400 union all
select 'A','2013-3-6', 500 union all
select 'A','2013-4-30',600 union all
select 'B','2013-2-3',200 union all
select 'B','2013-3-5',300 union all
select 'B','2013-4-24',400
--select * from #xs
;WITH cte AS (
SELECT vip,[date],DATEADD(dd,-1,DATEADD(mm,DATEDIFF(m,0,[date]),0)) bomonth,DATEADD(dd,-1,DATEADD(mm,DATEDIFF(m,0,[date])+1,0)) AS eomonth,sale
FROM #xs
),c2 AS (
SELECT vip,eomonth,COUNT(1) AS monthCount,SUM(sale) AS sale FROM cte GROUP BY vip,[bomonth],eomonth
),c3 AS(
SELECT a.number,b.vip FROM master..spt_values a JOIN (SELECT DISTINCT vip FROM #xs) b ON 1=1 WHERE a.type='p' AND a.number BETWEEN 1 AND 12
),c4 AS (
SELECT a.*,b.number,b.vip AS vipx FROM c2 a RIGHT JOIN c3 b ON a.vip=b.vip AND MONTH(a.eomonth)=b.number
--ORDER BY b.vip,b.number
),c5 AS (
SELECT * FROM c4 a WHERE a.eomonth IS NOT NULL OR
EXISTS(SELECT 1 FROM c4 x WHERE a.vipx=x.vip AND a.number<x.number)
AND EXISTS(SELECT 1 FROM c4 x WHERE a.vipx =x.vip AND a.number>x.number)
),c6 AS (
SELECT vipx,CASE WHEN eomonth IS NOT NULL THEN eomonth ELSE (
SELECT DATEADD(mm,a.number-MAX(number),MAX(eomonth)) FROM c5 x WHERE x.vipx=a.vipx
) END AS eomonth
,CASE WHEN monthCount is NOT NULL THEN monthCount ELSE 0 END AS monthCount
,CASE WHEN sale is NOT NULL THEN sale ELSE 0 END AS sale
FROM c5 a
)
SELECT a.vipx,a.eomonth,a.monthCount
,(SELECT SUM(monthcount) FROM c6 x WHERE x.vipx=a.vipx AND x.eomonth<=a.eomonth) AS totalCount
,(SELECT SUM(sale) FROM c6 x WHERE x.vipx=a.vipx AND x.eomonth<=a.eomonth) AS TotalSale
FROM c6 a ORDER BY a.vipx,a.eomonth
--
/*
vipx eomonth monthCount totalCount TotalSale
-------------------- ----------------------- ----------- ----------- ----------------------
A 2013-01-31 00:00:00.000 2 2 630
A 2013-02-28 00:00:00.000 0 2 630
A 2013-03-31 00:00:00.000 1 3 1130
A 2013-04-30 00:00:00.000 1 4 1730
B 2013-02-28 00:00:00.000 1 1 200
B 2013-03-31 00:00:00.000 1 2 500
B 2013-04-30 00:00:00.000 1 3 900
*/
DROP TABLE #xs;
