如何查询最近3个月的发生金额

bnd248 2009-03-17 02:55:37
因为管理的需要需以3个月为期得到月发生金额


原表:
年 月 科目代码 客户代码 期初余额 本月借方发生 本月贷方发生 期末余额
2005 4 122 01.053.00173 0.00 416820.00 416820.00 0.00
2005 5 122 01.053.00173 0.00 351000.00 0.00 351000.00
2005 6 122 01.053.00173 351000.00 8500.00 351000.00 8500.00
2005 7 122 01.053.00173 8500.00 780000.00 788500.00 0.00
2005 8 122 01.053.00173 0.00 1964810.00 194250.00 1770560.00
2005 9 122 01.053.00173 1770560.00 37000.00 792000.00 1015560.00
2005 10 122 01.053.00173 1015560.00 0.00 1015560.00 0.00
2005 11 122 01.053.00239 0.00 0.00 0.00 0.00
2005 12 122 01.053.00239 0.00 0.00 0.00 0.00
2006 1 122 01.053.00239 0.00 0.00 0.00 0.00
2006 2 122 01.053.00239 0.00 0.00 0.00 0.00
2006 3 122 01.053.00239 0.00 0.00 0.00 0.00
2006 4 122 01.053.00239 0.00 1807110.00 0.00 1807110.00
2006 5 122 01.053.00239 1807110.00 1346400.00 1330560.00 1822950.00
2006 6 122 01.053.00239 1822950.00 0.00 1346400.00 476550.00
2006 7 122 01.053.00239 476550.00 0.00 476550.00 0.00
2006 8 122 01.053.00239 0.00 0.00 0.00 0.00
2006 9 122 01.053.00239 0.00 0.00 0.00 0.00

结果表:
年 月 科目代码 客户代码 3个月前期初余额 近3个月借方发生 近3个月贷方发生 期末余额
2005 4 122 01.053.00173 0.00 416820.00 416820.00 0.00
2005 5 122 01.053.00173 0.00 767820.00 416820.00 351000.00
2005 6 122 01.053.00173 0.00 776320.00 767820.00 8500.00
2005 7 122 01.053.00173 0.00 1139500.00 1139500.00 0.00
2005 8 122 01.053.00173 351000.00 2753310.00 1333750.00 1770560.00
2005 9 122 01.053.00173 8500.00 2781810.00 1774750.00 1015560.00
2005 10 122 01.053.00173 1015560.00 0.00 1015560.00 0.00
2005 11 122 01.053.00239 0.00 0.00 0.00 0.00
2005 12 122 01.053.00239 0.00 0.00 0.00 0.00
2006 1 122 01.053.00239 0.00 0.00 0.00 0.00
2006 2 122 01.053.00239 0.00 0.00 0.00 0.00
2006 3 122 01.053.00239 0.00 0.00 0.00 0.00
2006 4 122 01.053.00239 0.00 1807110.00 0.00 1807110.00
2006 5 122 01.053.00239 0.00 3153510.00 1330560.00 1822950.00
2006 6 122 01.053.00239 0.00 3153510.00 2676960.00 476550.00
2006 7 122 01.053.00239 1807110.00 1346400.00 3153510.00 0.00
2006 8 122 01.053.00239 1822950.00 0.00 1822950.00 0.00
2006 9 122 01.053.00239 476550.00 0.00 476550.00 0.00


...全文
126 8 打赏 收藏 转发到动态 举报
写回复
用AI写文章
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
wfu_liuxh 2009-03-21
  • 打赏
  • 举报
回复
学习了,牛。
claro 2009-03-21
  • 打赏
  • 举报
回复
学习6楼的,实现了,牛。
百年树人 2009-03-21
  • 打赏
  • 举报
