日期:2014-05-17 浏览次数:20536 次
CREATE PROCEDURE Finallenergy @enddatetime char(10),@sub char(10) AS
DECLARE @username varchar(50)
DECLARE @userid char(11)
DECLARE @moterid char(12)
DECLARE @strsinReadtime1 char(16)
DECLARE @fsinEnergy1 decimal(18,2)
IF not EXISTS (SELECT name FROM sysobjects
WHERE name ='Finalldianliang' )
begin
CREATE TABLE Finalldianliang (
[userid] [char] (11) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[username] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[moterid] [char] (12) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[endengy] [decimal](18, 2) NULL,
[enddate] [char] (16) COLLATE Chinese_PRC_CI_AS NULL,
) ON [PRIMARY]
end
DECLARE userinfo CURSOR LOCAL FOR select username,userid,moterid from userprofile u,sub s where s.subid=u.sub and s.substation=@sub order by line,userid
OPEN userinfo
FETCH NEXT FROM userinfo INTO @username,@userid,@moterid
while(@@fetch_status = 0)
begin
set @strsinReadtime1=' '
set @fsinEnergy1=-1
select @strsinReadtime1=enddate,@fsinEnergy1=endengy from userdayengy where userid=@userid and moterid=@moterid and datetime=@enddatetime
if (@fsinEnergy1=-1) --如果没有,取前一天
begin
select @strsinReadtime1=enddate,@fsinEnergy1=endengy from userdayengy where userid=@userid and moterid=@moterid and datetime=dateadd(day,-1,@enddatetime)
end
if (@fsinEnergy1=-1) --如果没有,取后一天
begin
select @strsinReadtime1=enddate,@fsinEnergy1=endengy from userdayengy where userid=@userid and moterid=@moterid and datetime=dateadd(day,1,@enddatetime)
end
if (@fsinEnergy1=-1)
begin
set @enddatetime=@enddatetime+'-23-59'
end
if (@fsinEnergy1 != -1)
begin
insert into Finalldianliang (username,userid,moterid,enddate,endengy) values(@username,@userid,@moterid,@strsinReadtime1,@fsinEnergy1)
end
FETCH NEXT FROM userinfo INTO @username,@userid,@moterid
end
CLOSE userinfo
DEALLOCATE userinfo
GO
CREATE PROCEDURE Finallenergy
@enddatetime CHAR(10) ,
@sub CHAR(10)
AS
DECLARE @username VARCHAR(50)
DECLARE @userid CHAR(11)
DECLARE @moterid CHAR(12)
DECLARE @strsinReadtime1 CHAR(16)
&nbs