create table #DSSF_SALE --创建一个临时表
(
TIME_YEAR char(4) not null,
TIME_MONT char(2) not null,
UNIT_CODE varchar(50) not null,
SALE_CODE varchar(50) not null,
POWE numeric(18,4) null ,
CUME_POWE numeric(18,4) null ,
LAST_POWE numeric(18,4) null ,
PREV_POWE numeric(18,4) null ,
LAST_CUME_POWE numeric(18,4) null ,
MONE numeric(18,4) null ,
CUME_MONE numeric(18,4) null ,
LAST_MONE numeric(18,4) null ,
PREV_MONE numeric(18,4) null ,
LAST_CUME_MONE numeric(18,4) null ,
PRIC numeric(12,6) null ,
CUME_PRIC numeric(12,6) null ,
LAST_PRIC numeric(12,6) null ,
PREV_PRIC numeric(12,6) null ,
LAST_CUME_PRIC numeric(12,6) null ,
NUMB numeric(18,4) null ,
CUME_NUMB numeric(18,4) null ,
LAST_NUMB numeric(18,4) null ,
PREV_NUMB numeric(18,4) null ,
LAST_CUME_NUMB numeric(18,4) null ,
NUMB_PRIC numeric(12,6) null ,
LAST_NUMB_PRIC numeric(12,6) null ,
CUME_NUMB_PRIC numeric(12,6) null ,
LAST_CUME_NUMB_PRIC numeric(12,6) null ,
PREV_NUMB_PRIC numeric(12,6) null ,
COSI numeric(18,4) null ,
CUME_COSI numeric(18,4) null ,
LAST_COSI numeric(18,4) null ,
PREV_COSI numeric(18,4) null ,
LAST_CUME_COSI numeric(18,4) null ,
COSD numeric(18,4) null ,
CUME_COSD numeric(18,4) null ,
LAST_COSD numeric(18,4) null ,
PREV_COSD numeric(18,4) null ,
LAST_CUME_COSD numeric(18,4) null ,
COS_PRIC numeric(12,6) null ,
LAST_COS_PRIC numeric(12,6) null ,
PREV_COS_PRIC numeric(12,6) null ,
CUME_COS_PRIC numeric(12,6) null ,
LAST_CUME_COS_PRIC numeric(12,6) null ,
CAPA numeric(18,4) null ,
CUME_CAPA numeric(18,4) null ,
LAST_CAPA numeric(18,4) null ,
PREV_CAPA numeric(18,4) null ,
LAST_CUME_CAPA numeric(18,4) null ,
NEED numeric(18,4) null ,
CUME_NEED numeric(18,4) null ,
LAST_NEED numeric(18,4) null ,
PREV_NEED numeric(18,4) null ,
LAST_CUME_NEED numeric(18,4) null ,
BASE_PRIC numeric(12,6) null ,
LAST_BASE_PRIC numeric(12,6) null ,
PREV_BASE_PRIC numeric(12,6) null ,
CUME_BASE_PRIC numeric(12,6) null ,
LAST_CUME_BASE_PRIC numeric(12,6) null ,
FGPM numeric(18,4) null ,
CUME_FGPM numeric(18,4) null ,
LAST_FGPM numeric(18,4) null ,
PREV_FGPM numeric(18,4) null ,
LAST_CUME_FGPM numeric(18,4) null ,
FGP_PRIC numeric(12,6) null ,
LAST_FGP_PRIC numeric(12,6) null ,
PREV_FGP_PRIC numeric(12,6) null ,
CUME_FGP_PRIC numeric(12,6) null ,
LAST_CUME_FGP_PRIC numeric(12,6) null ,
PREF_POWE numeric(18,4) null ,
CUME_PREF_POWE numeric(18,4) null ,
LAST_PREF_POWE numeric(18,4) null ,
PREV_PREF_POWE numeric(18,4) null ,
LAST_CUME_PREF_POWE numeric(18,4) null ,
PREF_MONE numeric(18,4) null ,
CUME_PREF_MONE numeric(18,4) null ,
LAST_PREF_MONE numeric(18,4) null ,
PREV_PREF_MONE numeric(18,4) null ,
LAST_CUME_PREF_MONE numeric(18,4) null ,
PREF_PRIC numeric(12,6) null ,
CUME_PREF_PRIC numeric(12,6) null ,
LAST_PREF_PRIC numeric(12,6) null ,
PREV_PREF_PRIC numeric(12,6) null ,
LAST_CUME_PREF_PRIC numeric(12,6) null ,
PLAN_POWE numeric(18,4) null ,
YEAR_PLAN_POWE numeric(18,4) null ,
LAST_YEAR_POWE numeric(18,4) null ,
PLAN_PRIC numeric(12,6) null ,
CUME_PLAN_POWE numeric(18,4) null ,
CUME_PLAN_PRIC numeric(12,6) null ,
PLAN_MONE numeric(18,4) null ,
CUME_PLAN_MONE numeric(18,4) null ,
YEAR_PLAN_MONE numeric(18,4) null ,
LAST_YEAR_MONE numeric(18,4) null ,
PROG_POWE numeric(18,4) null ,
CUME_PROG_POWE numeric(18,4) null ,
ADD_MONEY numeric(18,4) null ,
LAST_ADD_MONEY numeric(18,4) null ,
PREV_ADD_MONEY numeric(18,4) null ,
CUME_ADD_MONEY numeric(18,4) null ,
LAST_CUME_ADD_MONEY numeric(18,4) null ,
FS_PRIC numeric(12,6) null ,
KS_PRIC numeric(12,6) null
)declare c1 cursor for --创建一个游标
select distinct SUM_MONT,LEVE from DSSS_TIME_RELATION
where MONT=@time_mont order by LEVE ASCopen c1 --打开游标
while(@@sqlstatus=0) --按照指定的条件进行循环
begin
fetch c1 into @sum_mont,@leve --装取行值
(
TIME_YEAR char(4) not null,
TIME_MONT char(2) not null,
UNIT_CODE varchar(50) not null,
SALE_CODE varchar(50) not null,
POWE numeric(18,4) null ,
CUME_POWE numeric(18,4) null ,
LAST_POWE numeric(18,4) null ,
PREV_POWE numeric(18,4) null ,
LAST_CUME_POWE numeric(18,4) null ,
MONE numeric(18,4) null ,
CUME_MONE numeric(18,4) null ,
LAST_MONE numeric(18,4) null ,
PREV_MONE numeric(18,4) null ,
LAST_CUME_MONE numeric(18,4) null ,
PRIC numeric(12,6) null ,
CUME_PRIC numeric(12,6) null ,
LAST_PRIC numeric(12,6) null ,
PREV_PRIC numeric(12,6) null ,
LAST_CUME_PRIC numeric(12,6) null ,
NUMB numeric(18,4) null ,
CUME_NUMB numeric(18,4) null ,
LAST_NUMB numeric(18,4) null ,
PREV_NUMB numeric(18,4) null ,
LAST_CUME_NUMB numeric(18,4) null ,
NUMB_PRIC numeric(12,6) null ,
LAST_NUMB_PRIC numeric(12,6) null ,
CUME_NUMB_PRIC numeric(12,6) null ,
LAST_CUME_NUMB_PRIC numeric(12,6) null ,
PREV_NUMB_PRIC numeric(12,6) null ,
COSI numeric(18,4) null ,
CUME_COSI numeric(18,4) null ,
LAST_COSI numeric(18,4) null ,
PREV_COSI numeric(18,4) null ,
LAST_CUME_COSI numeric(18,4) null ,
COSD numeric(18,4) null ,
CUME_COSD numeric(18,4) null ,
LAST_COSD numeric(18,4) null ,
PREV_COSD numeric(18,4) null ,
LAST_CUME_COSD numeric(18,4) null ,
COS_PRIC numeric(12,6) null ,
LAST_COS_PRIC numeric(12,6) null ,
PREV_COS_PRIC numeric(12,6) null ,
CUME_COS_PRIC numeric(12,6) null ,
LAST_CUME_COS_PRIC numeric(12,6) null ,
CAPA numeric(18,4) null ,
CUME_CAPA numeric(18,4) null ,
LAST_CAPA numeric(18,4) null ,
PREV_CAPA numeric(18,4) null ,
LAST_CUME_CAPA numeric(18,4) null ,
NEED numeric(18,4) null ,
CUME_NEED numeric(18,4) null ,
LAST_NEED numeric(18,4) null ,
PREV_NEED numeric(18,4) null ,
LAST_CUME_NEED numeric(18,4) null ,
BASE_PRIC numeric(12,6) null ,
LAST_BASE_PRIC numeric(12,6) null ,
PREV_BASE_PRIC numeric(12,6) null ,
CUME_BASE_PRIC numeric(12,6) null ,
LAST_CUME_BASE_PRIC numeric(12,6) null ,
FGPM numeric(18,4) null ,
CUME_FGPM numeric(18,4) null ,
LAST_FGPM numeric(18,4) null ,
PREV_FGPM numeric(18,4) null ,
LAST_CUME_FGPM numeric(18,4) null ,
FGP_PRIC numeric(12,6) null ,
LAST_FGP_PRIC numeric(12,6) null ,
PREV_FGP_PRIC numeric(12,6) null ,
CUME_FGP_PRIC numeric(12,6) null ,
LAST_CUME_FGP_PRIC numeric(12,6) null ,
PREF_POWE numeric(18,4) null ,
CUME_PREF_POWE numeric(18,4) null ,
LAST_PREF_POWE numeric(18,4) null ,
PREV_PREF_POWE numeric(18,4) null ,
LAST_CUME_PREF_POWE numeric(18,4) null ,
PREF_MONE numeric(18,4) null ,
CUME_PREF_MONE numeric(18,4) null ,
LAST_PREF_MONE numeric(18,4) null ,
PREV_PREF_MONE numeric(18,4) null ,
LAST_CUME_PREF_MONE numeric(18,4) null ,
PREF_PRIC numeric(12,6) null ,
CUME_PREF_PRIC numeric(12,6) null ,
LAST_PREF_PRIC numeric(12,6) null ,
PREV_PREF_PRIC numeric(12,6) null ,
LAST_CUME_PREF_PRIC numeric(12,6) null ,
PLAN_POWE numeric(18,4) null ,
YEAR_PLAN_POWE numeric(18,4) null ,
LAST_YEAR_POWE numeric(18,4) null ,
PLAN_PRIC numeric(12,6) null ,
CUME_PLAN_POWE numeric(18,4) null ,
CUME_PLAN_PRIC numeric(12,6) null ,
PLAN_MONE numeric(18,4) null ,
CUME_PLAN_MONE numeric(18,4) null ,
YEAR_PLAN_MONE numeric(18,4) null ,
LAST_YEAR_MONE numeric(18,4) null ,
PROG_POWE numeric(18,4) null ,
CUME_PROG_POWE numeric(18,4) null ,
ADD_MONEY numeric(18,4) null ,
LAST_ADD_MONEY numeric(18,4) null ,
PREV_ADD_MONEY numeric(18,4) null ,
CUME_ADD_MONEY numeric(18,4) null ,
LAST_CUME_ADD_MONEY numeric(18,4) null ,
FS_PRIC numeric(12,6) null ,
KS_PRIC numeric(12,6) null
)declare c1 cursor for --创建一个游标
select distinct SUM_MONT,LEVE from DSSS_TIME_RELATION
where MONT=@time_mont order by LEVE ASCopen c1 --打开游标
while(@@sqlstatus=0) --按照指定的条件进行循环
begin
fetch c1 into @sum_mont,@leve --装取行值
(
TIME_YEAR,TIME_MONT,UNIT_CODE,SALE_CODE,
POWE, CUME_POWE, LAST_POWE, PREV_POWE, LAST_CUME_POWE,
MONE, CUME_MONE, LAST_MONE, PREV_MONE, LAST_CUME_MONE,
PRIC, CUME_PRIC, LAST_PRIC, PREV_PRIC, LAST_CUME_PRIC,
NUMB, CUME_NUMB, LAST_NUMB, PREV_NUMB, LAST_CUME_NUMB,
NUMB_PRIC, LAST_NUMB_PRIC, CUME_NUMB_PRIC, LAST_CUME_NUMB_PRIC,
PREV_NUMB_PRIC, COSI, CUME_COSI, LAST_COSI, PREV_COSI,
LAST_CUME_COSI, COSD, CUME_COSD, LAST_COSD, PREV_COSD,
LAST_CUME_COSD, COS_PRIC, LAST_COS_PRIC, PREV_COS_PRIC,
CUME_COS_PRIC, LAST_CUME_COS_PRIC, CAPA, CUME_CAPA,
LAST_CAPA, PREV_CAPA, LAST_CUME_CAPA, NEED, CUME_NEED, LAST_NEED,
PREV_NEED, LAST_CUME_NEED, BASE_PRIC, LAST_BASE_PRIC, PREV_BASE_PRIC,
CUME_BASE_PRIC, LAST_CUME_BASE_PRIC, FGPM, CUME_FGPM, LAST_FGPM,
PREV_FGPM, LAST_CUME_FGPM, FGP_PRIC, LAST_FGP_PRIC, PREV_FGP_PRIC,
CUME_FGP_PRIC, LAST_CUME_FGP_PRIC, PREF_POWE, CUME_PREF_POWE,
LAST_PREF_POWE, PREV_PREF_POWE, LAST_CUME_PREF_POWE, PREF_MONE,
CUME_PREF_MONE, LAST_PREF_MONE, PREV_PREF_MONE, LAST_CUME_PREF_MONE,
PREF_PRIC, CUME_PREF_PRIC, LAST_PREF_PRIC, PREV_PREF_PRIC,
LAST_CUME_PREF_PRIC,PLAN_POWE,YEAR_PLAN_POWE,LAST_YEAR_POWE,PLAN_PRIC,
CUME_PLAN_POWE,CUME_PLAN_PRIC,PLAN_MONE,CUME_PLAN_MONE,YEAR_PLAN_MONE,
LAST_YEAR_MONE,PROG_POWE,CUME_PROG_POWE,ADD_MONEY,LAST_ADD_MONEY,
PREV_ADD_MONEY,CUME_ADD_MONEY,LAST_CUME_ADD_MONEY,FS_PRIC,KS_PRIC
)
SELECT ---按照变量的值和一个存储参数以及两个表满足取行变量的值进行插入
@time_year, @sum_mont, @unit_code, A.DIME_VALU_CODE,
B.POWE, B.CUME_POWE, B.LAST_POWE, B.PREV_POWE, B.LAST_CUME_POWE,
B.MONE, B.CUME_MONE, B.LAST_MONE, B.PREV_MONE, B.LAST_CUME_MONE,
B.PRIC, B.CUME_PRIC, B.LAST_PRIC, B.PREV_PRIC, B.LAST_CUME_PRIC,
B.NUMB, B.CUME_NUMB, B.LAST_NUMB, B.PREV_NUMB, B.LAST_CUME_NUMB,
B.NUMB_PRIC, B.LAST_NUMB_PRIC, B.CUME_NUMB_PRIC, B.LAST_CUME_NUMB_PRIC,
B.PREV_NUMB_PRIC, B.COSI, B.CUME_COSI, B.LAST_COSI, B.PREV_COSI,
B.LAST_CUME_COSI, B.COSD, B.CUME_COSD, B.LAST_COSD, B.PREV_COSD,
B.LAST_CUME_COSD, B.COS_PRIC, B.LAST_COS_PRIC, B.PREV_COS_PRIC,
B.CUME_COS_PRIC, B.LAST_CUME_COS_PRIC, B.CAPA, B.CUME_CAPA,
B.LAST_CAPA, B.PREV_CAPA, B.LAST_CUME_CAPA, B.NEED, B.CUME_NEED, B.LAST_NEED,
B.PREV_NEED, B.LAST_CUME_NEED, B.BASE_PRIC, B.LAST_BASE_PRIC, B.PREV_BASE_PRIC,
B.CUME_BASE_PRIC, B.LAST_CUME_BASE_PRIC, B.FGPM, B.CUME_FGPM, B.LAST_FGPM,
B.PREV_FGPM, B.LAST_CUME_FGPM, B.FGP_PRIC, B.LAST_FGP_PRIC, B.PREV_FGP_PRIC,
B.CUME_FGP_PRIC, B.LAST_CUME_FGP_PRIC, B.PREF_POWE, B.CUME_PREF_POWE,
B.LAST_PREF_POWE, B.PREV_PREF_POWE, B.LAST_CUME_PREF_POWE, B.PREF_MONE,
B.CUME_PREF_MONE, B.LAST_PREF_MONE, B.PREV_PREF_MONE, B.LAST_CUME_PREF_MONE,
B.PREF_PRIC, B.CUME_PREF_PRIC, B.LAST_PREF_PRIC, B.PREV_PREF_PRIC,
B.LAST_CUME_PREF_PRIC,B.PLAN_POWE,B.YEAR_PLAN_POWE,B.LAST_YEAR_POWE,B.PLAN_PRIC,
B.CUME_PLAN_POWE,B.CUME_PLAN_PRIC,B.PLAN_MONE,B.CUME_PLAN_MONE,B.YEAR_PLAN_MONE,
B.LAST_YEAR_MONE,B.PROG_POWE,B.CUME_PROG_POWE,B.ADD_MONEY,B.LAST_ADD_MONEY,
B.PREV_ADD_MONEY,B.CUME_ADD_MONEY,B.LAST_CUME_ADD_MONEY,B.FS_PRIC,B.KS_PRIC
FROM dbo.DSSD_SALE A, dbo.DSSF_SALE B
WHERE ( B.SALE_CODE =* A.DIME_VALU_CODE ) AND (B.UNIT_CODE=@unit_code) and
TIME_YEAR=@time_year and TIME_MONT=@sum_montdelete from DSSF_SALE where TIME_YEAR=@time_year and TIME_MONT=@sum_mont and UNIT_CODE=@unit_codeinsert into DSSF_SALE --按照表指定的值插入变量
(TIME_YEAR,TIME_MONT,UNIT_CODE,SALE_CODE,
POWE, CUME_POWE, LAST_POWE, PREV_POWE, LAST_CUME_POWE,
MONE, CUME_MONE, LAST_MONE, PREV_MONE, LAST_CUME_MONE,
PRIC, CUME_PRIC, LAST_PRIC, PREV_PRIC, LAST_CUME_PRIC,
NUMB, CUME_NUMB, LAST_NUMB, PREV_NUMB, LAST_CUME_NUMB,
NUMB_PRIC, LAST_NUMB_PRIC, CUME_NUMB_PRIC, LAST_CUME_NUMB_PRIC,
PREV_NUMB_PRIC, COSI, CUME_COSI, LAST_COSI, PREV_COSI,
LAST_CUME_COSI, COSD, CUME_COSD, LAST_COSD, PREV_COSD,
LAST_CUME_COSD, COS_PRIC, LAST_COS_PRIC, PREV_COS_PRIC,
CUME_COS_PRIC, LAST_CUME_COS_PRIC, CAPA, CUME_CAPA,
LAST_CAPA, PREV_CAPA, LAST_CUME_CAPA, NEED, CUME_NEED, LAST_NEED,
PREV_NEED, LAST_CUME_NEED, BASE_PRIC, LAST_BASE_PRIC, PREV_BASE_PRIC,
CUME_BASE_PRIC, LAST_CUME_BASE_PRIC, FGPM, CUME_FGPM, LAST_FGPM,
PREV_FGPM, LAST_CUME_FGPM, FGP_PRIC, LAST_FGP_PRIC, PREV_FGP_PRIC,
CUME_FGP_PRIC, LAST_CUME_FGP_PRIC, PREF_POWE, CUME_PREF_POWE,
LAST_PREF_POWE, PREV_PREF_POWE, LAST_CUME_PREF_POWE, PREF_MONE,
CUME_PREF_MONE, LAST_PREF_MONE, PREV_PREF_MONE, LAST_CUME_PREF_MONE,
PREF_PRIC, CUME_PREF_PRIC, LAST_PREF_PRIC, PREV_PREF_PRIC,
LAST_CUME_PREF_PRIC,PLAN_POWE,YEAR_PLAN_POWE,LAST_YEAR_POWE,PLAN_PRIC,
CUME_PLAN_POWE,CUME_PLAN_PRIC,PLAN_MONE,CUME_PLAN_MONE,YEAR_PLAN_MONE,
LAST_YEAR_MONE,PROG_POWE,CUME_PROG_POWE,ADD_MONEY,LAST_ADD_MONEY,
PREV_ADD_MONEY,CUME_ADD_MONEY,LAST_CUME_ADD_MONEY,FS_PRIC,KS_PRIC)
select
TIME_YEAR,TIME_MONT,UNIT_CODE,SALE_CODE,
POWE, CUME_POWE, LAST_POWE, PREV_POWE, LAST_CUME_POWE,
MONE, CUME_MONE, LAST_MONE, PREV_MONE, LAST_CUME_MONE,
PRIC, CUME_PRIC, LAST_PRIC, PREV_PRIC, LAST_CUME_PRIC,
NUMB, CUME_NUMB, LAST_NUMB, PREV_NUMB, LAST_CUME_NUMB,
NUMB_PRIC, LAST_NUMB_PRIC, CUME_NUMB_PRIC, LAST_CUME_NUMB_PRIC,
PREV_NUMB_PRIC, COSI, CUME_COSI, LAST_COSI, PREV_COSI,
LAST_CUME_COSI, COSD, CUME_COSD, LAST_COSD, PREV_COSD,
LAST_CUME_COSD, COS_PRIC, LAST_COS_PRIC, PREV_COS_PRIC,
CUME_COS_PRIC, LAST_CUME_COS_PRIC, CAPA, CUME_CAPA,
LAST_CAPA, PREV_CAPA, LAST_CUME_CAPA, NEED, CUME_NEED, LAST_NEED,
PREV_NEED, LAST_CUME_NEED, BASE_PRIC, LAST_BASE_PRIC, PREV_BASE_PRIC,
CUME_BASE_PRIC, LAST_CUME_BASE_PRIC, FGPM, CUME_FGPM, LAST_FGPM,
PREV_FGPM, LAST_CUME_FGPM, FGP_PRIC, LAST_FGP_PRIC, PREV_FGP_PRIC,
CUME_FGP_PRIC, LAST_CUME_FGP_PRIC, PREF_POWE, CUME_PREF_POWE,
LAST_PREF_POWE, PREV_PREF_POWE, LAST_CUME_PREF_POWE, PREF_MONE,
CUME_PREF_MONE, LAST_PREF_MONE, PREV_PREF_MONE, LAST_CUME_PREF_MONE,
PREF_PRIC, CUME_PREF_PRIC, LAST_PREF_PRIC, PREV_PREF_PRIC,
LAST_CUME_PREF_PRIC,PLAN_POWE,YEAR_PLAN_POWE,LAST_YEAR_POWE,PLAN_PRIC,
CUME_PLAN_POWE,CUME_PLAN_PRIC,PLAN_MONE,CUME_PLAN_MONE,YEAR_PLAN_MONE,
LAST_YEAR_MONE,PROG_POWE,CUME_PROG_POWE,ADD_MONEY,LAST_ADD_MONEY,
PREV_ADD_MONEY,CUME_ADD_MONEY,LAST_CUME_ADD_MONEY,FS_PRIC,KS_PRIC
from #DSSF_SALE
set
POWE=(--售电量
select
SUM(ISNULL(POWE,0))
from DSSF_SALE B,DSSS_TIME_RELATION C
where B.TIME_MONT=C.MONT and B.UNIT_CODE=@unit_code and C.SUM_MONT=@sum_mont
and B.SALE_CODE=A.SALE_CODE and B.TIME_YEAR=@time_year
)
from DSSF_SALE A
where A.TIME_YEAR=@time_year and A.TIME_MONT=@sum_mont and A.UNIT_CODE=@unit_codeupdate DSSF_SALE
set
MONE=(--售电收入
select
SUM(ISNULL(MONE,0))
from DSSF_SALE B,DSSS_TIME_RELATION C
where B.TIME_MONT=C.MONT and B.UNIT_CODE=@unit_code and C.SUM_MONT=@sum_mont
and B.SALE_CODE=A.SALE_CODE and B.TIME_YEAR=@time_year
)
from DSSF_SALE A
where A.TIME_YEAR=@time_year and A.TIME_MONT=@sum_mont and A.UNIT_CODE=@unit_code
update DSSF_SALE
set
NUMB=(--电度电费
select
SUM(ISNULL(NUMB,0))
from DSSF_SALE B,DSSS_TIME_RELATION C
where B.TIME_MONT=C.MONT and B.UNIT_CODE=@unit_code and C.SUM_MONT=@sum_mont
and B.SALE_CODE=A.SALE_CODE and B.TIME_YEAR=@time_year
)
from DSSF_SALE A
where A.TIME_YEAR=@time_year and A.TIME_MONT=@sum_mont and A.UNIT_CODE=@unit_code
update DSSF_SALE
set
COSI=(--力调增
select
SUM(ISNULL(COSI,0))
from DSSF_SALE B,DSSS_TIME_RELATION C
where B.TIME_MONT=C.MONT and B.UNIT_CODE=@unit_code and C.SUM_MONT=@sum_mont
and B.SALE_CODE=A.SALE_CODE and B.TIME_YEAR=@time_year
)
from DSSF_SALE A
where A.TIME_YEAR=@time_year and A.TIME_MONT=@sum_mont and A.UNIT_CODE=@unit_code
update DSSF_SALE
set
COSD=(--力调减
select
SUM(ISNULL(COSD,0))
from DSSF_SALE B,DSSS_TIME_RELATION C
where B.TIME_MONT=C.MONT and B.UNIT_CODE=@unit_code and C.SUM_MONT=@sum_mont
and B.SALE_CODE=A.SALE_CODE and B.TIME_YEAR=@time_year
)
from DSSF_SALE A
where A.TIME_YEAR=@time_year and A.TIME_MONT=@sum_mont and A.UNIT_CODE=@unit_code
update DSSF_SALE
set
CAPA=(--容量电费
select
SUM(ISNULL(CAPA,0))
from DSSF_SALE B,DSSS_TIME_RELATION C
where B.TIME_MONT=C.MONT and B.UNIT_CODE=@unit_code and C.SUM_MONT=@sum_mont
and B.SALE_CODE=A.SALE_CODE and B.TIME_YEAR=@time_year
)
from DSSF_SALE A
where A.TIME_YEAR=@time_year and A.TIME_MONT=@sum_mont and A.UNIT_CODE=@unit_codeupdate DSSF_SALE
set
NEED=(--需量电费
select
SUM(ISNULL(NEED,0))
from DSSF_SALE B,DSSS_TIME_RELATION C
where B.TIME_MONT=C.MONT and B.UNIT_CODE=@unit_code and C.SUM_MONT=@sum_mont
and B.SALE_CODE=A.SALE_CODE and B.TIME_YEAR=@time_year
)
from DSSF_SALE A
where A.TIME_YEAR=@time_year and A.TIME_MONT=@sum_mont and A.UNIT_CODE=@unit_code
UPDATE DSSF_SALE
set
FGPM=(--峰谷增收电费
select
SUM(ISNULL(FGPM,0))
from DSSF_SALE B,DSSS_TIME_RELATION C
where B.TIME_MONT=C.MONT and B.UNIT_CODE=@unit_code and C.SUM_MONT=@sum_mont
and B.SALE_CODE=A.SALE_CODE and B.TIME_YEAR=@time_year
)
from DSSF_SALE A
where A.TIME_YEAR=@time_year and A.TIME_MONT=@sum_mont and A.UNIT_CODE=@unit_codeupdate DSSF_SALE
set
PREF_POWE=(--优惠电量
select
SUM(ISNULL(PREF_POWE,0))
from DSSF_SALE B,DSSS_TIME_RELATION C
where B.TIME_MONT=C.MONT and B.UNIT_CODE=@unit_code and C.SUM_MONT=@sum_mont
and B.SALE_CODE=A.SALE_CODE and B.TIME_YEAR=@time_year
)
from DSSF_SALE A
where A.TIME_YEAR=@time_year and A.TIME_MONT=@sum_mont and A.UNIT_CODE=@unit_code
set
PREF_MONE=(--优惠电费
select
SUM(ISNULL(PREF_MONE,0))
from DSSF_SALE B,DSSS_TIME_RELATION C
where B.TIME_MONT=C.MONT and B.UNIT_CODE=@unit_code and C.SUM_MONT=@sum_mont
and B.SALE_CODE=A.SALE_CODE and B.TIME_YEAR=@time_year
)
from DSSF_SALE A
where A.TIME_YEAR=@time_year and A.TIME_MONT=@sum_mont and A.UNIT_CODE=@unit_codeupdate DSSF_SALE
set
PLAN_POWE=(--计划电量
select
SUM(ISNULL(PLAN_POWE,0))
from DSSF_SALE B,DSSS_TIME_RELATION C
where B.TIME_MONT=C.MONT and B.UNIT_CODE=@unit_code and C.SUM_MONT=@sum_mont
and B.SALE_CODE=A.SALE_CODE and B.TIME_YEAR=@time_year
)
from DSSF_SALE A
where A.TIME_YEAR=@time_year and A.TIME_MONT=@sum_mont and A.UNIT_CODE=@unit_codeupdate DSSF_SALE
set
PLAN_MONE=(--计划电费
select
SUM(ISNULL(PLAN_MONE,0))
from DSSF_SALE B,DSSS_TIME_RELATION C
where B.TIME_MONT=C.MONT and B.UNIT_CODE=@unit_code and C.SUM_MONT=@sum_mont
and B.SALE_CODE=A.SALE_CODE and B.TIME_YEAR=@time_year
),
PROG_POWE=(--预测电量
select
SUM(ISNULL(PROG_POWE,0))
from DSSF_SALE B,DSSS_TIME_RELATION C
where B.TIME_MONT=C.MONT and B.UNIT_CODE=@unit_code and C.SUM_MONT=@sum_mont
and B.SALE_CODE=A.SALE_CODE and B.TIME_YEAR=@time_year
)
from DSSF_SALE A
where A.TIME_YEAR=@time_year and A.TIME_MONT=@sum_mont and A.UNIT_CODE=@unit_codeupdate DSSF_SALE
set
CUME_POWE=
(
select B.CUME_POWE from DSSF_SALE B where B.TIME_YEAR=@time_year and B.TIME_MONT=@time_mont
and B.SALE_CODE=A.SALE_CODE and B.UNIT_CODE=@unit_code
),
CUME_MONE=
(
select B.CUME_MONE from DSSF_SALE B where B.TIME_YEAR=@time_year and B.TIME_MONT=@time_mont
and B.SALE_CODE=A.SALE_CODE and B.UNIT_CODE=@unit_code
),
CUME_NUMB=
(
select B.CUME_NUMB from DSSF_SALE B where B.TIME_YEAR=@time_year and B.TIME_MONT=@time_mont
and B.SALE_CODE=A.SALE_CODE and B.UNIT_CODE=@unit_code
),
CUME_COSI=
(
select B.CUME_COSI from DSSF_SALE B where B.TIME_YEAR=@time_year and B.TIME_MONT=@time_mont
and B.SALE_CODE=A.SALE_CODE and B.UNIT_CODE=@unit_code
),
CUME_COSD=
(
select B.CUME_COSD from DSSF_SALE B where B.TIME_YEAR=@time_year and B.TIME_MONT=@time_mont
and B.SALE_CODE=A.SALE_CODE and B.UNIT_CODE=@unit_code
),
CUME_CAPA=
(
select B.CUME_CAPA from DSSF_SALE B where B.TIME_YEAR=@time_year and B.TIME_MONT=@time_mont
and B.SALE_CODE=A.SALE_CODE and B.UNIT_CODE=@unit_code
),
CUME_NEED=
(
select B.CUME_NEED from DSSF_SALE B where B.TIME_YEAR=@time_year and B.TIME_MONT=@time_mont
and B.SALE_CODE=A.SALE_CODE and B.UNIT_CODE=@unit_code
),
CUME_FGPM=
(
select B.CUME_FGPM from DSSF_SALE B where B.TIME_YEAR=@time_year and B.TIME_MONT=@time_mont
and B.SALE_CODE=A.SALE_CODE and B.UNIT_CODE=@unit_code
),
CUME_PREF_POWE=
(
select B.CUME_PREF_POWE from DSSF_SALE B where B.TIME_YEAR=@time_year and B.TIME_MONT=@time_mont
and B.SALE_CODE=A.SALE_CODE and B.UNIT_CODE=@unit_code
),
CUME_PREF_MONE=
(
select B.CUME_PREF_MONE from DSSF_SALE B where B.TIME_YEAR=@time_year and B.TIME_MONT=@time_mont
and B.SALE_CODE=A.SALE_CODE and B.UNIT_CODE=@unit_code
),
CUME_PLAN_POWE=
(
select B.CUME_PLAN_POWE from DSSF_SALE B where B.TIME_YEAR=@time_year and B.TIME_MONT=@time_mont
and B.SALE_CODE=A.SALE_CODE and B.UNIT_CODE=@unit_code
),
CUME_PROG_POWE=
(
select B.CUME_PROG_POWE from DSSF_SALE B where B.TIME_YEAR=@time_year and B.TIME_MONT=@time_mont
and B.SALE_CODE=A.SALE_CODE and B.UNIT_CODE=@unit_code
),
CUME_PLAN_MONE=
(
select B.CUME_PLAN_MONE from DSSF_SALE B where B.TIME_YEAR=@time_year and B.TIME_MONT=@time_mont
and B.SALE_CODE=A.SALE_CODE and B.UNIT_CODE=@unit_code
)from DSSF_SALE A where A.TIME_YEAR=@time_year and A.TIME_MONT=@sum_mont and A.UNIT_CODE=@unit_codeend --结素循环
close c1 --关闭游标distinct SUM_MONT from DSSS_TIME_RELATION where MONT=@time_mont)
)
update DSSF_SALE --按照指定的值进行更新表并且进行强制替换
set
PRIC=isnull(convert(numeric(12,6), MONE/POWE),0),
NUMB_PRIC=isnull(convert(numeric(12,6), NUMB/POWE),0),
COS_PRIC=isnull(convert(numeric(12,6), (isnull(COSI,0)-isnull(COSD,0))/POWE),0),
BASE_PRIC=isnull(convert(numeric(12,6), (isnull(CAPA,0)+isnull(NEED,0))/POWE),0),
FGP_PRIC=isnull(convert(numeric(12,6), FGPM/POWE),0),
PREF_PRIC=isnull(convert(numeric(12,6), PREF_MONE/POWE),0)
from DSSF_SALE where TIME_YEAR=@time_year and UNIT_CODE=@unit_code and POWE>0 and
(TIME_MONT=@time_mont or TIME_MONT in
(select distinct SUM_MONT from DSSS_TIME_RELATION where MONT=@time_mont)
)update DSSF_SALE
set
CUME_PRIC=isnull(convert(numeric(12,6), CUME_MONE/CUME_POWE),0),
CUME_NUMB_PRIC=isnull(convert(numeric(12,6), CUME_NUMB/CUME_POWE),0),
CUME_COS_PRIC=isnull(convert(numeric(12,6), (isnull(CUME_COSI,0)-isnull(CUME_COSD,0))/CUME_POWE),0),
CUME_BASE_PRIC=isnull(convert(numeric(12,6), (isnull(CUME_CAPA,0)+isnull(CUME_NEED,0))/CUME_POWE),0),
CUME_FGP_PRIC=isnull(convert(numeric(12,6), CUME_FGPM/CUME_POWE),0),
CUME_PREF_PRIC=isnull(convert(numeric(12,6), CUME_PREF_MONE/CUME_POWE),0)
from DSSF_SALE where TIME_YEAR=@time_year and UNIT_CODE=@unit_code and CUME_POWE>0 and
(TIME_MONT=@time_mont or TIME_MONT in
(select distinct SUM_MONT from DSSS_TIME_RELATION where MONT=@time_mont)
)update DSSF_SALE
set
CUME_PLAN_PRIC=isnull(convert(numeric(12,6), CUME_PLAN_MONE/CUME_PLAN_POWE),0)
from DSSF_SALE where TIME_YEAR=@time_year and UNIT_CODE=@unit_code and CUME_PLAN_POWE>0 and
(TIME_MONT=@time_mont or TIME_MONT in
(select distinct SUM_MONT from DSSS_TIME_RELATION where MONT=@time_mont)
)update DSSF_SALE
set
PLAN_PRIC=isnull(convert(numeric(12,6), PLAN_MONE/PLAN_POWE),0)
from DSSF_SALE where TIME_YEAR=@time_year and UNIT_CODE=@unit_code and PLAN_POWE>0 and
(TIME_MONT=@time_mont or TIME_MONT in
(select
set
表a的需要更新的列=
(select sum(表b的求和的列) from 表B where 表b的条件列1=@输入参数 and 表b的外键列=表a的主键列),
......
set
表a的需要更新的列=
(select sum(表b的求和的列) from 表B where 表b的条件列1=@输入参数 and 表b的外键列=表a的主键列),
......
set
表a的需要更新的列=
(select sum(表b的求和的列) from 表B where 表b的条件列1=@输入参数 and 表b的外键列=表a的主键列),
......