-- ================================================
-- 单日内24小时产量统计
-- ================================================
if exists(select * from sysobjects where name='ups_QueryDataOfSingleDay')
drop procedure ups_QueryDataOfSingleDay --删除已创建的存储过程
go
CREATE PROCEDURE ups_QueryDataOfSingleDay
@QueryDatetime datetime,
@StationID varchar(10),
@Remain integer output
AS
BEGIN
-- 不返回受影响的行数
SET NOCOUNT ON
--创建临时表
create table #tWeight
(
id integer,
TonWeight integer,
DisWeight integer
)
declare @i integer,@StartDate datetime,@TonTotalWeight integer,@DisTotalWeight integer
set @i = 0
set @StartDate = Convert(varchar(10),@QueryDatetime,120)
set @StartDate = Convert(varchar(19),@StartDate+'00:00:00',120)
while @i < 24
begin
--投料
select @TonTotalWeight = IsNull(sum(DiffWeight),0) from t_SaveRecordData where
(ThisTime between @StartDate and DATEADD(hour,1,@StartDate)) and DiffWeight > 0
--排料
select @DisTotalWeight = IsNull(sum(DiffWeight),0) from t_SaveRecordData where
(ThisTime between @StartDate and DATEADD(hour,1,@StartDate)) and DiffWeight < 0
--存入临时表
insert into #tWeight(id,TonWegiht, DisWeight)values(@i, @TonTotalWeight,@DisTotalWeight)
set @StartDate = DATEADD(hour,1,@StartDate)--时间更新
set @i = @i +1
end
select * from #tWeight
--删除临时表
drop table #tWeight
--统计余量
select @Remain = isnull(sum(convert(int,Gweight)),0) from ##Gpsrealtimedata,TankInfo where
(TankInfo.GPSID = ##GpsrealtimeData.GpsID)
and TankInfo.StationID = @StationID and CONVERT(int,Gweight) >= 0
END
GO
在查询管理工具中:
declare @a integer
exec ups_QueryDataOfSingleDay '2019-05-08','1001',@a output
select @a
在C++Builder中调用,代码如下:
AnsiString strSql = "exec ups_QueryDataOfSingleDay :qtime,:sid,:Remain output";
m_pAdo->Active = false;
m_pAdo->SQL->Clear();
m_pAdo->SQL->Add(strSql);
m_pAdo->Parameters->Items[0]->Value = dt;
m_pAdo->Parameters->Items[1]->Value = szStationID;
m_pAdo->Parameters->Items[2]->Value = 0;//输出参数须赋值
m_pAdo->Active = true;
STWeight24Hours wh={0};
int i = 0;
while(!m_pAdo->Eof)
{
wh.nTonWeight[i] = m_pAdo->FieldByName("TonWeight")->AsInteger;//临时表字段
wh.nDisWeight[i] = m_pAdo->FieldByName("DisWeight")->AsInteger;
i++;
m_pAdo->Next();
}
wh.nTonWeight[24] = wh.nTonWeight[0];
wh.nDisWeight[24] = wh.nDisWeight[0];
wh.nRemainWeight = m_pAdo->Parameters->ParamByName("Remain")->Value;//读输出参数