ALTER PROCEDURE db_owner.FinanceStat
@year int
AS
declare @i int
declare @raccount float
declare @caccount float
declare @account float
declare @sql varchar(500)
set @i=1
create table #oo(mon int,raccount float,caccount float,account float)
while @i <13
begin
set @raccount=SELECT ISNULL(SUM(CASE type WHEN 0 THEN jine ELSE 0 END), 0) AS raccount where status=0 and year(addtime)[email protected] and month(addtime)[email protected]
set @caccount=SELECT ISNULL(SUM(CASE type WHEN 1 THEN jine ELSE 0 END), 0) AS raccount where status=0 and year(addtime)[email protected] and month(addtime)[email protected]
set @account=SELECT ISNULL(SUM(CASE type WHEN 0 THEN jine ELSE - jine END), 0) AS account where status=0 and year(addtime)[email protected] and month(addtime)[email protected]
insert into #oo(mon,raccount,caccount,account) values(@i,@raccount,@caccount,@account)
set @[email protected]+1
end
set @sql= 'select * from #oo '
exe(@sql)
drop table #oo
------解决方案--------------------
create PROCEDURE db_owner.FinanceStat
@year int
AS
begin
declare @i int
declare @sql varchar(500)
set @i=1
create table #oo(mon int,raccount float,caccount float,account float)
while @i <13
begin
insert into #oo(mon,raccount,caccount,account)
SELECT
@i,
@raccount=ISNULL(SUM(CASE type WHEN 0 THEN jine ELSE 0 END), 0),
@caccount=ISNULL(SUM(CASE type WHEN 1 THEN jine ELSE 0 END), 0),
@account=ISNULL(SUM(CASE type WHEN 0 THEN jine ELSE - jine END), 0)
where
status=0 and year(addtime)[email protected] and month(addtime)[email protected]
set @[email protected]+1
end
set @sql= 'select * from #oo '
exe(@sql)
drop table #oo
end
------解决方案--------------------
ALTER PROCEDURE db_owner.FinanceStat
@year int
AS
declare @i int
declare @raccount float
declare @caccount float
declare @account float
declare @sql varchar(500)
set @i=1
create table #oo(mon int,raccount float,caccount float,account float)
while @i <13
begin
set @raccount=SELECT ISNULL(SUM(CASE type WHEN 0 THEN jine ELSE 0 END), 0) AS raccount where status=0 and year(addtime)[email protected] and month(addtime)[email protected]
SELECT @caccount=ISNULL(SUM(CASE type WHEN 1 THEN jine ELSE 0 END), 0) AS raccount where status=0 and year(addtime)[email protected] and month(addtime)[email protected]
SELECT @account=ISNULL(SUM(CASE type WHEN 0 THEN jine ELSE - jine END), 0) AS account where status=0 and year(addtime)[email protected] and month(addtime)[email protected]
insert into #oo(mon,raccount,caccount,account) values(@i,@raccount,@caccount,@account)
set @[email protected]+1