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

songlinshu 2015-12-07 11:18:08
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?
...全文
91 1 收藏 4
写回复
4 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
songlinshu 2015-12-07
例: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,
回复
中国风 2015-12-07
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
回复
spiritofdragon 2015-12-07
其实你描述的一点都不清楚,但我试了下,看看是不是你要的效果,如果不是,那么请你看看发帖须知重新描述下你的需求。 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
回复
中国风 2015-12-07
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
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2015-12-07 11:18
社区公告
暂无公告