如何动态的汇总日期字段当天以前的数量汇总

[songlinshu]

fnumber fdate finqty foutqty
1.2.00239 NULL 0.0000000000 0.0000000000
1.2.00239 2015-05-01 00:00:00.000 0.0000000000 42.0000000000
1.2.00239 2015-05-04 00:00:00.000 400.0000000000 0.0000000000
1.2.00239 2015-05-07 00:00:00.000 0.0000000000 21.0000000000
1.2.00239 2015-05-11 00:00:00.000 0.0000000000 1.8000000000
1.2.00239 2015-05-12 00:00:00.000 0.0000000000 0.9000000000
1.2.00239 2015-05-13 00:00:00.000 0.0000000000 21.0000000000
1.2.00239 2015-05-15 00:00:00.000 0.0000000000 21.0000000000
1.2.00239 2015-05-20 00:00:00.000 0.0000000000 31.5000000000
1.2.00239 2015-05-26 00:00:00.000 0.0000000000 22.8000000000
1.2.00239 2015-05-28 00:00:00.000 0.0000000000 3.0000000000
1.2.00239 2015-05-28 00:00:00.000 0.0000000000 0.1300000000
1.2.00239 2015-06-01 00:00:00.000 0.0000000000 21.0000000000
1.2.00239 2015-06-01 00:00:00.000 400.0000000000 0.0000000000
1.2.00239 2015-06-01 00:00:00.000 -400.0000000000 0.0000000000
1.2.00239 2015-06-02 00:00:00.000 0.0000000000 21.0000000000
1.2.00239 2015-06-08 00:00:00.000 0.0000000000 21.0000000000
1.2.00239 2015-06-12 00:00:00.000 0.0000000000 21.0000000000
1.2.00239 2015-06-15 00:00:00.000 0.0000000000 21.0000000000
1.2.00239 2015-06-18 00:00:00.000 0.0000000000 21.0000000000
1.2.00239 2015-06-24 00:00:00.000 0.0000000000 24.0000000000
1.2.00239 2015-06-26 00:00:00.000 0.0000000000 21.0000000000
1.2.00239 2015-06-30 00:00:00.000 0.3500000000 0.0000000000
1.2.00239B NULL 0.0000000000 0.000000000
如何动态根据fdate值 ，小于fdate 值汇总finqty,foutqty?

[中国风]

SELECT a.fnumber,a.fdate
,(SELECT SUM(finqty) FROM TabName WHERE fnumber=a.fnumber AND ISNULL(fdate,'1900-01-01')<=ISNULL(a.fdate,'1900-01-01'))  AS finqty
,(SELECT SUM(foutqty) FROM TabName WHERE fnumber=a.fnumber AND ISNULL(fdate,'1900-01-01')<=ISNULL(a.fdate,'1900-01-01'))  AS foutqty
FROM TabName AS a

[spiritofdragon]

