is
procedure Report_MaTolStartupEff
( rst out mytype ,
vID in nvarchar2,
Name in nvarchar2,
dbegin Date,
dend Date,
vTime nvarchar2)
is
MaTol varchar2(100);
vGroup varchar2(200);
v_Resultvid varchar2(200);
v_Resultdot varchar2(200);
v_Resultyun varchar2(200);
v_mysql varchar2(1000);
begin
v_Resultvid := QUOTENAME(vID,'''');
v_Resultdot := QUOTENAME('.','');
v_Resultyun := QUOTENAME('运行','');
vGroup:='GetTimeGroup(StartTime,'||QUOTENAME(vTime,'''')||')';
if(Name='All') then
MaTol:='';
elsif(Name='Dept') then
MaTol:='DeptID='||v_Resultvid||' AND';
elsif(Name='MaTolGrp') then
MaTol:='MaTolGrpID='||v_Resultvid||' AND';
elsif(Name='MaTol') then
MaTol:='MaTolName='||v_Resultvid||' AND';
end if;
if(nvl(vTime,'#')='#') then
vGroup:='DeptName||'''||v_Resultdot||'''||MaTolGrpName||'''||v_Resultdot||'''||MaTolName';
end if;
v_Mysql := 'SELECT MaTolName,sONLINE,WORKING,GetTimeStr(sONLINE) as STRONLINE,';
v_Mysql := v_Mysql||'GetTimeStr(WORKING) AS STRWORKING,';
v_Mysql := v_Mysql||'cast(sONLINE*10000/WORKING as decimal(10,2))/100 as STARTUPEFF,';
v_Mysql := v_Mysql||'sGroup From ';
v_Mysql := v_Mysql||'(Select sum(statusTimeValue) AS sONLINE,';
--v_Mysql := v_Mysql||'(dend-dbegin)*86400 as WORKING,';
v_Mysql := v_Mysql||'('||dend||'-'||dbegin||')*86400 as WORKING,';
--v_Mysql := v_Mysql||'(to_date('||dend||','yyyy-mm-dd') - to_date('||dbegin||','yyyy-mm-dd'))*86400 as WORKING,';
v_Mysql := v_Mysql||'(DeptName||'''||v_Resultdot||'''||MaTolGrpName||'''||v_Resultdot||'''||MaTolName) as MaTolName,';
v_Mysql := v_Mysql||vGroup||' as sGroup ';
v_Mysql := v_Mysql||'From StatusTable WHERE '||MaTol||' StartTime BETWEEN '''||dbegin||''' AND '''||dend||''' Group by MaTolName,';
v_Mysql := v_Mysql||' DeptName,MaTolGrpName,'||vGroup||')';
insert into tmp_ok values(v_Mysql,sysdate);
commit;
open rst for v_Mysql;
end;
end;
v_Mysql := v_Mysql||'('||dend||'-'||dbegin||')*86400 as WORKING,';
这样计算的结果为;
(24-4月 -08-01-4月 -08)*86400 as WORKING,
得不到我要的值,大家看这个地方如何修改 v_Mysql := v_Mysql||'('||dend||'-'||dbegin||')*86400 as WORKING,';
procedure Report_MaTolStartupEff
( rst out mytype ,
vID in nvarchar2,
Name in nvarchar2,
dbegin Date,
dend Date,
vTime nvarchar2)
is
MaTol varchar2(100);
vGroup varchar2(200);
v_Resultvid varchar2(200);
v_Resultdot varchar2(200);
v_Resultyun varchar2(200);
v_mysql varchar2(1000);
begin
v_Resultvid := QUOTENAME(vID,'''');
v_Resultdot := QUOTENAME('.','');
v_Resultyun := QUOTENAME('运行','');
vGroup:='GetTimeGroup(StartTime,'||QUOTENAME(vTime,'''')||')';
if(Name='All') then
MaTol:='';
elsif(Name='Dept') then
MaTol:='DeptID='||v_Resultvid||' AND';
elsif(Name='MaTolGrp') then
MaTol:='MaTolGrpID='||v_Resultvid||' AND';
elsif(Name='MaTol') then
MaTol:='MaTolName='||v_Resultvid||' AND';
end if;
if(nvl(vTime,'#')='#') then
vGroup:='DeptName||'''||v_Resultdot||'''||MaTolGrpName||'''||v_Resultdot||'''||MaTolName';
end if;
v_Mysql := 'SELECT MaTolName,sONLINE,WORKING,GetTimeStr(sONLINE) as STRONLINE,';
v_Mysql := v_Mysql||'GetTimeStr(WORKING) AS STRWORKING,';
v_Mysql := v_Mysql||'cast(sONLINE*10000/WORKING as decimal(10,2))/100 as STARTUPEFF,';
v_Mysql := v_Mysql||'sGroup From ';
v_Mysql := v_Mysql||'(Select sum(statusTimeValue) AS sONLINE,';
--v_Mysql := v_Mysql||'(dend-dbegin)*86400 as WORKING,';
v_Mysql := v_Mysql||'('||dend||'-'||dbegin||')*86400 as WORKING,';
--v_Mysql := v_Mysql||'(to_date('||dend||','yyyy-mm-dd') - to_date('||dbegin||','yyyy-mm-dd'))*86400 as WORKING,';
v_Mysql := v_Mysql||'(DeptName||'''||v_Resultdot||'''||MaTolGrpName||'''||v_Resultdot||'''||MaTolName) as MaTolName,';
v_Mysql := v_Mysql||vGroup||' as sGroup ';
v_Mysql := v_Mysql||'From StatusTable WHERE '||MaTol||' StartTime BETWEEN '''||dbegin||''' AND '''||dend||''' Group by MaTolName,';
v_Mysql := v_Mysql||' DeptName,MaTolGrpName,'||vGroup||')';
insert into tmp_ok values(v_Mysql,sysdate);
commit;
open rst for v_Mysql;
end;
end;
v_Mysql := v_Mysql||'('||dend||'-'||dbegin||')*86400 as WORKING,';
这样计算的结果为;
(24-4月 -08-01-4月 -08)*86400 as WORKING,
得不到我要的值,大家看这个地方如何修改 v_Mysql := v_Mysql||'('||dend||'-'||dbegin||')*86400 as WORKING,';
select to_date(to_char(dend,'yyyymmdd'),'yyyymmdd')-to_date(to_char(dbegin,'yyyymmdd'),'yyyymmdd') from dual;
dend和dbegin本身就是date
直接减啊
select dend-dbegin from dual