回复
---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([年] int,[月] int,[科目代码] int,[客户代码] varchar(12),[期初余额] numeric(9,2),[本月借方发生] numeric(9,2),[本月贷方发生] numeric(9,2),[期末余额] numeric(9,2))
insert [tb]
select 2005,4,122,'01.053.00173',0.00,416820.00,416820.00,0.00 union all
select 2005,5,122,'01.053.00173',0.00,351000.00,0.00,351000.00 union all
select 2005,6,122,'01.053.00173',351000.00,8500.00,351000.00,8500.00 union all
select 2005,7,122,'01.053.00173',8500.00,780000.00,788500.00,0.00 union all
select 2005,8,122,'01.053.00173',0.00,1964810.00,194250.00,1770560.00 union all
select 2005,9,122,'01.053.00173',1770560.00,37000.00,792000.00,1015560.00 union all
select 2005,10,122,'01.053.00173',1015560.00,0.00,1015560.00,0.00 union all
select 2005,11,122,'01.053.00239',0.00,0.00,0.00,0.00 union all
select 2005,12,122,'01.053.00239',0.00,0.00,0.00,0.00 union all
select 2006,1,122,'01.053.00239',0.00,0.00,0.00,0.00 union all
select 2006,2,122,'01.053.00239',0.00,0.00,0.00,0.00 union all
select 2006,3,122,'01.053.00239',0.00,0.00,0.00,0.00 union all
select 2006,4,122,'01.053.00239',0.00,1807110.00,0.00,1807110.00 union all
select 2006,5,122,'01.053.00239',1807110.00,1346400.00,1330560.00,1822950.00 union all
select 2006,6,122,'01.053.00239',1822950.00,0.00,1346400.00,476550.00 union all
select 2006,7,122,'01.053.00239',476550.00,0.00,476550.00,0.00 union all
select 2006,8,122,'01.053.00239',0.00,0.00,0.00,0.00 union all
select 2006,9,122,'01.053.00239',0.00,0.00,0.00,0.00

---查询---
select * into #
from
(
select
px1=(select count(1) from tb where ltrim(年)+right('00'+ltrim(月),2)<ltrim(t.年)+right('00'+ltrim(t.月),2))/3+1,
px2=(select count(1) from tb where ltrim(年)+right('00'+ltrim(月),2)<ltrim(t.年)+right('00'+ltrim(t.月),2))%3+1,
px3=(select count(1) from tb where ltrim(年)+right('00'+ltrim(月),2)<ltrim(t.年)+right('00'+ltrim(t.月),2))+1,
*
from tb t
) tt

select *from #