其实你描述的一点都不清楚，但我试了下，看看是不是你要的效果，如果不是，那么请你看看发帖须知重新描述下你的需求。 with t(fnumber,fdate,finqty,foutqty) as ( select '1.2.00239',NULL,'0.0000000000','0.0000000000' union all select '1.2.00239','2015-05-01 00:00:00.000','0.0000000000','42.0000000000' union all select '1.2.00239','2015-05-04 00:00:00.000','400.0000000000','0.0000000000' union all select '1.2.00239','2015-05-07 00:00:00.000','0.0000000000','21.0000000000' union all select '1.2.00239','2015-05-11 00:00:00.000','0.0000000000','1.8000000000' union all select '1.2.00239','2015-05-12 00:00:00.000','0.0000000000','0.9000000000' union all select '1.2.00239','2015-05-13 00:00:00.000','0.0000000000','21.0000000000' union all select '1.2.00239','2015-05-15 00:00:00.000','0.0000000000','21.0000000000' union all select '1.2.00239','2015-05-20 00:00:00.000','0.0000000000','31.5000000000' union all select '1.2.00239','2015-05-26 00:00:00.000','0.0000000000','22.8000000000' union all select '1.2.00239','2015-05-28 00:00:00.000','0.0000000000','3.0000000000' union all select '1.2.00239','2015-05-28 00:00:00.000','0.0000000000','0.1300000000' union all select '1.2.00239','2015-06-01 00:00:00.000','0.0000000000','21.0000000000' union all select '1.2.00239','2015-06-01 00:00:00.000','400.0000000000','0.0000000000' union all select '1.2.00239','2015-06-01 00:00:00.000','-400.0000000000','0.0000000000' union all select '1.2.00239','2015-06-02 00:00:00.000','0.0000000000','21.0000000000' union all select '1.2.00239','2015-06-08 00:00:00.000','0.0000000000','21.0000000000' union all select '1.2.00239','2015-06-12 00:00:00.000','0.0000000000','21.0000000000' union all select '1.2.00239','2015-06-15 00:00:00.000','0.0000000000','21.0000000000' union all select '1.2.00239','2015-06-18 00:00:00.000','0.0000000000','21.0000000000' union all select '1.2.00239','2015-06-24 00:00:00.000','0.0000000000','24.0000000000' union all select '1.2.00239','2015-06-26 00:00:00.000','0.0000000000','21.0000000000' union all select '1.2.00239','2015-06-30 00:00:00.000','0.3500000000','0.0000000000' union all select '1.2.00239B',NULL,'0.0000000000','0.000000000' ) ,tt as ( select t.fdate,SUM(CAST(finqty as money))finqty,SUM(CAST(foutqty as money))foutqty from t group by t.fdate ) select t1.fdate,isnull(t2.finqty,0)finqty,isnull(t2.foutqty,0)foutqty from tt t1 outer apply(select SUM(finqty)finqty,SUM(foutqty)foutqty from tt t2 where t2.fdate<t1.fdate) t2 order by t1.fdate

结果

fdate	finqty	foutqty
NULL	0.00	0.00
2015-05-01 00:00:00.000	0.00	0.00
2015-05-04 00:00:00.000	0.00	42.00
2015-05-07 00:00:00.000	400.00	42.00
2015-05-11 00:00:00.000	400.00	63.00
2015-05-12 00:00:00.000	400.00	64.80
2015-05-13 00:00:00.000	400.00	65.70
2015-05-15 00:00:00.000	400.00	86.70
2015-05-20 00:00:00.000	400.00	107.70
2015-05-26 00:00:00.000	400.00	139.20
2015-05-28 00:00:00.000	400.00	162.00
2015-06-01 00:00:00.000	400.00	165.13
2015-06-02 00:00:00.000	400.00	186.13
2015-06-08 00:00:00.000	400.00	207.13
2015-06-12 00:00:00.000	400.00	228.13
2015-06-15 00:00:00.000	400.00	249.13
2015-06-18 00:00:00.000	400.00	270.13
2015-06-24 00:00:00.000	400.00	291.13
2015-06-26 00:00:00.000	400.00	315.13
2015-06-30 00:00:00.000	400.00	336.13

[中国风]

SELECT a.*
,(SELECT SUM(finqty) FROM TabName WHERE fnumber=a.fnumber AND ISNULL(fdate,'1900-01-01')<ISNULL(a.fdate,'1900-01-01'))  AS finqty
,(SELECT SUM(foutqty) FROM TabName WHERE fnumber=a.fnumber AND ISNULL(fdate,'1900-01-01')<ISNULL(a.fdate,'1900-01-01'))  AS foutqty
FROM TabName AS a

[songlinshu]

例：1.2.00239 NULL 0.0000000000 0.0000000000 1.2.00239 2015-05-01 00:00:00.000 0.0000000000 42.0000000000 1.2.00239 2015-05-04 00:00:00.000 400.0000000000 0.0000000000 1.2.00239 2015-05-07 00:00:00.000 0.0000000000 21.0000000000 1.2.00239 2015-05-11 00:00:00.000 0.0000000000 1.8000000000 1.2.00239 2015-05-12 00:00:00.000 0.0000000000 0.9000000000 1.2.00239 2015-05-13 00:00:00.000 0.0000000000 21.0000000000 1.2.00239 2015-05-15 00:00:00.000 0.0000000000 21.0000000000 1.2.00239 2015-05-20 00:00:00.000 0.0000000000 31.5000000000 当日期=2015-05-15 时汇总包含2015-05-15以前的finqty,foutqty, 当日期=2015-05-20 时汇总包含2015-05-20以前的finqty,foutqty,