CREATE Proc Workpiece_CountReport
@MaTolName nvarchar(50)=null,
@dtpBegin Datetime=null,
@dtpEnd Datetime=null
as
begin
if(@MaTolName='#')
begin
set @MaTolName=null
end
if (@MaTolName IS not null)
begin
SELECT MaTolName,ProgramName, count(ProgramName) as ProgramNameCount,ProgramName As ReportGroup
FROM ProgramRunInfo
WHERE StartTime>=@dtpBegin+'00:00:00'and StartTime <=@dtpEnd+'23:59:59'AND MaTolName=@MaTolName group by MaTolName,ProgramName
end
else
begin
SELECT MaTolName, count(*) as ProgramNameCount,MaTolName as ReportGroup
FROM ProgramRunInfo
WHERE StartTime>=@dtpBegin+'00:00:00'and StartTime <=@dtpEnd+'23:59:59'group by MaTolName
end
endGO
/*查询指定机床的基本状态*/
CREATE procedure Report_MaTolStatus_Group
@ID nvarchar(50),
@Name nvarchar(50),
@begin DateTime,
@end DateTime
as
DECLARE @TimeValue BigInt
SET @TimeValue=DATEDIFF (second,@begin,@end)
begin
if(@Name='All')
begin
SELECT [status]=case
when grouping(MaTolName)=1 then '合计'
when grouping(status)=1 then '关机'
when grouping(status)=0 then status
end ,statusTimeValue=case
when grouping(status)=1 then @TimeValue-sum([statusTimeValue])
when grouping(status)=0 then sum([statusTimeValue])
end,dbo.GetTimeStr(case
when grouping(status)=1 then @TimeValue-sum([statusTimeValue])
when grouping(status)=0 then sum([statusTimeValue])
end)AS STRstatusTimeValue,[Group]=[MaTolName],[TimeValue]=@TimeValue
FROM [dbo].[StatusTable]
WHERE [StartTime] BETWEEN @begin AND @end GROUP BY [MaTolName] ,[status] WITH ROLLUP
end
if(@Name='Dept')
begin
SELECT [status]=case
when grouping(MaTolName)=1 then '合计'
when grouping(status)=1 then '关机'
when grouping(status)=0 then status
end ,statusTimeValue=case
when grouping(status)=1 then @TimeValue-sum([statusTimeValue])
when grouping(status)=0 then sum([statusTimeValue])
end,dbo.GetTimeStr(case
when grouping(status)=1 then @TimeValue-sum([statusTimeValue])
when grouping(status)=0 then sum([statusTimeValue])
end)AS STRstatusTimeValue,[Group]=[MaTolName],[TimeValue]=@TimeValue
FROM [dbo].[StatusTable]
WHERE [DeptID]=cast(@ID AS uniqueidentifier)
AND [StartTime] BETWEEN @begin AND @end GROUP BY [MaTolName],[status] WITH ROLLUP
end
if(@Name='MaTolGrp')
begin
SELECT [status]=case
when grouping(MaTolName)=1 then '合计'
when grouping(status)=1 then '关机'
when grouping(status)=0 then status
end ,statusTimeValue=case
when grouping(status)=1 then @TimeValue -sum([statusTimeValue])
when grouping(status)=0 then sum([statusTimeValue])
end,dbo.GetTimeStr(case
when grouping(status)=1 then @TimeValue-sum([statusTimeValue])
when grouping(status)=0 then sum([statusTimeValue])
end)AS STRstatusTimeValue,[Group]=[MaTolName],[TimeValue]=@TimeValue
FROM [dbo].[StatusTable]
WHERE [MaTolGrpID]=cast(@ID AS uniqueidentifier) AND [StartTime] BETWEEN @begin AND @end GROUP BY [MaTolName],[status] WITH ROLLUP
end
if(@Name='MaTol')
begin
SELECT [status]=case
when grouping(MaTolName)=1 then '合计'
when grouping(status)=1 then '关机'
when grouping(status)=0 then status
end ,statusTimeValue=case
when grouping(status)=1 then @TimeValue-sum([statusTimeValue])
when grouping(status)=0 then sum([statusTimeValue])
end,dbo.GetTimeStr(case
when grouping(status)=1 then @TimeValue-sum([statusTimeValue])
when grouping(status)=0 then sum([statusTimeValue])
end)AS STRstatusTimeValue,[Group]=[MaTolName],[TimeValue]=@TimeValue
FROM [dbo].[StatusTable]
WHERE [MaTolName]=@ID AND [StartTime] BETWEEN @begin AND @end GROUP BY [MaTolName],[status] WITH ROLLUP
end
end
--和Report_MaTolStatus相比多一个机床分组
GO
@MaTolName nvarchar(50)=null,
@dtpBegin Datetime=null,
@dtpEnd Datetime=null
as
begin
if(@MaTolName='#')
begin
set @MaTolName=null
end
if (@MaTolName IS not null)
begin
SELECT MaTolName,ProgramName, count(ProgramName) as ProgramNameCount,ProgramName As ReportGroup
FROM ProgramRunInfo
WHERE StartTime>=@dtpBegin+'00:00:00'and StartTime <=@dtpEnd+'23:59:59'AND MaTolName=@MaTolName group by MaTolName,ProgramName
end
else
begin
SELECT MaTolName, count(*) as ProgramNameCount,MaTolName as ReportGroup
FROM ProgramRunInfo
WHERE StartTime>=@dtpBegin+'00:00:00'and StartTime <=@dtpEnd+'23:59:59'group by MaTolName
end
endGO
/*查询指定机床的基本状态*/
CREATE procedure Report_MaTolStatus_Group
@ID nvarchar(50),
@Name nvarchar(50),
@begin DateTime,
@end DateTime
as
DECLARE @TimeValue BigInt
SET @TimeValue=DATEDIFF (second,@begin,@end)
begin
if(@Name='All')
begin
SELECT [status]=case
when grouping(MaTolName)=1 then '合计'
when grouping(status)=1 then '关机'
when grouping(status)=0 then status
end ,statusTimeValue=case
when grouping(status)=1 then @TimeValue-sum([statusTimeValue])
when grouping(status)=0 then sum([statusTimeValue])
end,dbo.GetTimeStr(case
when grouping(status)=1 then @TimeValue-sum([statusTimeValue])
when grouping(status)=0 then sum([statusTimeValue])
end)AS STRstatusTimeValue,[Group]=[MaTolName],[TimeValue]=@TimeValue
FROM [dbo].[StatusTable]
WHERE [StartTime] BETWEEN @begin AND @end GROUP BY [MaTolName] ,[status] WITH ROLLUP
end
if(@Name='Dept')
begin
SELECT [status]=case
when grouping(MaTolName)=1 then '合计'
when grouping(status)=1 then '关机'
when grouping(status)=0 then status
end ,statusTimeValue=case
when grouping(status)=1 then @TimeValue-sum([statusTimeValue])
when grouping(status)=0 then sum([statusTimeValue])
end,dbo.GetTimeStr(case
when grouping(status)=1 then @TimeValue-sum([statusTimeValue])
when grouping(status)=0 then sum([statusTimeValue])
end)AS STRstatusTimeValue,[Group]=[MaTolName],[TimeValue]=@TimeValue
FROM [dbo].[StatusTable]
WHERE [DeptID]=cast(@ID AS uniqueidentifier)
AND [StartTime] BETWEEN @begin AND @end GROUP BY [MaTolName],[status] WITH ROLLUP
end
if(@Name='MaTolGrp')
begin
SELECT [status]=case
when grouping(MaTolName)=1 then '合计'
when grouping(status)=1 then '关机'
when grouping(status)=0 then status
end ,statusTimeValue=case
when grouping(status)=1 then @TimeValue -sum([statusTimeValue])
when grouping(status)=0 then sum([statusTimeValue])
end,dbo.GetTimeStr(case
when grouping(status)=1 then @TimeValue-sum([statusTimeValue])
when grouping(status)=0 then sum([statusTimeValue])
end)AS STRstatusTimeValue,[Group]=[MaTolName],[TimeValue]=@TimeValue
FROM [dbo].[StatusTable]
WHERE [MaTolGrpID]=cast(@ID AS uniqueidentifier) AND [StartTime] BETWEEN @begin AND @end GROUP BY [MaTolName],[status] WITH ROLLUP
end
if(@Name='MaTol')
begin
SELECT [status]=case
when grouping(MaTolName)=1 then '合计'
when grouping(status)=1 then '关机'
when grouping(status)=0 then status
end ,statusTimeValue=case
when grouping(status)=1 then @TimeValue-sum([statusTimeValue])
when grouping(status)=0 then sum([statusTimeValue])
end,dbo.GetTimeStr(case
when grouping(status)=1 then @TimeValue-sum([statusTimeValue])
when grouping(status)=0 then sum([statusTimeValue])
end)AS STRstatusTimeValue,[Group]=[MaTolName],[TimeValue]=@TimeValue
FROM [dbo].[StatusTable]
WHERE [MaTolName]=@ID AND [StartTime] BETWEEN @begin AND @end GROUP BY [MaTolName],[status] WITH ROLLUP
end
end
--和Report_MaTolStatus相比多一个机床分组
GO
解决方案 »
- ORACLE只查询到了一行,怎么回事?
- oraociei10.dll 安装
- request: set autotrace traceonly;response:Cannot SET AUTOTRACE ??? what is the matter?
- oracle安装到27%时jrew.exe已停止工作
- 怎么在SQL里截串?请高人指教
- 新手提问: 怎么查某个SID/SESSION是哪个客户端的? 怎么查回滚段的使用情况,被哪些SESSION使用?
- 奇怪的字符集问题。。。
- 时间格式问题?
- Oracle无法初始化参数子系统是怎么回事?
- 请教为什么Oracle 总是提示 ORA-27101 shared memory realm does not exist
- 有关求无限分级目录下的产品信息的SQL
- C++ builder 调到oracle 存储过程式的问题
oracle里,变量前面没有@
变量赋值时是这样的
变量:=
然后select要赋值给变量的
select 字段 into 变量
如果是多条纪录,就用游标吧
/*查询指定机床的基本状态*/
CREATE procedure Report_MaTolStatus_Group
pID nvarchar(50),
pName nvarchar(50),
pbegin DateTime,
pend DateTime
as
DECLARE pTimeValue BigInt
SET pTimeValue:=DATEDIFF (second,pbegin,pend)
begin
if(pName='All')
begin
SELECT [status]=case
when grouping(MaTolName)=1 then '合计'
when grouping(status)=1 then '关机'
when grouping(status)=0 then status
end ,statusTimeValue=case
when grouping(status)=1 then pTimeValue-sum([statusTimeValue])
when grouping(status)=0 then sum([statusTimeValue])
end,dbo.GetTimeStr(case
when grouping(status)=1 then pTimeValue-sum([statusTimeValue])
when grouping(status)=0 then sum([statusTimeValue])
end)AS STRstatusTimeValue,[Group]=[MaTolName],[TimeValue]=pTimeValue
FROM [dbo].[StatusTable]
WHERE [StartTime] BETWEEN pbegin AND pend GROUP BY [MaTolName] ,[status] WITH ROLLUP
end
if(pName='Dept')
begin
SELECT [status]=case
when grouping(MaTolName)=1 then '合计'
when grouping(status)=1 then '关机'
when grouping(status)=0 then status
end ,statusTimeValue=case
when grouping(status)=1 then pTimeValue-sum([statusTimeValue])
when grouping(status)=0 then sum([statusTimeValue])
end,dbo.GetTimeStr(case
when grouping(status)=1 then pTimeValue-sum([statusTimeValue])
when grouping(status)=0 then sum([statusTimeValue])
end)AS STRstatusTimeValue,[Group]=[MaTolName],[TimeValue]=@TimeValue
FROM [dbo].[StatusTable]
WHERE [DeptID]=cast(pID AS uniqueidentifier)
AND [StartTime] BETWEEN pbegin AND pend GROUP BY [MaTolName],[status] WITH ROLLUP
end
if(pName='MaTolGrp')
begin
SELECT [status]=case
when grouping(MaTolName)=1 then '合计'
when grouping(status)=1 then '关机'
when grouping(status)=0 then status
end ,statusTimeValue=case
when grouping(status)=1 then pTimeValue -sum([statusTimeValue])
when grouping(status)=0 then sum([statusTimeValue])
end,dbo.GetTimeStr(case
when grouping(status)=1 then pTimeValue-sum([statusTimeValue])
when grouping(status)=0 then sum([statusTimeValue])
end)AS STRstatusTimeValue,[Group]=[MaTolName],[TimeValue]=pTimeValue
FROM [dbo].[StatusTable]
WHERE [MaTolGrpID]=cast(pID AS uniqueidentifier) AND [StartTime] BETWEEN pbegin AND pend GROUP BY [MaTolName],[status] WITH ROLLUP
end
if(pName='MaTol')
begin
SELECT [status]=case
when grouping(MaTolName)=1 then '合计'
when grouping(status)=1 then '关机'
when grouping(status)=0 then status
end ,statusTimeValue=case
when grouping(status)=1 then pTimeValue-sum([statusTimeValue])
when grouping(status)=0 then sum([statusTimeValue])
end,dbo.GetTimeStr(case
when grouping(status)=1 then pTimeValue-sum([statusTimeValue])
when grouping(status)=0 then sum([statusTimeValue])
end)AS STRstatusTimeValue,[Group]=[MaTolName],[TimeValue]=pTimeValue
FROM [dbo].[StatusTable]
WHERE [MaTolName]=pID AND [StartTime] BETWEEN pbegin AND pend GROUP BY [MaTolName],[status] WITH ROLLUP
end
end
--和Report_MaTolStatus相比多一个机床分组
GO
CREATE procedure Report_MaTolStatus_Group
pID nvarchar2(50),
pName nvarchar2(50),
pbegin date,
pend Date 定义也有问题