select
年,月,科目代码,客户代码,
[3个月前期初余额]=isnull((select 期末余额 from # where px2=t.px2 and px1=t.px1-1),0),
[近3个月借方发生]=isnull((select sum(本月借方发生) from # where px1=t.px1 and px3<=t.px3),0),
[近3个月贷方发生]=isnull((select sum(本月贷方发生) from # where px1=t.px1 and px3<=t.px3),0),
期末余额
from # t

drop table #

---结果---
年 月 科目代码 客户代码 3个月前期初余额 近3个月借方发生 近3个月贷方发生 期末余额
----------- ----------- ----------- ------------ ----------- ---------------------------------------- ---------------------------------------- -----------
2005 4 122 01.053.00173 .00 416820.00 416820.00 .00
2005 5 122 01.053.00173 .00 767820.00 416820.00 351000.00
2005 6 122 01.053.00173 .00 776320.00 767820.00 8500.00
2005 7 122 01.053.00173 .00 780000.00 788500.00 .00
2005 8 122 01.053.00173 351000.00 2744810.00 982750.00 1770560.00
2005 9 122 01.053.00173 8500.00 2781810.00 1774750.00 1015560.00
2005 10 122 01.053.00173 .00 .00 1015560.00 .00
2005 11 122 01.053.00239 1770560.00 .00 1015560.00 .00
2005 12 122 01.053.00239 1015560.00 .00 1015560.00 .00
2006 1 122 01.053.00239 .00 .00 .00 .00
2006 2 122 01.053.00239 .00 .00 .00 .00
2006 3 122 01.053.00239 .00 .00 .00 .00
2006 4 122 01.053.00239 .00 1807110.00 .00 1807110.00
2006 5 122 01.053.00239 .00 3153510.00 1330560.00 1822950.00
2006 6 122 01.053.00239 .00 3153510.00 2676960.00 476550.00
2006 7 122 01.053.00239 1807110.00 .00 476550.00 .00
2006 8 122 01.053.00239 1822950.00 .00 476550.00 .00
2006 9 122 01.053.00239 476550.00 .00 476550.00 .00

(所影响的行数为 18 行)
ChinaJiaBing 2009-03-21
  • 打赏
  • 举报
回复

select 年,科目代码,客户代码,SUM(期初余额),SUM(本月借方发生),sum(本月贷方发生),SUM(期末余额)
from 表 group by 年,datepart( quarter,(年+'-' +月 + '-'+ '00')),科目代码,客户代码




-狙击手- 2009-03-21
  • 打赏
  • 举报
回复
select 年,
月,
科目代码,
客户代码,
3个月前期初余额 = (select 期初余额 from ta where datediff(mm,ltrim(年)+'-'+ltrim(月)+'01',ltrim(a.年)+'-'+ltrim(a.月)+'01') = 3 ) ,
近3个月借方发生= ...
近3个月贷方发生= ...
期末余额
from ta a
htl258_Tony 2009-03-21
  • 打赏
  • 举报
回复
这么多,楼主分也给得太少了吧,其它贴子先来了.
claro 2009-03-21
  • 打赏
  • 举报
回复
帮提供数据
Declare @a Table (年 varchar(20), 月 varchar(20),科目代码 varchar(20), 客户代码 varchar(20),
期初余额 float, 本月借方发生 float,本月贷方发生 float,期末余额 float)
Insert @a Select
'2005','4','122','01.053.00173',0.00,416820.00,416820.00,0.00 Union All Select
'2005','5','122','01.053.00173',0.00,351000.00,0.00,351000.00 Union All Select
'2005','6','122','01.053.00173',351000.00,8500.00,351000.00,8500.00 Union All Select
'2005','7','122','01.053.00173',8500.00,780000.00,788500.00,0.00 Union All Select
'2005','8','122','01.053.00173',0.00,1964810.00,194250.00,1770560.00 Union All Select
'2005','9','122','01.053.00173',1770560.00,37000.00,792000.00,1015560.00 Union All Select
'2005','10','122','01.053.00173',1015560.00,0.00,1015560.00,0.00 Union All Select
'2005','11','122','01.053.00239',0.00,0.00,0.00,0.00 Union All Select
'2005','12','122','01.053.00239',0.00,0.00,0.00,0.00 Union All Select
'2006','1','122','01.053.00239',0.00,0.00,0.00,0.00 Union All Select
'2006','2','122','01.053.00239',0.00,0.00,0.00,0.00 Union All Select
'2006','3','122','01.053.00239',0.00,0.00,0.00,0.00 Union All Select
'2006','4','122','01.053.00239',0.00,1807110.00,0.00,1807110.00 Union All Select
'2006','5','122','01.053.00239',1807110.00,1346400.00,1330560.00,1822950.00 Union All Select
'2006','6','122','01.053.00239',1822950.00,0.00,1346400.00,476550.00 Union All Select
'2006','7','122','01.053.00239',476550.00,0.00,476550.00,0.00 Union All Select
'2006','8','122','01.053.00239',0.00,0.00,0.00,0.00 Union All Select
'2006','9','122','01.053.00239',0.00,0.00,0.00,0.00

select * from @a
年 月 科目代码 客户代码 期初余额 本月借方发生 本月贷方发生 期末余额
2005 4 122 01.053.00173 0 416820 416820 0
2005 5 122 01.053.00173 0 351000 0 351000
2005 6 122 01.053.00173 351000 8500 351000 8500
2005 7 122 01.053.00173 8500 780000 788500 0
2005 8 122 01.053.00173 0 1964810 194250 1770560
2005 9 122 01.053.00173 1770560 37000 792000 1015560
2005 10 122 01.053.00173 1015560 0 1015560 0
2005 11 122 01.053.00239 0 0 0 0
2005 12 122 01.053.00239 0 0 0 0
2006 1 122 01.053.00239 0 0 0 0
2006 2 122 01.053.00239 0 0 0 0
2006 3 122 01.053.00239 0 0 0 0
2006 4 122 01.053.00239 0 1807110 0 1807110
2006 5 122 01.053.00239 1807110 1346400 1330560 1822950
2006 6 122 01.053.00239 1822950 0 1346400 476550
2006 7 122 01.053.00239 476550 0 476550 0
2006 8 122 01.053.00239 0 0 0 0
2006 9 122 01.053.00239 0 0 0 0
claro 2009-03-21
  • 打赏
  • 举报
回复
帮顶
通过程序实现吧。

22,210

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