use kj
go
CREATE procedure ccssxs
@year int,@month int,@day int
AS
BEGIN
DECLARE @ccclss varchar(40)
SET @ccclss=0
declare @rcl float
declare @rcnbf float
declare @rcnb float
declare @flag int
set @flag=0
declare @id int
set @id=1
declare @yearend int
set @yearend=@year-1
list: while @month>=1
begin
loop: WHILE @day>=1
begin
select @rcl=(SELECT sum(时产量)/1000 FROM CLSC,clcs
WHERE clsc.市名或集团公司=clcs.市名或集团公司 and clsc.县名或公司名称=clcs.县名或公司名称 and clsc.矿名=clcs.矿名
and clsc.井口号=@id and DATEPART(year,时间)=@year AND DATEPART(month,时间)=@month AND DATEPART(day,时间)=@day)
select @rcnbf=@rcl*1000/(select 日产量计划 from clcs where 井口号=@id and DATEPART(year,CLCS.修改日期)=@year )
select @rcnb=@rcnbf*100
if @rcnb>100
BEGIN
SET @ccclss=convert(varchar(8),@rcl)+'/'+convert(varchar(8),@rcnb)+'/'+convert(varchar(8),@year)+'/'+convert(varchar(4),@month)+'/'+convert(varchar(4),@day)
print @ccclss
set @flag=1
return(0)
break
END
ELSE
BEGIN
SET @day=@day-1
continue --继续执行循环
END
end
begin
set @month=@month-1
set @day=31
if @month=0
break --循环结束
else
goto loop
end
end
begin
set @year=@year-1
set @month=12
set @day=31
if @year<@yearend
return (0)
else
goto list
end
end
GO