select a.部门,a.日期,
金额=(select sum(case a.借贷方式 when '收入' then 金额 else -金额 end)
from 表
where 部门=a.部门 and 日期<=a.日期),
占用天数=datediff(day,(select max(日期)
from 表
where 部门=a.部门 and 日期<a.日期),a.日期)
from 表 a
金额=(select sum(case a.借贷方式 when '收入' then 金额 else -金额 end)
from 表
where 部门=a.部门 and 日期<=a.日期),
占用天数=datediff(day,(select max(日期)
from 表
where 部门=a.部门 and 日期<a.日期),a.日期)
from 表 a
select a.部门,日期=convert(char(10),a.日期,120)
,金额=(
select sum(case 借贷方式 when '借' then 金额 else -金额 end)
from tb
where 部门=a.部门 and 日期<=a.日期)
,占用天数=datediff(day,a.日期,(select min(日期) from tb where 部门=a.部门 and 日期>a.日期))
from tb a left join (
select 部门,日期=max(日期) from tb group by 部门
) b on a.部门=b.部门 and a.日期=b.日期
where b.部门 is null
,金额=(
select sum(case 借贷方式 when '借' then 金额 else -金额 end)
from tb
where 部门=a.部门 and 日期<=a.日期)
,占用天数=datediff(day,a.日期,(select min(日期) from tb where 部门=a.部门 and 日期>a.日期))
from tb a
create table tb(部门 varchar(10),日期 datetime,金额 int,借贷方式 nvarchar(10))
insert tb select 'D01','2004-11-09',20 ,'借'
union all select 'D01','2004-11-15',1000 ,'借'
union all select 'D01','2004-11-18',1900 ,'借'
union all select 'D01','2004-11-19',100 ,'借'
go--查询
select a.部门,日期=convert(char(10),a.日期,120)
,金额=(
select sum(case 借贷方式 when '借' then 金额 else -金额 end)
from tb
where 部门=a.部门 and 日期<=a.日期)
,占用天数=datediff(day,a.日期,(select min(日期) from tb where 部门=a.部门 and 日期>a.日期))
from tb a
--/*--
left join (
select 部门,日期=max(日期) from tb group by 部门
) b on a.部门=b.部门 and a.日期=b.日期
where b.部门 is null
--*/
go--删除测试
drop table tb/*--测试结果部门 日期 金额 占用天数
---------- ---------- ----------- -----------
D01 2004-11-09 20 6
D01 2004-11-15 1020 3
D01 2004-11-18 2920 1(所影响的行数为 3 行)
--*/
select a.部门,a.日期,
金额=(select sum(case a.借贷方式 when '收入' then 金额 else -金额 end)
from 表
where 部门=a.部门 and 日期<=a.日期),
占用天数=isnull(datediff(day,(select max(日期)
from 表
where 部门=a.部门 and 日期<a.日期),a.日期),0)
from 表 a
e.部门,
e.日期,
f.金额,
e.占用天数
from
(select
a.部门
a.日期,
min(datediff(dd,a.日期,b.日期)) as 占用天数,
from
tabname a
inner join
tabname b
on
a.部门 = b.部门 and a.日期 < b.日期
group by
a.部门,a.日期) e
inner join
(select
c.部门,
c.日期,
sum(c.金额) as 金额
from
tname c
inner join
tname d
where
c.部门 = d.部门 and c.日期 < d.日期
group by
c.部门,c.日期) f
on
e.部门 = f.部门 and e.日期 = f.日期
部门 时间 金额 借贷方式
D01 2004-12-28 500.0 借(收入)
D01 2004-12-31 1000.0 借(收入)
D01 2005-01-03 5000.0 贷(付出)
D01 2005-01-03 1800.0 借(收入)
D01 2005-01-12 2000.0 贷(付出)如果查询日期是2004-11-20到2005-01-15
那么结果为部门 日期 金额 占用天数
D01 2004-11-20 0 38
D01 2004-12-28 500 3
D01 2004-12-31 1500 3
D01 2005-01-03 -3500 0
D01 2005-01-03 -1700 9
D01 2005-01-12 -3700 3大家再帮帮忙啦!!
,金额=(
select sum(case 借贷方式 when '借' then 金额 else -金额 end)
from tb
where 部门=a.部门 and 日期<=a.日期)
,占用天数=datediff(day,a.日期,(select min(日期) from tb where 部门=a.部门 and 日期>=a.日期+1))----这里改一下
from tb a left join (
select 部门,日期=max(日期) from tb group by 部门
) b on a.部门=b.部门 and a.日期=b.日期
where b.部门 is null
create Table test22
(
dept varchar(3),
cdate datetime,
qty decimal(8,1) null,
ctype varchar(10)
)insert into test22
select 'D01', '2004-12-28', 500.0, '借(收入)' union all
select 'D01', '2004-12-31', 1000.0, '借(收入)' union all
select 'D01', '2005-01-03', 5000.0, '貨(付出)' union all
select 'D01', '2005-01-03', 1800.0, '借(收入)' union all
select 'D01', '2005-01-12', 2000.0, '貨(付出)'select * from test22
------------------------Create Procedure-------------------------------------------
Create Procedure ct
@date datetime,
@edate datetime
as
create Table #test22
(
dept varchar(3),
cdate datetime,
qty decimal(8,1) null,
ctype varchar(10) null,
i int null,
c int null
)
declare @dept varchar(3),@cdate datetime,@qty decimal(8,1),@ctype varchar(10),@dept1 varchar(3)
Declare @sum decimal(8,1),@c int,@udate datetime,@i int
set @i=0
set @udate=@date
set @c=0
set @sum=0
insert into #test22(dept,cdate,qty,ctype,i,c)
select dept,@date,0,'',0,0 from (select dept,cdate=min(cdate) from test22 group by dept) a where cdate<>@date
Declare kk cursor for
select dept,cdate,qty,ctype from test22 order by cdate
open kk
fetch next from kk into @dept,@cdate,@qty,@ctype
while @@fetch_status=0
begin
if @dept1 is null or @dept1<>@dept
begin
if substring(@ctype,1,1)='貨'
set @qty=@qty *-1
set @sum=@qty
set @dept1=@dept
set @c=datediff(dd,@udate,@cdate)
set @udate=@cdate
update #test22 set c=@c where i=@i and dept=@dept
set @i=1
end
else
begin
if substring(@ctype,1,1)='貨'
set @qty=@qty *-1
set @sum=@sum+@qty
set @c=datediff(dd,@udate,@cdate)
set @udate=@cdate
update #test22 set c=@c where i=@i and dept=@dept
set @i=@i+1
end
insert into #test22(dept,cdate,qty,ctype,i,c)
select @dept,@cdate,@sum,@ctype,@i,0
fetch next from kk into @dept,@cdate,@qty,@ctype
end
set @c=datediff(dd,@udate,@edate)
update #test22 set c=@c where i=@i and dept=@dept
close kk
deallocate kk
select dept,cdate,qty,c from #test22
drop table #test22
-----------------------------------------------------------------------------
--------------運行 ct 過程 -------------------------------------------
--- ct '2004/11/20','2005/01/15'
----------------------------The end----------------------------------------------
---------輸出-----------------------
D01 2004-11-20 0 38
D01 2004-12-28 500 3
D01 2004-12-31 1500 3
D01 2005-01-03 -3500 0
D01 2005-01-03 -1700 9
D01 2005-01-12 -3700 3
運行 ct過程ct '2004/11/20','2005/01/15'
----便得出結果----------
---------輸出-----------------------
D01 2004-11-20 0 38
D01 2004-12-28 500 3
D01 2004-12-31 1500 3
D01 2005-01-03 -3500 0
D01 2005-01-03 -1700 9
D01 2005-01-12 -3700 3
(
dept varchar(3),
cdate datetime,
qty decimal(8,1) null,
ctype varchar(10)
)insert into test22
select 'D01', '2004-12-28', 500.0, '借(收入)' union all
select 'D01', '2004-12-31', 1000.0, '借(收入)' union all
select 'D01', '2005-01-03', 5000.0, '貨(付出)' union all
select 'D01', '2005-01-03', 1800.0, '借(收入)' union all
select 'D01', '2005-01-12', 2000.0, '貨(付出)' union all
select 'D02', '2004-12-28', 500.0, '借(收入)' union all
select 'D02', '2004-12-31', 1000.0, '借(收入)' union all
select 'D02', '2005-01-03', 5000.0, '貨(付出)' union all
select 'D02', '2005-01-03', 1800.0, '借(收入)' union all
select 'D02', '2005-01-12', 2000.0, '貨(付出)' select * from test22 order by dept,cdate
------------------------Create Procedure-------------------------------------------
Create Procedure ct
@date datetime,
@edate datetime
as
create Table #test22
(
dept varchar(3),
cdate datetime,
qty decimal(8,1) null,
ctype varchar(10) null,
i int null,
c int null
)
declare @dept varchar(3),@cdate datetime,@qty decimal(8,1),@ctype varchar(10),@dept1 varchar(3)
Declare @sum decimal(8,1),@c int,@udate datetime,@i int
set @i=0
set @udate=@date
set @c=0
set @sum=0
insert into #test22(dept,cdate,qty,ctype,i,c)
select dept,@date,0,'',0,0 from (select dept,cdate=min(cdate) from test22 group by dept) a where cdate<>@date
Declare kk cursor for
select dept,cdate,qty,ctype from test22 order by dept,cdate
open kk
fetch next from kk into @dept,@cdate,@qty,@ctype
set @dept1=''
while @@fetch_status=0
begin
if @dept1 is null or @dept1<>@dept
begin
if substring(@ctype,1,1)='貨'
set @qty=@qty *-1
set @sum=@qty
if @i=0
begin
set @c=datediff(dd,@date,@cdate)
update #test22 set c=@c where i=@i and dept=@dept
end
else
begin
set @c=datediff(dd,@udate,@edate)
update #test22 set c=@c where i=@i and dept=@dept1
set @c=datediff(dd,@date,@cdate)
set @i=0
update #test22 set c=@c where i=@i and dept=@dept
end
set @udate=@cdate
set @i=1
set @dept1=@dept
end
else if @dept1=@dept
begin
if substring(@ctype,1,1)='貨'
set @qty=@qty *-1
set @sum=@sum+@qty
set @c=datediff(dd,@udate,@cdate)
set @udate=@cdate
update #test22 set c=@c where i=@i and dept=@dept
set @i=@i+1
end
insert into #test22(dept,cdate,qty,ctype,i,c)
select @dept,@cdate,@sum,@ctype,@i,0
fetch next from kk into @dept,@cdate,@qty,@ctype
end
set @c=datediff(dd,@udate,@edate)
update #test22 set c=@c where i=@i and dept=@dept
close kk
deallocate kk
select dept,cdate,qty,c from #test22 order by dept,i
drop table #test22
-----------------------------------------------------------------------------
--------------運行 ct 過程 -------------------------------------------
--- ct '2004/11/20','2005/01/15'
----------------------------The end----------------------------------------------
---------輸出---------------
D01 2004-11-20 00:00:00.000 .0 38
D01 2004-12-28 00:00:00.000 500.0 3
D01 2004-12-31 00:00:00.000 1500.0 3
D01 2005-01-03 00:00:00.000 -3500.0 0
D01 2005-01-03 00:00:00.000 -1700.0 9
D01 2005-01-12 00:00:00.000 -3700.0 3D02 2004-11-20 00:00:00.000 .0 38
D02 2004-12-28 00:00:00.000 500.0 3
D02 2004-12-31 00:00:00.000 1500.0 3
D02 2005-01-03 00:00:00.000 -3500.0 0
D02 2005-01-03 00:00:00.000 -1700.0 9
D02 2005-01-12 00:00:00.000 -3700.0 3