lijianfu
/
EEMS
1
0
Fork 0
Code Issues Pull Requests Projects Releases Wiki Activity
狮山水务系统
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
1 Commit
1 Branch
0 Tags
8.8 MiB
Branch: dev
Branches Tags
${ item.name }
Create tag ${ searchTerm }
Create branch ${ searchTerm }
from 'dev'
${ noResults }
EEMS/document/存储过程_使用量分析.sql

693 lines
27 KiB
Raw Permalink Normal View History Unescape

初始化代码
8 months ago
-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,ljf>
-- Create date: <Create Date,,2019-08-15>
-- Description: <Description,,ʹ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>>
-- =============================================
ALTER PROCEDURE pro_usage_analysis
@level int, --<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ
@nodeName varchar(50), --<EFBFBD>ڵ<EFBFBD><EFBFBD><EFBFBD>ֵ
@time varchar(20), --<EFBFBD><EFBFBD><EFBFBD><EFBFBD>ʱ<EFBFBD><EFBFBD>
@page int, --ҳ<EFBFBD><EFBFBD>
@limit int, --<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
@result_count int out --<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ļ<EFBFBD>¼ֵ
AS
declare @nodeCount int --<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>¥<EFBFBD>̡<EFBFBD>¥<EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ŀ
declare @name varchar(50) --<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>¥<EFBFBD>̡<EFBFBD>¥<EFBFBD><EFBFBD>
declare @total_used numeric(24,2) --<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
declare @table_name varchar(20) --<EFBFBD><EFBFBD>ȡ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>ʱ<EFBFBD><EFBFBD>
declare @quarter varchar(10) --<EFBFBD><EFBFBD>ȡ<EFBFBD><EFBFBD><EFBFBD>ڼ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
declare @sqlStr nvarchar(max) --sql<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
declare @result_table table
(
id bigint identity(1,1) not null,
times varchar(50) null,
name varchar(50) null,
total_used numeric(24,2) null,
ratio varchar(20) null
)
BEGIN
if @level is null
begin
set @level = 1
end
if @nodeName is null
begin
select top 1 @nodeName = house_num from user_manage where parent_id = 0
end
if @time is null
begin
set @time = convert(varchar(10),datepart(yyyy,getdate()))+'<EFBFBD><EFBFBD><EFBFBD><EFBFBD>1<EFBFBD><EFBFBD><EFBFBD><EFBFBD>'
end
print @time
--<EFBFBD><EFBFBD>ѯ<EFBFBD><EFBFBD>ǰ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ж<EFBFBD><EFBFBD>ٸ<EFBFBD><EFBFBD>ӽڵ<EFBFBD>
--select * from user_manage where parent_id =(select id from user_manage where house_num = ''+@nodeName+'' and node_type = ''+@level+'')
if len(@time) = 10 --<EFBFBD><EFBFBD><EFBFBD>ղ<EFBFBD>ѯ
begin
--<EFBFBD><EFBFBD>ȡ<EFBFBD><EFBFBD><EFBFBD>ĺ<EFBFBD>׺ʱ<EFBFBD><EFBFBD>
set @table_name = replace(substring(@time,1,7),'-','')
if @level = 3 --<EFBFBD><EFBFBD>ѯ<EFBFBD><EFBFBD><EFBFBD>嵽¥<EFBFBD><EFBFBD>
begin
--<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>α<EFBFBD>
declare cs_name cursor for
select house_num from user_manage where parent_id =(select id from user_manage where house_num = ''+@nodeName+'' and node_type = ''+@level+'')
--<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>α<EFBFBD>
open cs_name
fetch next from cs_name into @name
while @@fetch_status = 0
begin
--<EFBFBD>ж<EFBFBD><EFBFBD>Ƿ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ
set @sqlStr ='select top 1 @nodeCount = count(*) from wt_building_day'+@table_name+' where building_name = '''+@name+'''';
--ִ<EFBFBD><EFBFBD>SQL<EFBFBD><EFBFBD><EFBFBD>䣬<EFBFBD>ѽ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
exec sp_executesql @sqlStr,N'@nodeCount int out',@nodeCount out
if @nodeCount <> 0
begin
set @sqlStr = 'select top 1 @total_used = isnull(calc_flow,0.00) from wt_building_day'+@table_name
+' where building_name = '''+@name+''' and convert(varchar(10),cur_date,23) = '''+@time+'''';
--ִ<EFBFBD><EFBFBD>SQL<EFBFBD><EFBFBD><EFBFBD>䣬<EFBFBD>ѽ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
exec sp_executesql @sqlStr,N'@total_used int out',@total_used out
end
else
begin
set @total_used = 0
end
insert into @result_table(times,name,total_used)values(@time,@name,@total_used)
fetch next from cs_name into @name
end
--<EFBFBD>ر<EFBFBD><EFBFBD>α<EFBFBD>
close cs_name
--<EFBFBD>ͷ<EFBFBD><EFBFBD>α<EFBFBD>
deallocate cs_name
select top 1 @total_used = isnull(convert(numeric(24,2),sum(total_used)),0.00) from @result_table
update @result_table set ratio = convert(varchar(20),convert(numeric(24,2),total_used/@total_used)*100)+'%'
end
else
if @level = 2 --<EFBFBD><EFBFBD>ѯ<EFBFBD><EFBFBD><EFBFBD>嵽¥<EFBFBD><EFBFBD>
begin
--<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>α<EFBFBD>
declare cs_name cursor for
select house_num from user_manage where parent_id =(select id from user_manage where house_num = ''+@nodeName+'' and node_type = ''+@level+'')
--<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>α<EFBFBD>
open cs_name
fetch next from cs_name into @name
while @@fetch_status = 0
begin
--<EFBFBD>ж<EFBFBD><EFBFBD>Ƿ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ
set @sqlStr ='select top 1 @nodeCount = count(*) from wt_building_day'+@table_name+' where areas_name = '''+@name+'''';
--ִ<EFBFBD><EFBFBD>SQL<EFBFBD><EFBFBD><EFBFBD>䣬<EFBFBD>ѽ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
exec sp_executesql @sqlStr,N'@nodeCount int out',@nodeCount out
if @nodeCount <> 0
begin
set @sqlStr = 'select top 1 @total_used = isnull(calc_flow,0.00) from wt_building_day'+@table_name
+' where areas_name = '''+@name+''' and convert(varchar(10),cur_date,23) = '''+@time+'''';
--ִ<EFBFBD><EFBFBD>SQL<EFBFBD><EFBFBD><EFBFBD>䣬<EFBFBD>ѽ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
exec sp_executesql @sqlStr,N'@total_used int out',@total_used out
end
else
begin
set @total_used = 0
end
insert into @result_table(times,name,total_used)values(@time,@name,@total_used)
fetch next from cs_name into @name
end
--<EFBFBD>ر<EFBFBD><EFBFBD>α<EFBFBD>
close cs_name
--<EFBFBD>ͷ<EFBFBD><EFBFBD>α<EFBFBD>
deallocate cs_name
select top 1 @total_used = isnull(convert(numeric(24,2),sum(total_used)),0.00) from @result_table
if @total_used = 0
begin
update @result_table set ratio = '0.00%'
end else
begin
update @result_table set ratio = convert(varchar(20),convert(numeric(24,2),total_used/@total_used)*100)+'%'
end
end
else
if @level = 1 --<EFBFBD><EFBFBD>ѯ<EFBFBD><EFBFBD>ij<EFBFBD><EFBFBD>Ƭ<EFBFBD><EFBFBD>
begin
--<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>α<EFBFBD>
declare cs_name cursor for
(select house_num from user_manage where parent_id in
(select id from user_manage where parent_id =
(select id from user_manage where house_num = ''+@nodeName+'' and node_type = ''+@level+'')))
--<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>α<EFBFBD>
open cs_name
fetch next from cs_name into @name
while @@fetch_status = 0
begin
--<EFBFBD>ж<EFBFBD><EFBFBD>Ƿ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ
set @sqlStr ='select top 1 @nodeCount = count(*) from wt_building_day'+@table_name+' where areas_name = '''+@name+'''';
--ִ<EFBFBD><EFBFBD>SQL<EFBFBD><EFBFBD><EFBFBD>䣬<EFBFBD>ѽ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
exec sp_executesql @sqlStr,N'@nodeCount int out',@nodeCount out
if @nodeCount <> 0
begin
set @sqlStr = 'select top 1 @total_used = isnull(calc_flow,0.00) from wt_building_day'+@table_name
+' where areas_name = '''+@name+''' and convert(varchar(10),cur_date,23) = '''+@time+'''';
--ִ<EFBFBD><EFBFBD>SQL<EFBFBD><EFBFBD><EFBFBD>䣬<EFBFBD>ѽ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
exec sp_executesql @sqlStr,N'@total_used int out',@total_used out
end
else
begin
set @total_used = 0
end
insert into @result_table(times,name,total_used)values(@time,@name,@total_used)
fetch next from cs_name into @name
end
--<EFBFBD>ر<EFBFBD><EFBFBD>α<EFBFBD>
close cs_name
--<EFBFBD>ͷ<EFBFBD><EFBFBD>α<EFBFBD>
deallocate cs_name
select top 1 @total_used = isnull(convert(numeric(24,2),sum(total_used)),0.00) from @result_table
if @total_used = 0
begin
update @result_table set ratio = '0.00%'
end else
begin
update @result_table set ratio = convert(varchar(20),convert(numeric(24,2),total_used/@total_used)*100)+'%'
end
end
end
else
if len(@time) = 7 --<EFBFBD><EFBFBD><EFBFBD>²<EFBFBD>ѯ
begin
if @level = 3 --<EFBFBD><EFBFBD>ѯ<EFBFBD><EFBFBD><EFBFBD>嵽¥<EFBFBD><EFBFBD>
begin
--<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>α<EFBFBD>
declare cs_name cursor for
select house_num from user_manage where parent_id =(select id from user_manage where house_num = ''+@nodeName+'' and node_type = ''+@level+'')
--<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>α<EFBFBD>
open cs_name
fetch next from cs_name into @name
while @@fetch_status = 0
begin
--<EFBFBD>ж<EFBFBD><EFBFBD>Ƿ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ
set @sqlStr ='select top 1 @nodeCount = count(*) from wt_building_day'+@table_name+' where building_name = '''+@name+'''';
--ִ<EFBFBD><EFBFBD>SQL<EFBFBD><EFBFBD><EFBFBD>䣬<EFBFBD>ѽ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
exec sp_executesql @sqlStr,N'@nodeCount int out',@nodeCount out
if @nodeCount <> 0
begin
--select top 1 @total_used = isnull(calc_flow,0.00) from wt_building_month2019 where building_name = ''+@name+''
set @sqlStr = 'select top 1 @total_used = isnull(calc_flow,0.00) from wt_building_month'+@table_name
+' where building_name = '''+@name+''' and cur_date = '''+@time+'''';
--ִ<EFBFBD><EFBFBD>SQL<EFBFBD><EFBFBD><EFBFBD>䣬<EFBFBD>ѽ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
exec sp_executesql @sqlStr,N'@total_used int out',@total_used out
end
else
begin
set @total_used = 0
end
insert into @result_table(times,name,total_used)values(@time,@name,@total_used)
fetch next from cs_name into @name
end
--<EFBFBD>ر<EFBFBD><EFBFBD>α<EFBFBD>
close cs_name
--<EFBFBD>ͷ<EFBFBD><EFBFBD>α<EFBFBD>
deallocate cs_name
select top 1 @total_used = isnull(convert(numeric(24,2),sum(total_used)),0.00) from @result_table
update @result_table set ratio = convert(varchar(20),convert(numeric(24,2),total_used/@total_used)*100)+'%'
end
else
if @level = 2 --<EFBFBD><EFBFBD>ѯ<EFBFBD><EFBFBD><EFBFBD>嵽¥<EFBFBD><EFBFBD>
begin
--<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>α<EFBFBD>
declare cs_name cursor for
select house_num from user_manage where parent_id =(select id from user_manage where house_num = ''+@nodeName+'' and node_type = ''+@level+'')
--<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>α<EFBFBD>
open cs_name
fetch next from cs_name into @name
while @@fetch_status = 0
begin
--<EFBFBD>ж<EFBFBD><EFBFBD>Ƿ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ
set @sqlStr ='select top 1 @nodeCount = count(*) from wt_building_day'+@table_name+' where areas_name = '''+@name+'''';
--ִ<EFBFBD><EFBFBD>SQL<EFBFBD><EFBFBD><EFBFBD>䣬<EFBFBD>ѽ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
exec sp_executesql @sqlStr,N'@nodeCount int out',@nodeCount out
if @nodeCount <> 0
begin
--select top 1 @total_used = isnull(calc_flow,0.00) from wt_building_month2019 where areas_name = ''+@name+''
set @sqlStr = 'select top 1 @total_used = isnull(calc_flow,0.00) from wt_building_month'+@table_name
+' where areas_name = '''+@name+''' and cur_date = '''+@time+'''';
--ִ<EFBFBD><EFBFBD>SQL<EFBFBD><EFBFBD><EFBFBD>䣬<EFBFBD>ѽ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
exec sp_executesql @sqlStr,N'@total_used int out',@total_used out
end
else
begin
set @total_used = 0
end
insert into @result_table(times,name,total_used)values(@time,@name,@total_used)
fetch next from cs_name into @name
end
--<EFBFBD>ر<EFBFBD><EFBFBD>α<EFBFBD>
close cs_name
--<EFBFBD>ͷ<EFBFBD><EFBFBD>α<EFBFBD>
deallocate cs_name
select top 1 @total_used = isnull(convert(numeric(24,2),sum(total_used)),0.00) from @result_table
if @total_used = 0
begin
update @result_table set ratio = '0.00%'
end else
begin
update @result_table set ratio = convert(varchar(20),convert(numeric(24,2),total_used/@total_used)*100)+'%'
end
end
else
if @level = 1 --<EFBFBD><EFBFBD>ѯ<EFBFBD><EFBFBD>ij<EFBFBD><EFBFBD>Ƭ<EFBFBD><EFBFBD>
begin
--<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>α<EFBFBD>
declare cs_name cursor for
(select house_num from user_manage where parent_id in
(select id from user_manage where parent_id =
(select id from user_manage where house_num = ''+@nodeName+'' and node_type = ''+@level+'')))
--<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>α<EFBFBD>
open cs_name
fetch next from cs_name into @name
while @@fetch_status = 0
begin
--<EFBFBD>ж<EFBFBD><EFBFBD>Ƿ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ
--select top 1 @nodeCount = count(*) from wt_building_month2019 where areas_name = ''+@name+''
--<EFBFBD>ж<EFBFBD><EFBFBD>Ƿ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ
set @sqlStr ='select top 1 @nodeCount = count(*) from wt_building_day'+@table_name+' where areas_name = '''+@name+'''';
--ִ<EFBFBD><EFBFBD>SQL<EFBFBD><EFBFBD><EFBFBD>䣬<EFBFBD>ѽ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
exec sp_executesql @sqlStr,N'@nodeCount int out',@nodeCount out
if @nodeCount <> 0
begin
--select top 1 @total_used = isnull(calc_flow,0.00) from wt_building_month2019 where areas_name = ''+@name+''
set @sqlStr = 'select top 1 @total_used = isnull(calc_flow,0.00) from wt_building_month'+@table_name
+' where areas_name = '''+@name+''' and cur_date = '''+@time+'''';
--ִ<EFBFBD><EFBFBD>SQL<EFBFBD><EFBFBD><EFBFBD>䣬<EFBFBD>ѽ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
exec sp_executesql @sqlStr,N'@total_used int out',@total_used out
end
else
begin
set @total_used = 0
end
insert into @result_table(times,name,total_used)values(@time,@name,@total_used)
fetch next from cs_name into @name
end
--<EFBFBD>ر<EFBFBD><EFBFBD>α<EFBFBD>
close cs_name
--<EFBFBD>ͷ<EFBFBD><EFBFBD>α<EFBFBD>
deallocate cs_name
select top 1 @total_used = isnull(convert(numeric(24,2),sum(total_used)),0.00) from @result_table
if @total_used = 0
begin
update @result_table set ratio = '0.00%'
end else
begin
update @result_table set ratio = convert(varchar(20),convert(numeric(24,2),total_used/@total_used)*100)+'%'
end
end
end
if len(@time) = 4 --<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ѯ
begin
if @level = 3 --<EFBFBD><EFBFBD>ѯ<EFBFBD><EFBFBD><EFBFBD>嵽¥<EFBFBD><EFBFBD>
begin
--<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>α<EFBFBD>
declare cs_name cursor for
select house_num from user_manage where parent_id =(select id from user_manage where house_num = ''+@nodeName+'' and node_type = ''+@level+'')
--<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>α<EFBFBD>
open cs_name
fetch next from cs_name into @name
while @@fetch_status = 0
begin
--<EFBFBD>ж<EFBFBD><EFBFBD>Ƿ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ
--select top 1 @nodeCount = count(*) from wt_building_year2019 where building_name = ''+@name+''
--<EFBFBD>ж<EFBFBD><EFBFBD>Ƿ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ
set @sqlStr ='select top 1 @nodeCount = count(*) from wt_building_year'+@table_name+' where building_name = '''+@name+'''';
--ִ<EFBFBD><EFBFBD>SQL<EFBFBD><EFBFBD><EFBFBD>䣬<EFBFBD>ѽ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
exec sp_executesql @sqlStr,N'@nodeCount int out',@nodeCount out
if @nodeCount <> 0
begin
--select top 1 @total_used = isnull(calc_flow,0.00) from wt_building_year2019 where building_name = ''+@name+''
set @sqlStr = 'select top 1 @total_used = isnull(calc_flow,0.00) from wt_building_year'+@table_name
+' where building_name = '''+@name+''' and cur_date = '''+@time+'''';
--ִ<EFBFBD><EFBFBD>SQL<EFBFBD><EFBFBD><EFBFBD>䣬<EFBFBD>ѽ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
exec sp_executesql @sqlStr,N'@total_used int out',@total_used out
end
else
begin
set @total_used = 0
end
insert into @result_table(times,name,total_used)values(@time,@name,@total_used)
fetch next from cs_name into @name
end
--<EFBFBD>ر<EFBFBD><EFBFBD>α<EFBFBD>
close cs_name
--<EFBFBD>ͷ<EFBFBD><EFBFBD>α<EFBFBD>
deallocate cs_name
select top 1 @total_used = isnull(convert(numeric(24,2),sum(total_used)),0.00) from @result_table
update @result_table set ratio = convert(varchar(20),convert(numeric(24,2),total_used/@total_used)*100)+'%'
end
else
if @level = 2 --<EFBFBD><EFBFBD>ѯ<EFBFBD><EFBFBD><EFBFBD>嵽¥<EFBFBD><EFBFBD>
begin
--<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>α<EFBFBD>
declare cs_name cursor for
select house_num from user_manage where parent_id =(select id from user_manage where house_num = ''+@nodeName+'' and node_type = ''+@level+'')
--<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>α<EFBFBD>
open cs_name
fetch next from cs_name into @name
while @@fetch_status = 0
begin
--<EFBFBD>ж<EFBFBD><EFBFBD>Ƿ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ
--select top 1 @nodeCount = count(*) from wt_building_year2019 where areas_name = ''+@name+''
--<EFBFBD>ж<EFBFBD><EFBFBD>Ƿ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ
set @sqlStr ='select top 1 @nodeCount = count(*) from wt_building_year'+@table_name+' where areas_name = '''+@name+'''';
--ִ<EFBFBD><EFBFBD>SQL<EFBFBD><EFBFBD><EFBFBD>䣬<EFBFBD>ѽ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
exec sp_executesql @sqlStr,N'@nodeCount int out',@nodeCount out
if @nodeCount <> 0
begin
--select top 1 @total_used = isnull(calc_flow,0.00) from wt_building_year2019 where areas_name = ''+@name+''
set @sqlStr = 'select top 1 @total_used = isnull(calc_flow,0.00) from wt_building_year'+@table_name
+' where areas_name = '''+@name+''' and cur_date = '''+@time+'''';
--ִ<EFBFBD><EFBFBD>SQL<EFBFBD><EFBFBD><EFBFBD>䣬<EFBFBD>ѽ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
exec sp_executesql @sqlStr,N'@total_used int out',@total_used out
end
else
begin
set @total_used = 0
end
insert into @result_table(times,name,total_used)values(@time,@name,@total_used)
fetch next from cs_name into @name
end
--<EFBFBD>ر<EFBFBD><EFBFBD>α<EFBFBD>
close cs_name
--<EFBFBD>ͷ<EFBFBD><EFBFBD>α<EFBFBD>
deallocate cs_name
select top 1 @total_used = isnull(convert(numeric(24,2),sum(total_used)),0.00) from @result_table
if @total_used = 0
begin
update @result_table set ratio = '0.00%'
end else
begin
update @result_table set ratio = convert(varchar(20),convert(numeric(24,2),total_used/@total_used)*100)+'%'
end
end
else
if @level = 1 --<EFBFBD><EFBFBD>ѯ<EFBFBD><EFBFBD>ij<EFBFBD><EFBFBD>Ƭ<EFBFBD><EFBFBD>
begin
--<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>α<EFBFBD>
declare cs_name cursor for
(select house_num from user_manage where parent_id in
(select id from user_manage where parent_id =
(select id from user_manage where house_num = ''+@nodeName+'' and node_type = ''+@level+'')))
--<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>α<EFBFBD>
open cs_name
fetch next from cs_name into @name
while @@fetch_status = 0
begin
--<EFBFBD>ж<EFBFBD><EFBFBD>Ƿ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ
--select top 1 @nodeCount = count(*) from wt_building_year2019 where areas_name = ''+@name+''
--<EFBFBD>ж<EFBFBD><EFBFBD>Ƿ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ
set @sqlStr ='select top 1 @nodeCount = count(*) from wt_building_year'+@table_name+' where areas_name = '''+@name+'''';
--ִ<EFBFBD><EFBFBD>SQL<EFBFBD><EFBFBD><EFBFBD>䣬<EFBFBD>ѽ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
exec sp_executesql @sqlStr,N'@nodeCount int out',@nodeCount out
if @nodeCount <> 0
begin
--select top 1 @total_used = isnull(calc_flow,0.00) from wt_building_year2019 where areas_name = ''+@name+''
set @sqlStr = 'select top 1 @total_used = isnull(calc_flow,0.00) from wt_building_year'+@table_name
+' where areas_name = '''+@name+''' and cur_date = '''+@time+'''';
--ִ<EFBFBD><EFBFBD>SQL<EFBFBD><EFBFBD><EFBFBD>䣬<EFBFBD>ѽ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
exec sp_executesql @sqlStr,N'@total_used int out',@total_used out
end
else
begin
set @total_used = 0
end
insert into @result_table(times,name,total_used)values(@time,@name,@total_used)
fetch next from cs_name into @name
end
--<EFBFBD>ر<EFBFBD><EFBFBD>α<EFBFBD>
close cs_name
--<EFBFBD>ͷ<EFBFBD><EFBFBD>α<EFBFBD>
deallocate cs_name
select top 1 @total_used = isnull(convert(numeric(24,2),sum(total_used)),0.00) from @result_table
if @total_used = 0
begin
update @result_table set ratio = '0.00%'
end else
begin
update @result_table set ratio = convert(varchar(20),convert(numeric(24,2),total_used/@total_used)*100)+'%'
end
end
end
else
begin --<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ȳ<EFBFBD>ѯ
--<EFBFBD><EFBFBD>ȡ<EFBFBD><EFBFBD>һ<EFBFBD><EFBFBD>
set @table_name = substring(@time,1,4)
--<EFBFBD><EFBFBD>ȡ<EFBFBD><EFBFBD>һ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
set @quarter = substring(@time,6,5)
--<EFBFBD>ж<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>һ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
--if charindex('1',@quarter) > 0 --<EFBFBD><EFBFBD>һ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
begin
if @level = 3 --<EFBFBD><EFBFBD>ѯ<EFBFBD><EFBFBD><EFBFBD>嵽¥<EFBFBD><EFBFBD>
begin
--<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>α<EFBFBD>
declare cs_name cursor for
select house_num from user_manage where parent_id =(select id from user_manage where house_num = ''+@nodeName+'' and node_type = ''+@level+'')
--<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>α<EFBFBD>
open cs_name
fetch next from cs_name into @name
while @@fetch_status = 0
begin
--<EFBFBD>ж<EFBFBD><EFBFBD>Ƿ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ
--select top 1 @nodeCount = count(*) from wt_building_month2019 where building_name = ''+@name+''
--<EFBFBD>ж<EFBFBD><EFBFBD>Ƿ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ
set @sqlStr ='select top 1 @nodeCount = count(*) from wt_building_month'+@table_name+' where building_name = '''+@name+'''';
--ִ<EFBFBD><EFBFBD>SQL<EFBFBD><EFBFBD><EFBFBD>䣬<EFBFBD>ѽ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
exec sp_executesql @sqlStr,N'@nodeCount int out',@nodeCount out
if @nodeCount <> 0
begin
--select top 1 @total_used = isnull(calc_flow,0.00) from wt_building_month2019 where building_name = ''+@name+''
if charindex('1',@quarter) > 0 --<EFBFBD><EFBFBD>һ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
begin
set @sqlStr = 'select top 1 @total_used = isnull(calc_flow,0.00) from wt_building_month'+@table_name
+' where building_name = '''+@name+''' and cur_date = '''+@table_name+'-01'+''' or cur_date = '''+@table_name+'-02'+''' or cur_date = '''+@table_name+'-03'+'''';
--ִ<EFBFBD><EFBFBD>SQL<EFBFBD><EFBFBD><EFBFBD>䣬<EFBFBD>ѽ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
exec sp_executesql @sqlStr,N'@total_used int out',@total_used out
end
else
if charindex('2',@quarter) > 0 --<EFBFBD>ڶ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
begin
set @sqlStr = 'select top 1 @total_used = isnull(calc_flow,0.00) from wt_building_month'+@table_name
+' where building_name = '''+@name+''' and cur_date = '''+@table_name+'-04'+''' or cur_date = '''+@table_name+'-05'+''' or cur_date = '''+@table_name+'-06'+'''';
--ִ<EFBFBD><EFBFBD>SQL<EFBFBD><EFBFBD><EFBFBD>䣬<EFBFBD>ѽ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
exec sp_executesql @sqlStr,N'@total_used int out',@total_used out
end
else
if charindex('3',@quarter) > 0 --<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
begin
set @sqlStr = 'select top 1 @total_used = isnull(calc_flow,0.00) from wt_building_month'+@table_name
+' where building_name = '''+@name+''' and cur_date = '''+@table_name+'-07'+''' or cur_date = '''+@table_name+'-08'+''' or cur_date = '''+@table_name+'-09'+'''';
--ִ<EFBFBD><EFBFBD>SQL<EFBFBD><EFBFBD><EFBFBD>䣬<EFBFBD>ѽ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
exec sp_executesql @sqlStr,N'@total_used int out',@total_used out
end
else
if charindex('4',@quarter) > 0 --<EFBFBD><EFBFBD><EFBFBD>ļ<EFBFBD><EFBFBD><EFBFBD>
begin
set @sqlStr = 'select top 1 @total_used = isnull(calc_flow,0.00) from wt_building_month'+@table_name
+' where building_name = '''+@name+''' and cur_date = '''+@table_name+'-10'+''' or cur_date = '''+@table_name+'-11'+''' or cur_date = '''+@table_name+'-12'+'''';
--ִ<EFBFBD><EFBFBD>SQL<EFBFBD><EFBFBD><EFBFBD>䣬<EFBFBD>ѽ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
exec sp_executesql @sqlStr,N'@total_used int out',@total_used out
end
end
else
begin
set @total_used = 0
end
insert into @result_table(times,name,total_used)values(@time,@name,isnull(@total_used,0.00))
fetch next from cs_name into @name
end
--<EFBFBD>ر<EFBFBD><EFBFBD>α<EFBFBD>
close cs_name
--<EFBFBD>ͷ<EFBFBD><EFBFBD>α<EFBFBD>
deallocate cs_name
select top 1 @total_used = isnull(convert(numeric(24,2),sum(total_used)),0.00) from @result_table
update @result_table set ratio = convert(varchar(20),convert(numeric(24,2),total_used/@total_used)*100)+'%'
end
else
if @level = 2 --<EFBFBD><EFBFBD>ѯ<EFBFBD><EFBFBD><EFBFBD>嵽¥<EFBFBD><EFBFBD>
begin
--<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>α<EFBFBD>
declare cs_name cursor for
select house_num from user_manage where parent_id =(select id from user_manage where house_num = ''+@nodeName+'' and node_type = ''+@level+'')
--<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>α<EFBFBD>
open cs_name
fetch next from cs_name into @name
while @@fetch_status = 0
begin
--<EFBFBD>ж<EFBFBD><EFBFBD>Ƿ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ
--select top 1 @nodeCount = count(*) from wt_building_month2019 where areas_name = ''+@name+''
--<EFBFBD>ж<EFBFBD><EFBFBD>Ƿ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ
set @sqlStr ='select top 1 @nodeCount = count(*) from wt_building_month'+@table_name+' where areas_name = '''+@name+'''';
--ִ<EFBFBD><EFBFBD>SQL<EFBFBD><EFBFBD><EFBFBD>䣬<EFBFBD>ѽ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
exec sp_executesql @sqlStr,N'@nodeCount int out',@nodeCount out
if @nodeCount <> 0
begin
--select top 1 @total_used = isnull(calc_flow,0.00) from wt_building_month2019 where areas_name = ''+@name+''
--set @sqlStr = 'select top 1 @total_used = isnull(calc_flow,0.00) from wt_building_month'+@table_name
-- +' where areas_name = '''+@name+''' and cur_date = '''+@table_name+'-01'+''' or cur_date = '''+@table_name+'-02'+''' or cur_date = '''+@table_name+'-02'+'''';
----ִ<EFBFBD><EFBFBD>SQL<EFBFBD><EFBFBD><EFBFBD>䣬<EFBFBD>ѽ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
--exec sp_executesql @sqlStr,N'@total_used int out',@total_used out
if charindex('1',@quarter) > 0 --<EFBFBD><EFBFBD>һ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
begin
set @sqlStr = 'select top 1 @total_used = isnull(calc_flow,0.00) from wt_building_month'+@table_name
+' where areas_name = '''+@name+''' and cur_date = '''+@table_name+'-01'+''' or cur_date = '''+@table_name+'-02'+''' or cur_date = '''+@table_name+'-03'+'''';
--ִ<EFBFBD><EFBFBD>SQL<EFBFBD><EFBFBD><EFBFBD>䣬<EFBFBD>ѽ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
exec sp_executesql @sqlStr,N'@total_used int out',@total_used out
end
else
if charindex('2',@quarter) > 0 --<EFBFBD>ڶ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
begin
set @sqlStr = 'select top 1 @total_used = isnull(calc_flow,0.00) from wt_building_month'+@table_name
+' where areas_name = '''+@name+''' and cur_date = '''+@table_name+'-04'+''' or cur_date = '''+@table_name+'-05'+''' or cur_date = '''+@table_name+'-06'+'''';
--ִ<EFBFBD><EFBFBD>SQL<EFBFBD><EFBFBD><EFBFBD>䣬<EFBFBD>ѽ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
exec sp_executesql @sqlStr,N'@total_used int out',@total_used out
end
else
if charindex('3',@quarter) > 0 --<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
begin
set @sqlStr = 'select top 1 @total_used = isnull(calc_flow,0.00) from wt_building_month'+@table_name
+' where areas_name = '''+@name+''' and cur_date = '''+@table_name+'-07'+''' or cur_date = '''+@table_name+'-08'+''' or cur_date = '''+@table_name+'-09'+'''';
--ִ<EFBFBD><EFBFBD>SQL<EFBFBD><EFBFBD><EFBFBD>䣬<EFBFBD>ѽ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
exec sp_executesql @sqlStr,N'@total_used int out',@total_used out
end
else
if charindex('4',@quarter) > 0 --<EFBFBD><EFBFBD><EFBFBD>ļ<EFBFBD><EFBFBD><EFBFBD>
begin
set @sqlStr = 'select top 1 @total_used = isnull(calc_flow,0.00) from wt_building_month'+@table_name
+' where areas_name = '''+@name+''' and cur_date = '''+@table_name+'-10'+''' or cur_date = '''+@table_name+'-11'+''' or cur_date = '''+@table_name+'-12'+'''';
--ִ<EFBFBD><EFBFBD>SQL<EFBFBD><EFBFBD><EFBFBD>䣬<EFBFBD>ѽ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
exec sp_executesql @sqlStr,N'@total_used int out',@total_used out
end
end
else
begin
set @total_used = 0
end
insert into @result_table(times,name,total_used)values(@time,@name,isnull(@total_used,0.00))
fetch next from cs_name into @name
end
--<EFBFBD>ر<EFBFBD><EFBFBD>α<EFBFBD>
close cs_name
--<EFBFBD>ͷ<EFBFBD><EFBFBD>α<EFBFBD>
deallocate cs_name
select top 1 @total_used = isnull(convert(numeric(24,2),sum(total_used)),0.00) from @result_table
if @total_used = 0
begin
update @result_table set ratio = '0.00%'
end else
begin
update @result_table set ratio = convert(varchar(20),convert(numeric(24,2),total_used/@total_used)*100)+'%'
end
end
else
if @level = 1 --<EFBFBD><EFBFBD>ѯ<EFBFBD><EFBFBD>ij<EFBFBD><EFBFBD>Ƭ<EFBFBD><EFBFBD>
begin
print '<EFBFBD><EFBFBD>ѯ<EFBFBD><EFBFBD>ij<EFBFBD><EFBFBD>Ƭ<EFBFBD><EFBFBD>'
--<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>α<EFBFBD>
declare cs_name cursor for
(select house_num from user_manage where parent_id in
(select id from user_manage where parent_id =
(select id from user_manage where house_num = ''+@nodeName+'' and node_type = ''+@level+'')))
--<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>α<EFBFBD>
open cs_name
fetch next from cs_name into @name
while @@fetch_status = 0
begin
--<EFBFBD>ж<EFBFBD><EFBFBD>Ƿ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ
--select top 1 @nodeCount = count(*) from wt_building_month2019 where areas_name = ''+@name+''
--<EFBFBD>ж<EFBFBD><EFBFBD>Ƿ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ
set @sqlStr ='select top 1 @nodeCount = count(*) from wt_building_month'+@table_name+' where areas_name = '''+@name+'''';
--ִ<EFBFBD><EFBFBD>SQL<EFBFBD><EFBFBD><EFBFBD>䣬<EFBFBD>ѽ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
exec sp_executesql @sqlStr,N'@nodeCount int out',@nodeCount out
if @nodeCount <> 0
begin
--select top 1 @total_used = isnull(calc_flow,0.00) from wt_building_month2019 where areas_name = ''+@name+''
--set @sqlStr = 'select top 1 @total_used = isnull(calc_flow,0.00) from wt_building_month'+@table_name
-- +' where areas_name = '''+@name+''' and cur_date = '''+@table_name+'-01'+''' or cur_date = '''+@table_name+'-02'+''' or cur_date = '''+@table_name+'-02'+'''';
----ִ<EFBFBD><EFBFBD>SQL<EFBFBD><EFBFBD><EFBFBD>䣬<EFBFBD>ѽ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
--exec sp_executesql @sqlStr,N'@total_used int out',@total_used out
if charindex('1',@quarter) > 0 --<EFBFBD><EFBFBD>һ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
begin
set @sqlStr = 'select top 1 @total_used = isnull(calc_flow,0.00) from wt_building_month'+@table_name
+' where areas_name = '''+@name+''' and cur_date = '''+@table_name+'-01'+''' or cur_date = '''+@table_name+'-02'+''' or cur_date = '''+@table_name+'-03'+'''';
--ִ<EFBFBD><EFBFBD>SQL<EFBFBD><EFBFBD><EFBFBD>䣬<EFBFBD>ѽ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
--print @sqlStr
exec sp_executesql @sqlStr,N'@total_used int out',@total_used out
end
else
if charindex('2',@quarter) > 0 --<EFBFBD>ڶ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
begin
set @sqlStr = 'select top 1 @total_used = isnull(calc_flow,0.00) from wt_building_month'+@table_name
+' where areas_name = '''+@name+''' and cur_date = '''+@table_name+'-04'+''' or cur_date = '''+@table_name+'-05'+''' or cur_date = '''+@table_name+'-06'+'''';
--ִ<EFBFBD><EFBFBD>SQL<EFBFBD><EFBFBD><EFBFBD>䣬<EFBFBD>ѽ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
exec sp_executesql @sqlStr,N'@total_used int out',@total_used out
end
else
if charindex('3',@quarter) > 0 --<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
begin
set @sqlStr = 'select top 1 @total_used = isnull(calc_flow,0.00) from wt_building_month'+@table_name
+' where areas_name = '''+@name+''' and cur_date = '''+@table_name+'-07'+''' or cur_date = '''+@table_name+'-08'+''' or cur_date = '''+@table_name+'-09'+'''';
--ִ<EFBFBD><EFBFBD>SQL<EFBFBD><EFBFBD><EFBFBD>䣬<EFBFBD>ѽ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
exec sp_executesql @sqlStr,N'@total_used int out',@total_used out
end
else
if charindex('4',@quarter) > 0 --<EFBFBD><EFBFBD><EFBFBD>ļ<EFBFBD><EFBFBD><EFBFBD>
begin
set @sqlStr = 'select top 1 @total_used = isnull(calc_flow,0.00) from wt_building_month'+@table_name
+' where areas_name = '''+@name+''' and cur_date = '''+@table_name+'-10'+''' or cur_date = '''+@table_name+'-11'+''' or cur_date = '''+@table_name+'-12'+'''';
--ִ<EFBFBD><EFBFBD>SQL<EFBFBD><EFBFBD><EFBFBD>䣬<EFBFBD>ѽ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
exec sp_executesql @sqlStr,N'@total_used int out',@total_used out
end
end
else
begin
set @total_used = 0
end
insert into @result_table(times,name,total_used)values(@time,@name,isnull(@total_used,0.00))
fetch next from cs_name into @name
end
--<EFBFBD>ر<EFBFBD><EFBFBD>α<EFBFBD>
close cs_name
--<EFBFBD>ͷ<EFBFBD><EFBFBD>α<EFBFBD>
deallocate cs_name
select top 1 @total_used = isnull(convert(numeric(24,2),sum(total_used)),0.00) from @result_table
if @total_used = 0
begin
update @result_table set ratio = '0.00%'
end else
begin
update @result_table set ratio = convert(varchar(20),convert(numeric(24,2),total_used/@total_used)*100)+'%'
end
end
end
end
select * from @result_table order by id OFFSET (@limit * (@page-1)) ROW FETCH NEXT @limit rows only
select top 1 @result_count = count(*) from @result_table
END
GO